Re: Hierarchical Query Question

"Jack R. Sumner Jr." <jrsumner_at_ix.netcom.com> wrote:

>Hi. I am having trouble with a hierarchical query and I thought someone
>out there might have run across this before and be able to help.
 

>I need to be able to start at the top of a hierarchy (100 below) and
>perform a sum on a column (say PRICE) in each record. The catch is that
>the summation can only include those records where a flag (represented
>by the =91*=92 below) is set to TRUE.
 

> 100*
> 200* 201*
> 301* 302 303 304*
> 401* 402
 

>In this example starting at 100, I need to sum up the PRICE column for
>100, 200, 201, 301, 304, & 401.
 

>Any ideas on how to do this with a hierarchical query?
 

>Thank you in advance,
>Jack Sumner (jrsumner_at_ix.netcom.com)

I do not have access to my doc nor test bed however it is my recollection that the CONNECT BY and START WITH clauses will direct the RDBMS to traverse a specific branch of your hierarchy. This is seperate and distinct from the WHERE clause which will filter the specified records from the branch. As a result the combination of the following statements (in completely unverified order and syntax) would be something like:
SELECT
        sum(Price)
FROM
        table_in_question
WHERE
        Indicator = '*'
CONNECT BY

	T.PKey_of_table = PRIOR T.FKey_Pointing_To_Parent
START WITH	T.PKey_of_Table = 100

Please excuse the erroneous syntax, however my point is that there is a seperation of the use of the (CONNECT BY, START WITH) clauses and the WHERE clause.

Another ugly technique would be to alter the above and utilize SUM ( DECODE (Indicator,'*', Price, 0) ) as the subject of your where clause (instead of utilizing the WHERE clause indicated.

Regards... Received on Thu Jan 16 1997 - 00:00:00 CET