Re: Connect_by in TOAD SQL Modeler

Użytkownik johnathompson napisał:

> Does anybody know if you can use TOAD's SQL Modeler to build a
> connect_by query? I can execute a SQL script that includes connect_by
> in TOAD, but I can't figure out how to model one.
>
> I've tried the TOAD FAQ and the Yahoo TOAD Users group, but no joy.
>
> As far as I know, Oracle's Query Builder and Discoverer can't handle
> connect_by at all, though I haven't tried Discoverer 10g yet.

I don't know if TOAD can do this, but it's better understand and study syntax of that kind of SQL queries.

I give example, maybe it helps you...

--
-- Table with 'self-reference' parrent_id->id

CREATE TABLE SHOW_TREE
  (
  ID          NUMBER,
  DESCRIPTION VARCHAR2(50),
  Parrent_ID  NUMBER
);
--
-- Some data

INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(1,'Root row',NULL);
INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(2, 'Child row 
level one',1);
INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(3, 'Child row 
level one',1);
INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(4, 'Child row 
level two',2);
INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(5, 'Child row 
level two',3);
INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(6, 'Child row 
level two',3);
INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(7, 'Child row 
level three',5);
INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(8, 'Child row 
level three',6);
INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(9, 'Child row 
level three',6);
INSERT INTO SHOW_TREE(ID,DESCRIPTION, Parrent_ID) VALUES(10,'Child row 
level three',6);

COMMIT;

--
-- Show tree from root to leaves

SELECT Level, Description, LPAD(' ',level,' ')||description desc2, id
   FROM SHOW_TREE
CONNECT BY PRIOR  id = parrent_id
  START WITH parrent_id IS NULL;

-- Show reverse tree

SELECT Level, Description, LPAD(' ',level,' ')||description desc2, id
   FROM SHOW_TREE
CONNECT BY PRIOR  parrent_id = id
  START WITH parrent_id =6;



-- 
Noel