RE: Sql Query

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 18 May 2006 11:44:00 -0400
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2F201CD@EXCHANGE.corp.perceptron.com>

Wouldn't this be sufficient:  

with T AS (

    SELECT verse, LEVEL lvl, SYS_CONNECT_BY_PATH(file_name, ',') text     FROM ( select 1 verse, rownum piece, file_name from dba_data_files )

      CONNECT BY verse = PRIOR verse
             AND piece - 1 = PRIOR piece
      START WITH piece = 1

    order by lvl desc )
select text from T
where rownum = 1
/  

which is just "internal" part of your query (slightly modified) ?  

Igor

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra Sent: Thursday, May 18, 2006 10:56 AM
To: kennaim_at_gmail.com; oracle-l_at_freelists.org Subject: RE: Sql Query

Few Weeks Back I posted a question and got few very good response. Actually What I am looking is the big list in single SQL query and cannot use the PL sql or allowed to create the SQL function, I had finally used good points from all reply and created the following SQL. Here I used file_name from dba_data_files . I thought it is good to share as somebody who is reall expert in SQL can do some more fine-tuning. This will atleast work good for mine requirement but may give some issue if the Text cross the Concat restriction.  

select text from (
select verse, lvl, text, rank() over(partition by verse order by lvl desc) rn
from
  (
    SELECT verse, LEVEL lvl, SYS_CONNECT_BY_PATH(file_name, ',') text     FROM ( select 1 verse, rownum piece, file_name from dba_data_files )

      CONNECT BY verse = PRIOR verse
             AND piece - 1 = PRIOR piece
      START WITH piece = 1

    )x
) y
where rn=1
/  

Any advice or suggestion are most welcome. Tx
Sanjay

Sanjay Mishra <smishra_97_at_yahoo.com> wrote:

        Ken          

	SQL> select stragg(table_name) from user_tables
	  2  /
	select stragg(table_name) from user_tables
	*
	ERROR at line 1:
	ORA-06502: PL/SQL: numeric or value error
	ORA-06512: at "SYS.STRING_AGG_TYPE", line 17
	ORA-06512: at line 1
	
	It works for Group by Clauses like
	 select table_name,stragg(index_name) from user_indexes group by

table_name                   

	Regds
	Sanjay


	Ken Naim <kennaim_at_gmail.com> wrote: 

		Search for stragg on http://asktom.oracle.com
		 
		


________________________________


		From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra
		Sent: Thursday, May 04, 2006 1:12 PM
		To: oracle-l_at_freelists.org
		Subject: Sql Query
		 
		Sql Gurus
		 
		I need to create the sql script that can display all

tables from user_tables in one line seperated by ",". Is there any straighforward SQL that can display the result in Horizontal instead of single table in each row.                  

		Regds
		Sanjay
		


________________________________


		Yahoo! Mail goes everywhere you do. Get it on your phone

<http://us.rd.yahoo.com/evt=31132/*http:/mobile.yahoo.com/services?promo te=mail> .

---

        How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates.
<http://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.co m/evt=39663/*http://voice.yahoo.com>

---

Yahoo! Messenger with Voice.
<http://us.rd.yahoo.com/mail_us/taglines/postman3/*http://us.rd.yahoo.co m/evt=39666/*http://messenger.yahoo.com> PC-to-Phone calls for ridiculously low rates.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 18 2006 - 10:44:00 CDT