SQL Help: Extended use of START WITH CONNECT BY

I have a question about the extended use of START WITH and CONNECT BY.

I have a table with a hierarchical relationship, i.e. parent/child, thus:

SubjectID | ParentID
1         | 0
2         | 1
3         | 2

I also have a second table which expresses the non-hierarchical relationship. The primary key of the table above is the foreign key of my second table.

This is the SQL I am wressling with. The problem is that I want to add up the returned rows (21 + 115) in a 'wrapper' select. I am only using a UNION here too display both results, it is not a necessary part of the SQL logic.

SELECT (   1 select count(subjectid) from subjects   2 start with subjectid = 100514
  3 connect by prior subjectid = parentid   4 UNION
  5 select count(topicid) from subjecttopics   6 where subjectid in (
  7 select subjectid from subjects
  8 start with subjectid = 100514
  9 connect by prior subjectid = parentid  10* )

COUNT(SUBJECTID)

              21
             115

Ideally I would like to do:

SELECT ( select count(subjectid) from subjects
start with subjectid = 100514
connect by prior subjectid = parentid

+

select count(topicid) from subjecttopics where subjectid in (
select subjectid from subjects
start with subjectid = 100514
connect by prior subjectid = parentid)
)
from dual;

According to oracle, neither can I do:

select (count(s.subjectid) + count(t.topicid)) from subjects s, subjecttopics t
start with s.subjectid = 100514
connect by prior s.subjectid = parentid

since it envolves a join. HELP !!

I know I could solve the problem using a temporary table or PL/SQL, Oracle does not handle explicit temporary tables (of the form SELECT INTO TEMPORARY TABLE) so I would have to create it manually.

Any SQL guru help would be much appreciated.

charles_at_wire.co.uk Received on Thu Feb 11 1999 - 05:34:30 CST