Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: WHERE Clause Question

Re: WHERE Clause Question

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 7 Sep 1999 18:15:48 +0200
Message-ID: <7r3dov$7qq$1@oceanite.cybercable.fr>


The order generate by the connect by clause is kept. The effect of the where is only to eliminate the rows that don't satisfy it.

But you may have results that is not consistent with the meaning of your data.

For example, with the statement (that has no meaning):

SQL>select level, privilege#
  2 from sys.sysauth$
  3 where privilege# = 6 or privilege# = -175   4 connect by grantee# = prior privilege#   5 start with grantee# = userenv('SCHEMAID');

     LEVEL PRIVILEGE#
---------- ----------

         2       -175
         2          6
         3       -175

it seems that the second privilege -175 is child of privilege 6 or this is wrong: it is child of privilege 7 but this one is not selected.
Here's an extract of the query without the where clause:

     LEVEL PRIVILEGE#

---------- ----------
         1        -90
         1        -15
         1          4
         2       -175
...
         2          6
...
         2          7
         3       -175

...

Martin Douglas a écrit dans le message <37D522D1.CFB6703D_at_Boeing.com>...
>Greetings,
>
>When you add a WHERE clause on a SELECT statement that includes a
>CONNECT BY and START WITH, does it simply weed out records while
>maintaining the order of the others respectively, or is all order lost
>and undefined at that point? If so, would a ORDER BY LEVEL guarantee
>the order back again?
>
>Thanks!
Received on Tue Sep 07 1999 - 11:15:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US