Re: CONNECT BY HELP

From: timkarnold <timkarnold_at_home.com>
Date: Fri, 11 Jan 2002 13:02:29 GMT
Message-ID: <FnB%7.65827$fo.23091391@news1.rdc1.md.home.com>

"ASP Whiz" <aspwhiz_at_yahoo.com> wrote in message news:f93b01d3.0201100736.56a96a36_at_posting.google.com...
> Hello -
>
> I am a SQL Server SQL programmer that has very limited access to an Oracle
> dB that I use. I often use SQL Server's Openrowset to retrieve the
> information that I need from the Oracle dB.
>
> I need to get a list of a specified supervisor and all of the subordinates
> and the subordinates and so forth. I have tried the following query
against
> the table and only get one level down. I have my suspicions that it may be
> because one of the columns that I am using in the CONNECT BY is a
> concatenation of 2 columns. As you will see below, they have stored the
> SUPERVISOR in the table as 'lastname,firstname". Please note that I have
> tried this with Openrowset for SQL Server as well as directly in the
ORACLE
> SQL PLUS query window. Thank you much for your help.
>
> Here is the query:
> SELECT SUPERVISOR, LAST_NAME || ',' || PREFERRED_NAME
>
> FROM theTable
>
> START WITH SUPERVISOR = 'somelastname,somefirstname
>
> CONNECT BY PRIOR LAST_NAME || ',' || PREFERRED_NAME = SUPERVISOR
>
>
> Also, I cannot get the keyword LEVEL to work that I have read about in
this
> newsgroup. Can anyone shed some light on this as well. Thanks again.

From MetaLink

Bookmark Fixed font Go to End

Doc ID: Note:2172.1
Subject: CONNECT BY PRIOR using concatenated fields Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 23-DEC-1992
Last Revision Date: 14-MAY-2000

Purpose:

This bulletin discusses the use of concatenated fields in CONNECT BY PRIOR clauses for tree structured queries.

Scenario:

Consider the following table:

                   TABLE T1:    C1 C2 C3 C4
                                -- -- -- --
                                a  1
                                b  1  a  1
                                b  6  a  1
                                c  2  a  1
                                d  1  a  1
                                e  1  b  1
                                f  1  b  1
                                g  1  e  1

We want the relationship which connects nodes in this tree to be the values of C1 concatenated with C2, and C3 concatenated with C4, returning the following tree structure:

      EXAMPLE 1:  C1 C2 C3 C4
                  -- -- -- --                         a 1
                  a  1                                 |
                  b  1  a  1                  +-----+----+----+
                  e  1  b  1                 b 1   b 6  c 2  d 1
                  g  1  e  1               +----+
                  f  1  b  1      or      e 1  f 1
                  b  6  a  1               |
                  c  2  a  1              g 1
                  d  1  a  1

The SQL statement we use may be the following:

         select C1, C2, C3, C4
         from T1
         connect by prior C1||C2 = C3||C4
         start with C1||C2 is null

this SQL statement, however, returns the following:

      EXAMPLE 2:  C1 C2 C3 C4
                  -- -- -- --
                  a  1
                  b  1  a  1
                  e  1  b  1
                  g  1  e  1
                  f  1  b  1
                  d  1  a  1

Notice that the rows b 6 a 1, and c 2 a 1 are ignored. The reason for this is that the PRIOR operator has a higher precedence than the concatenation (||) operator, so that

PRIOR C1||C2 is equivalent to (PRIOR C1) || C2

therefore, row 3 of T1 table,

b 6 a 1

is looking at the PRIOR C1, which is 'b', and the current C2 which is '6' - since there are no records with C3||C4 = 'b6', this row is not returned in the tree structure.

In order to return all the rows as expected, include all concatenated fields of the prior clause in parenthesis:

          select C1, C2, C3, C4
          from T1
          connect by prior (C1||C2) = (C3||C4)
          start with C3||C4 is null

References:

SQL*Plus Reference Guide v2
SQL*Plus User's Guide v2
SQL*Language Reference Manual v6