RE: Sql Query
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <smishra_97@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 May 2006 13:58:53 -0400
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:
