RE: Sql Query

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Mon, 22 May 2006 09:17:08 -0400
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2F653CE@EXCHANGE.corp.perceptron.com>

Thanks for correction, Jared.
Lucky for me, I jumped from 8i directly to 10g.

Igor

From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Friday, May 19, 2006 8:03 PM
To: Igor Neyman
Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org Subject: Re: Sql Query

That's a clever bit of SQL.

It doesn't work in 9i however.

On 5/18/06, Igor Neyman <ineyman_at_perceptron.com > wrote:

And one more (totaly different) solution:

	CREATE or replace TYPE file_list AS TABLE OF varchar2(513);
	/
	SELECT CAST(COLLECT(file_name) AS file_list) 
	   FROM dba_data_files
	/
	



________________________________


	
	From: oracle-l-bounce_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Igor Neyman

	Sent: Thursday, May 18, 2006 1:59 PM
	To: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
	Subject: RE: Sql Query
	
	
	
	Or, without "order by":
	 
	with T AS (
	    SELECT SYS_CONNECT_BY_PATH(file_name, ',') text,
CONNECT_BY_ISLEAF
	    FROM ( select 1 verse, rownum piece, file_name from
dba_data_files ) 
	      WHERE CONNECT_BY_ISLEAF = 1      
	      CONNECT BY verse = PRIOR verse
	             AND piece - 1 = PRIOR piece
	      START WITH piece = 1
	      )
	select text from T
	/
	
	Igor
	 


________________________________


	From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Igor Neyman
	Sent: Thursday, May 18, 2006 11:44 AM
	To: smishra_97_at_yahoo.com; kennaim_at_gmail.com;
oracle-l_at_freelists.org
	Subject: RE: Sql Query
	
	
	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 <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.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


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

Received on Mon May 22 2006 - 08:17:08 CDT