Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Reading column into a string variable

Re: Reading column into a string variable

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 14 Nov 2006 02:35:51 -0800
Message-ID: <1163500551.435567.133660@h48g2000cwc.googlegroups.com>


Michel Cadot wrote:
> "Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news: 1163452050.065616.276080_at_f16g2000cwb.googlegroups.com...
> | 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
> |
> | 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

Very nice improvement.

I admit that I was concerned that placing a WHERE clause in that spot would prevent the CONNECT BY PRIOR syntax from working properly - but it works exactly as you describe. While not apparent with this small data set, the WHERE clause can make a significant impact on larger data sets. Modifcation of my query (same output as your query above): SELECT
  SUBSTR(SYS_CONNECT_BY_PATH(NAME,','),2) NAME_LIST FROM
  (SELECT
    NAME,
    ROW_NUMBER() OVER (ORDER BY NAME) ROW_POSITION,     COUNT(*) OVER () ROW_COUNT
  FROM
    T1)
WHERE
  ROW_POSITION=ROW_COUNT
CONNECT BY PRIOR
  ROW_POSITION=ROW_POSITION-1
START WITH
  ROW_POSITION=1; Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Nov 14 2006 - 04:35:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US