Hierarchical Subquery must omit nodes
in [Oracle Help]
|
From: Chris L. on 16 Apr 2008 13:16 Hi all, I thought this would be easy but it's turning out to be pretty difficult. I need a hierarchical query to omit nodes present in a EXCLUDED_NODES table. Omitted nodes' children should be omitted too. See this example taken from http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions121a.htm#88362 SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees START WITH last_name = 'Kochhar' CONNECT BY PRIOR employee_id = manager_id; Path --------------------------------------------------------------- /Kochhar /Kochhar/Greenberg /Kochhar/Greenberg/Faviet /Kochhar/Greenberg/Chen /Kochhar/Greenberg/Sciarra /Kochhar/Greenberg/Urman /Kochhar/Greenberg/Popp /Kochhar/Whalen /Kochhar/Mavris /Kochhar/Baer /Kochhar/Higgins /Kochhar/Higgins/Gietz If I have Greenberg in EXCLUDED_NODES then output should be Path --------------------------------------------------------------- /Kochhar /Kochhar/Whalen /Kochhar/Mavris /Kochhar/Baer /Kochhar/Higgins /Kochhar/Higgins/Gietz Excluded node can be in any depth of the hierarchy (i.e. it could be Gietz and then Gietz and all of its dependent sub-tree should be pruned) I've tried filtering the START WITH and works great but doesn't help when the excluded node is somewhere deep in the hierarchy. I've tried filtering the CONNECT BY but it cannot contain subqueries (manual states this). I've tried using the SYS_CONNECT_BY_PATH to parse it and see if it contains an excluded node but I got an Ora-00600 error. ORA-00600: internal error code, arguments: [rworupo.2], [26670], [1], [], [], [], [], [] Oracle version is 9i Thanks in advance! Chris
From: fitzjarrell on 16 Apr 2008 14:17 On Apr 16, 12:16 pm, "Chris L." <diver...(a)uol.com.ar> wrote: > Hi all, > > I thought this would be easy but it's turning out to be pretty > difficult. > > I need a hierarchical query to omit nodes present in a EXCLUDED_NODES > table. > Omitted nodes' children should be omitted too. > > See this example taken fromhttp://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functi... > > SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') > "Path" > FROM employees > START WITH last_name = 'Kochhar' > CONNECT BY PRIOR employee_id = manager_id; > > Path > --------------------------------------------------------------- > /Kochhar > /Kochhar/Greenberg > /Kochhar/Greenberg/Faviet > /Kochhar/Greenberg/Chen > /Kochhar/Greenberg/Sciarra > /Kochhar/Greenberg/Urman > /Kochhar/Greenberg/Popp > /Kochhar/Whalen > /Kochhar/Mavris > /Kochhar/Baer > /Kochhar/Higgins > /Kochhar/Higgins/Gietz > > If I have Greenberg in EXCLUDED_NODES then output should be > > Path > --------------------------------------------------------------- > /Kochhar > /Kochhar/Whalen > /Kochhar/Mavris > /Kochhar/Baer > /Kochhar/Higgins > /Kochhar/Higgins/Gietz > > Excluded node can be in any depth of the hierarchy (i.e. it could be > Gietz and then Gietz and all of its dependent sub-tree should be > pruned) > > I've tried filtering the START WITH and works great but doesn't help > when the excluded node is somewhere deep in the hierarchy. > > I've tried filtering the CONNECT BY but it cannot contain subqueries > (manual states this). > > I've tried using the SYS_CONNECT_BY_PATH to parse it and see if it > contains an excluded node but I got an Ora-00600 error. > > ORA-00600: internal error code, arguments: [rworupo.2], [26670], [1], > [], [], [], [], [] > > Oracle version is 9i > > Thanks in advance! > Chris Why wouldn't this work: -- Your original example SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 2 FROM employees 3 START WITH last_name = 'Kochhar' 4 CONNECT BY PRIOR employee_id = manager_id; Path -------------------------------------------------------------------------------- /Kochhar /Kochhar/Greenberg /Kochhar/Greenberg/Faviet /Kochhar/Greenberg/Chen /Kochhar/Greenberg/Sciarra /Kochhar/Greenberg/Urman /Kochhar/Greenberg/Popp /Kochhar/Whalen /Kochhar/Mavris /Kochhar/Baer /Kochhar/Higgins /Kochhar/Higgins/Gietz 12 rows selected. -- 'Filtered' example SQL> select "Path" 2 from 3 (SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 4 FROM employees 5 START WITH last_name = 'Kochhar' 6 CONNECT BY PRIOR employee_id = manager_id) 7 where instr("Path", 'Greenberg') = 0; Path -------------------------------------------------------------------------------- /Kochhar /Kochhar/Whalen /Kochhar/Mavris /Kochhar/Baer /Kochhar/Higgins /Kochhar/Higgins/Gietz 6 rows selected. SQL> David Fitzjarrell
From: Urs Metzger on 16 Apr 2008 14:45 fitzjarrell(a)cox.net schrieb: > On Apr 16, 12:16 pm, "Chris L." <diver...(a)uol.com.ar> wrote: >> Hi all, >> >> I thought this would be easy but it's turning out to be pretty >> difficult. >> >> I need a hierarchical query to omit nodes present in a EXCLUDED_NODES >> table. >> Omitted nodes' children should be omitted too. >> >> See this example taken fromhttp://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functi... >> >> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') >> "Path" >> FROM employees >> START WITH last_name = 'Kochhar' >> CONNECT BY PRIOR employee_id = manager_id; >> >> Path >> --------------------------------------------------------------- >> /Kochhar >> /Kochhar/Greenberg >> /Kochhar/Greenberg/Faviet >> /Kochhar/Greenberg/Chen >> /Kochhar/Greenberg/Sciarra >> /Kochhar/Greenberg/Urman >> /Kochhar/Greenberg/Popp >> /Kochhar/Whalen >> /Kochhar/Mavris >> /Kochhar/Baer >> /Kochhar/Higgins >> /Kochhar/Higgins/Gietz >> >> If I have Greenberg in EXCLUDED_NODES then output should be >> >> Path >> --------------------------------------------------------------- >> /Kochhar >> /Kochhar/Whalen >> /Kochhar/Mavris >> /Kochhar/Baer >> /Kochhar/Higgins >> /Kochhar/Higgins/Gietz >> >> Excluded node can be in any depth of the hierarchy (i.e. it could be >> Gietz and then Gietz and all of its dependent sub-tree should be >> pruned) >> >> I've tried filtering the START WITH and works great but doesn't help >> when the excluded node is somewhere deep in the hierarchy. >> >> I've tried filtering the CONNECT BY but it cannot contain subqueries >> (manual states this). >> >> I've tried using the SYS_CONNECT_BY_PATH to parse it and see if it >> contains an excluded node but I got an Ora-00600 error. >> >> ORA-00600: internal error code, arguments: [rworupo.2], [26670], [1], >> [], [], [], [], [] >> >> Oracle version is 9i >> >> Thanks in advance! >> Chris > > Why wouldn't this work: > > -- Your original example > SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') > "Path" > 2 FROM employees > 3 START WITH last_name = 'Kochhar' > 4 CONNECT BY PRIOR employee_id = manager_id; > > Path > -------------------------------------------------------------------------------- > /Kochhar > /Kochhar/Greenberg > /Kochhar/Greenberg/Faviet > /Kochhar/Greenberg/Chen > /Kochhar/Greenberg/Sciarra > /Kochhar/Greenberg/Urman > /Kochhar/Greenberg/Popp > /Kochhar/Whalen > /Kochhar/Mavris > /Kochhar/Baer > /Kochhar/Higgins > /Kochhar/Higgins/Gietz > > 12 rows selected. > > -- 'Filtered' example > SQL> select "Path" > 2 from > 3 (SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, > '/') "Path" > 4 FROM employees > 5 START WITH last_name = 'Kochhar' > 6 CONNECT BY PRIOR employee_id = manager_id) > 7 where instr("Path", 'Greenberg') = 0; > > Path > -------------------------------------------------------------------------------- > /Kochhar > /Kochhar/Whalen > /Kochhar/Mavris > /Kochhar/Baer > /Kochhar/Higgins > /Kochhar/Higgins/Gietz > > 6 rows selected. > > SQL> > > > David Fitzjarrell David, which Oracle version are you using? Urs Metzger
From: Shakespeare on 16 Apr 2008 15:21 "fitzjarrell(a)cox.net" <oratune(a)msn.com> schreef in bericht news:57056886-02e4-4188-9b29-b7ddf517bbb7(a)a70g2000hsh.googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver...(a)uol.com.ar> wrote: > Hi all, > > I thought this would be easy but it's turning out to be pretty > difficult. > > I need a hierarchical query to omit nodes present in a EXCLUDED_NODES > table. > Omitted nodes' children should be omitted too. > > See this example taken > fromhttp://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functi... > > SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') > "Path" > FROM employees > START WITH last_name = 'Kochhar' > CONNECT BY PRIOR employee_id = manager_id; > > Path > --------------------------------------------------------------- > /Kochhar > /Kochhar/Greenberg > /Kochhar/Greenberg/Faviet > /Kochhar/Greenberg/Chen > /Kochhar/Greenberg/Sciarra > /Kochhar/Greenberg/Urman > /Kochhar/Greenberg/Popp > /Kochhar/Whalen > /Kochhar/Mavris > /Kochhar/Baer > /Kochhar/Higgins > /Kochhar/Higgins/Gietz > > If I have Greenberg in EXCLUDED_NODES then output should be > > Path > --------------------------------------------------------------- > /Kochhar > /Kochhar/Whalen > /Kochhar/Mavris > /Kochhar/Baer > /Kochhar/Higgins > /Kochhar/Higgins/Gietz > > Excluded node can be in any depth of the hierarchy (i.e. it could be > Gietz and then Gietz and all of its dependent sub-tree should be > pruned) > > I've tried filtering the START WITH and works great but doesn't help > when the excluded node is somewhere deep in the hierarchy. > > I've tried filtering the CONNECT BY but it cannot contain subqueries > (manual states this). > > I've tried using the SYS_CONNECT_BY_PATH to parse it and see if it > contains an excluded node but I got an Ora-00600 error. > > ORA-00600: internal error code, arguments: [rworupo.2], [26670], [1], > [], [], [], [], [] > > Oracle version is 9i > > Thanks in advance! > Chris Why wouldn't this work: -- Your original example SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 2 FROM employees 3 START WITH last_name = 'Kochhar' 4 CONNECT BY PRIOR employee_id = manager_id; Path -------------------------------------------------------------------------------- /Kochhar /Kochhar/Greenberg /Kochhar/Greenberg/Faviet /Kochhar/Greenberg/Chen /Kochhar/Greenberg/Sciarra /Kochhar/Greenberg/Urman /Kochhar/Greenberg/Popp /Kochhar/Whalen /Kochhar/Mavris /Kochhar/Baer /Kochhar/Higgins /Kochhar/Higgins/Gietz 12 rows selected. -- 'Filtered' example SQL> select "Path" 2 from 3 (SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 4 FROM employees 5 START WITH last_name = 'Kochhar' 6 CONNECT BY PRIOR employee_id = manager_id) 7 where instr("Path", 'Greenberg') = 0; Path -------------------------------------------------------------------------------- /Kochhar /Kochhar/Whalen /Kochhar/Mavris /Kochhar/Baer /Kochhar/Higgins /Kochhar/Higgins/Gietz 6 rows selected. SQL> David Fitzjarrell -------------------------------- This will go wrong if you want to exclude 'Green' but include 'Greenberg' Shakespeare
From: fitzjarrell on 16 Apr 2008 15:57 On Apr 16, 2:21 pm, "Shakespeare" <what...(a)xs4all.nl> wrote: > "fitzjarr...(a)cox.net" <orat...(a)msn.com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7(a)a70g2000hsh.googlegroups.com... > On Apr 16, 12:16 pm, "Chris L." <diver...(a)uol.com.ar> wrote: > > > > > > > Hi all, > > > I thought this would be easy but it's turning out to be pretty > > difficult. > > > I need a hierarchical query to omit nodes present in a EXCLUDED_NODES > > table. > > Omitted nodes' children should be omitted too. > > > See this example taken > > fromhttp://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functi... > > > SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') > > "Path" > > FROM employees > > START WITH last_name = 'Kochhar' > > CONNECT BY PRIOR employee_id = manager_id; > > > Path > > --------------------------------------------------------------- > > /Kochhar > > /Kochhar/Greenberg > > /Kochhar/Greenberg/Faviet > > /Kochhar/Greenberg/Chen > > /Kochhar/Greenberg/Sciarra > > /Kochhar/Greenberg/Urman > > /Kochhar/Greenberg/Popp > > /Kochhar/Whalen > > /Kochhar/Mavris > > /Kochhar/Baer > > /Kochhar/Higgins > > /Kochhar/Higgins/Gietz > > > If I have Greenberg in EXCLUDED_NODES then output should be > > > Path > > --------------------------------------------------------------- > > /Kochhar > > /Kochhar/Whalen > > /Kochhar/Mavris > > /Kochhar/Baer > > /Kochhar/Higgins > > /Kochhar/Higgins/Gietz > > > Excluded node can be in any depth of the hierarchy (i.e. it could be > > Gietz and then Gietz and all of its dependent sub-tree should be > > pruned) > > > I've tried filtering the START WITH and works great but doesn't help > > when the excluded node is somewhere deep in the hierarchy. > > > I've tried filtering the CONNECT BY but it cannot contain subqueries > > (manual states this). > > > I've tried using the SYS_CONNECT_BY_PATH to parse it and see if it > > contains an excluded node but I got an Ora-00600 error. > > > ORA-00600: internal error code, arguments: [rworupo.2], [26670], [1], > > [], [], [], [], [] > > > Oracle version is 9i > > > Thanks in advance! > > Chris > > Why wouldn't this work: > > -- Your original example > SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') > "Path" > 2 FROM employees > 3 START WITH last_name = 'Kochhar' > 4 CONNECT BY PRIOR employee_id = manager_id; > > Path > -------------------------------------------------------------------------------- > /Kochhar > /Kochhar/Greenberg > /Kochhar/Greenberg/Faviet > /Kochhar/Greenberg/Chen > /Kochhar/Greenberg/Sciarra > /Kochhar/Greenberg/Urman > /Kochhar/Greenberg/Popp > /Kochhar/Whalen > /Kochhar/Mavris > /Kochhar/Baer > /Kochhar/Higgins > /Kochhar/Higgins/Gietz > > 12 rows selected. > > -- 'Filtered' example > SQL> select "Path" > 2 from > 3 (SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, > '/') "Path" > 4 FROM employees > 5 START WITH last_name = 'Kochhar' > 6 CONNECT BY PRIOR employee_id = manager_id) > 7 where instr("Path", 'Greenberg') = 0; > > Path > -------------------------------------------------------------------------------- > /Kochhar > /Kochhar/Whalen > /Kochhar/Mavris > /Kochhar/Baer > /Kochhar/Higgins > /Kochhar/Higgins/Gietz > > 6 rows selected. > > SQL> > > David Fitzjarrell > > -------------------------------- > This will go wrong if you want to exclude 'Green' but include 'Greenberg' > > Shakespeare- Hide quoted text - > > - Show quoted text - A valid concern. I must admit this was a quick and dirty attempt. And this is with 10.2.0.3. David Fitzjarrell
|
Next
|
Last
Pages: 1 2 Prev: Query Improvement Next: HowTo find out used (useful) and unused (usesless) indexes? |