RE: sql help, please

  • From: Eugene Pipko <eugene.pipko@xxxxxxxxxxxx>
  • To: 'Michael Moore' <michaeljmoore@xxxxxxxxx>
  • Date: Tue, 26 Jan 2010 14:59:14 -0800

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@xxxxxxxxx]
Sent: Tuesday, January 26, 2010 2:55 PM
To: Eugene Pipko
Cc: oracle-l@xxxxxxxxxxxxx
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;

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2010