RE: Sql Query
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: "Jared Still" <jkstill@xxxxxxxxx>
- Date: Mon, 22 May 2006 09:17:08 -0400
Thanks for correction, Jared.
Lucky for me, I jumped from 8i directly to 10g.
Igor
________________________________
From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Friday, May 19, 2006 8:03 PM
To: Igor Neyman
Cc: smishra_97@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxx > 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@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 <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.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts: