Sometimes, I am amazed by the results one can achieve
with Oracle SQL extensions, such as analytic functions
and sys_connect_by_path below...
Would you recommand a *good* book on advanced Oracle SQL
(not PL/SQL) illustrating the use of these extensions to solve
actual problems ?
The examples in the SQL reference manual are not bad,
but - understandably - a bit too focused on the statement
or function being specified.
Thanks
Michel Cadot wrote:
> "Michel Cadot" <micadot{at}altern{dot}org> a écrit dans le message de news: 455954da$0$7731$426a34cc_at_news.free.fr...
> |
> | "Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news: 1163452050.065616.276080_at_f16g2000cwb.googlegroups.com...
> || zomer wrote:
> || > Hi,
> || > I need to read all entries of a column into a varchar variable. There
> || > are about 100 entries.... how can i do it in PL/SQL?
> || >
> || > Table
> || > Name
> || > alex
> || > brad
> || > cynthia
> || > .
> || > .
> || > .
> || > .
> || > zomer
> || >
> || > Anticipated output
> || > alex,brad,cynthia.....,zomer
> || >
> || > Thanks.
> ||
> || It should be fairly easy in PL/SQL to loop through the results of the
> || query and append the value to a variable. You can also do this
> || automatically in SQL, and then just use the result in your PL/SQL code.
> ||
> || The setup:
> || CREATE TABLE T1 (NAME VARCHAR2(20));
> || INSERT INTO T1 VALUES('alex');
> || INSERT INTO T1 VALUES('brad');
> || INSERT INTO T1 VALUES('cynthia');
> || INSERT INTO T1 VALUES('zomer');
> ||
> || The SELECT statement:
> || SELECT
> || MAX(SUBSTR(SYS_CONNECT_BY_PATH(NAME,','),2)) NAME_LIST
> || FROM
> || (SELECT
> || NAME,
> || ROW_NUMBER() OVER (ORDER BY NAME) ROW_POSITION
> || FROM
> || T1)
> || CONNECT BY PRIOR
> || ROW_POSITION=ROW_POSITION-1
> || START WITH
> || ROW_POSITION=1;
> ||
> || The output:
> || NAME_LIST
> || =========
> || alex,brad,cynthia,zomer
> ||
> || How do this work? The inline view retreives each name, and a number
> || that indicates the sorted order of the name:
> || SELECT
> || NAME,
> || ROW_NUMBER() OVER (ORDER BY NAME) ROW_POSITION
> || FROM
> || T1
> ||
> || If we then use a CONNECT BY PRIOR, starting with ROW_POSITION 1, we
> || receive as output:
> || NAME_LIST
> || ====================
> || alex
> || brad
> || cynthia
> || zomer
> ||
> || By specifying SYS_CONNECT_BY_PATH, and eliminating the prefixed , we
> || obtain:
> || NAME_LIST
> || ====================
> || alex
> || alex,brad
> || alex,brad,cynthia
> || alex,brad,cynthia,zomer
> ||
> || If we then find the maximum value, we are left with the last item:
> || NAME_LIST
> || =========
> || alex,brad,cynthia,zomer
> ||
> || Charles Hooper
> || PC Support Specialist
> || K&M Machine-Fabricating, Inc.
> ||
> |
> | A slighty better query is:
> |
> | SQL> with
> | 2 data as (
> | 3 select name,
> | 4 row_number() over (order by name) rn,
> | 5 count(*) over () cnt
> | 6 from t1
> | 7 )
> | 8 select substr(sys_connect_by_path(name,','),2) name_list
> | 9 from data
> | 10 where rn = cnt
> | 11 connect by prior rn = rn-1
> | 12 start with rn = 1
> | 13 /
> | NAME_LIST
> | --------------------------------------------------------------
> | alex,brad,cynthia,zomer
> |
> | 1 row selected.
> |
> | It avoids the grouping action by precalculating which row will contain the final result.
> |
> | Regards
> | Michel Cadot
> |
>
> Forgot to mention: of course the result is limited to 4000 characters.
> If you expect a larger result you have to use a function such as
> Tom Kyte's stragg one (see http://asktom.oracle.com)
>
> Regards
> Michel Cadot
Received on Tue Nov 14 2006 - 00:53:47 CST
