RE: sql help, please

From: Eugene Pipko <eugene.pipko_at_unionbay.com>
Date: Tue, 26 Jan 2010 14:59:14 -0800
Message-ID: <34DB87F47199374280ADFD2968CDBCFA8816034718_at_MAIL01KT.seattlepacificindustries.com>

Thank you Michael, but I am on 9.2

ORA-00904: "CONNECT_BY_ISLEAF": invalid identifier

Eugene Pipko
Seattle Pacific Industries
office: 253.872.5243
cell: 206.304.7726
P Please consider the environment before printing this e-mail

From: Michael Moore [mailto:michaeljmoore_at_gmail.com] Sent: Tuesday, January 26, 2010 2:55 PM
To: Eugene Pipko
Cc: oracle-l_at_freelists.org
Subject: Re: sql help, please

Looks like you already got your answer but ... CREATE TABLE C
(
  ANIMAL VARCHAR2(5 BYTE),
  GRP NUMBER(6)
);

Insert into C   (ANIMAL, GRP) Values   ('cat', 1);
Insert into C   (ANIMAL, GRP) Values   ('frog', 1);
Insert into C   (ANIMAL, GRP) Values   ('frog', 66);
Insert into C   (ANIMAL, GRP) Values   ('cat', 33);
Insert into C   (ANIMAL, GRP) Values   ('cat', 44);
Insert into C   (ANIMAL, GRP) Values   ('cat', 66);
Insert into C   (ANIMAL, GRP) Values   ('frog', 44);
Insert into C   (ANIMAL, GRP) Values   ('toad', 44);

COMMIT;

SELECT     animal, LTRIM( SYS_CONNECT_BY_PATH( grp, ',' ), ',' )
      FROM ( SELECT animal, ROW_NUMBER( ) OVER( PARTITION BY animal ORDER BY 1 ) rnum,  grp
              FROM c )
     WHERE CONNECT_BY_ISLEAF = 1

CONNECT BY animal = PRIOR animal AND rnum - 1 = PRIOR rnum START WITH rnum = 1;

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 26 2010 - 16:59:14 CST