Noel
*nix forums addict
Joined: 25 Mar 2005
Posts: 67
|
 Posted: Wed Apr 13, 2005 7:47 am Post subject:
Re: Connect_by in TOAD SQL Modeler
|
|
|
Użytkownik johnathompson napisał:
| Quote: | 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 |
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
»
Tools
