Re: SQL Help: Extended use of START WITH CONNECT BY

Remember you can use a select in place of a table name. This is a really powerful feature often overlooked.

Hence can't you just do:

select sum(cnt)
from (
select count(subjectid) as cnt from subjects   start with subjectid = 100514
 connect by prior subjectid = parentid
  UNION
  select count(topicid) from subjecttopics   where subjectid in (
  select subjectid from subjects
  start with subjectid = 100514
  connect by prior subjectid = parentid   )
)

Charles Stewart wrote:

> 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 - 06:02:57 CST