RE: Sql Query

btw., statistics on this query are much better then with hierarhical
query.
 
Looks like I'm talking to myself here :)
 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Igor Neyman
Sent: Thursday, May 18, 2006 2:39 PM
To: smishra_97@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Sql Query


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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Igor Neyman
Sent: Thursday, May 18, 2006 1:59 PM
To: smishra_97@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Igor Neyman
Sent: Thursday, May 18, 2006 11:44 AM
To: smishra_97@xxxxxxxxx; kennaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sanjay Mishra
Sent: Thursday, May 18, 2006 10:56 AM
To: kennaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxx> wrote: 

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

                From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sanjay Mishra
                Sent: Thursday, May 04, 2006 1:12 PM
                To: oracle-l@xxxxxxxxxxxxx
                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.

Other related posts: