Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> CONNECT BY HELP
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. Received on Thu Jan 10 2002 - 09:36:31 CST