Connect_by in TOAD SQL Modeler

[FredBear]

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.

John Thompson

[Noel]

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