Solved

Oracle 'CONNECT BY PRIOR' Equivalent in MS-SQL

Posted on 2001-08-01
7
6,941 Views
Last Modified: 2009-05-28
Hi,
I've been working with Oracle for quite sometime now. But, off late I've started working with SQL Server. I would like to get the equivalent command/SQL statement for the 'CONNECT BY PRIOR' functionality of Oracle, in SQL Server. I've been searching for sometime & am unable to find the same. I need this urgently.

Thanks,
Deepak Ganesan
0
Comment
Question by:deepakg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6343428
There is no such thing in SQL Server :-(
thus the only thing you can do is implement it using some temporary tables and a stored proc...

Cheers
0
 
LVL 2

Author Comment

by:deepakg
ID: 6343453
angel,
I know that there is an equivalent is SQL Server for 'CONNECT BY PRIOR' functionality of Oracle..
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6343525
Just to put things clear:
you are asking for the functionality CONNECT BY PRIOR, telling me that you know that it exists. So you are only asking for it's name??
I have been working some years with SQL Server and with Oracle, and i have not seen the CONNECT BY PRIOR in SQL Server (including SQL2000).
I know that ADO (used in VB/C++) can use the Shaping Provider to build recordsets that are parent-child, but still not the same as Oracle's CONNECT BY PRIOR...

I would like to hear the SQL Server's name for this functionality, but as far as i know, it's only Oracle that supplies this (strong) extention to the ANSI standards...

Cheers
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6347522
0
 
LVL 2

Author Comment

by:deepakg
ID: 6371166
Hi angelll,
Do you have any sample code for reproducing the CONNECT BY Functionality in SQL Server as you've been saying the same can be accomplished by using some temp. tables & stored procedure..

thanks,
Deepak.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 6372290
Find below the code of a stored proc that finds the hierarchy data in some tables...
Sorry if it isn't too explicit, but i coulnd't comment it better for the moment...

Hope this helps

CREATE PROCEDURE
     tsBuildHierarchy
     @root           VARCHAR ( 100 ),
     @owner     VARCHAR ( 30 ),
--      This flag indicates if the resulting data is immediately returned, or if the calling
--     procedure will use the contents of the global table ##tmpFolders itself
     @return          int      = 1              
AS

IF      @return = 1
     DROP TABLE ##tmpFolders

IF      @return <> 3
BEGIN
     IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE name = '##tmpFolders'  )
          DROP TABLE ##tmpFolders

     CREATE TABLE ##tmpFolders
          (
          oID int , pID int, oLevel int, oName varchar(255), oType int ,
          ROW int IDENTITY (1,1)
          )
END    

--      maybe we didn't be able to DROP/CREATE the table, we will then try to empty the table
TRUNCATE TABLE ##tmpFolders


DECLARE     --     store the id's of the owner and the root folder
     @oID     int,
     @rID     int

DECLARE     --      control variables
     @level          int,
     @lastcount     int

--      Translate @root and @owner to @rID and @oID
IF ( ISNUMERIC ( @owner )  =  1 )
     SET @oID = CAST ( @owner AS INT )
ELSE
     SELECT
          @oID =      o.[user_id]
     FROM
          amdb..benutzer     o          
     WHERE
          o.displayname = @owner

IF (ISNUMERIC ( @root ) = 1)
     SET @rID = CAST ( @root AS INT )
ELSE
     SELECT
          @rID = o.obj_id
     FROM
          celldb..objekt     o
     WHERE
          o.[name] = @root
     AND     o.bearbeiter = @oID

--     The root folder has 0 as level, the others will have 1 to n
SET     @level = 0

INSERT INTO
     ##tmpFolders
SELECT
     obj_id , NULL, @level, [name], objkl_id
FROM
     celldb..objekt
WHERE      1=1
AND     obj_id = @rId
AND     bearbeiter = @oId


--     Check if any (normally 0 or 1) rows have been inserted
SET     @lastcount = @@rowcount


WHILE @lastcount <> 0
--     as long as rows are inserted, we must proceed
BEGIN
     --      proceed next level
     SET @level = @level + 1    

     --      insert the folders/items on the next level
     INSERT INTO
          ##tmpFolders
     SELECT
          o.obj_id , p.oID,  @level, o.[name], o.objkl_id
     FROM
          ##tmpFolders     p
     JOIN
          celldb..objsch     r
     ON    
          r.vater_obj_id = p.oID
     AND     p.olevel = @level - 1
     JOIN    
          celldb..objekt     o
     ON    
          o.obj_id = r.sohn_obj_id
     
     --      determine how many rows have been entered this time
     SET @lastcount = @@rowcount
END


--     Check if we have to return the table
IF @return = 1 or @return = 2
BEGIN
     CREATE INDEX IDXl ON ##tmpFolders ( oLevel )
     
     --     We return the entire table, ordered by level
     SELECT
          *
     FROM
          ##tmpFolders
     ORDER BY
          oLevel        
END

0
 

Expert Comment

by:CleanupPing
ID: 9281917
deepakg:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question