Need Help Passing Parameter in CTE Recurrsive Quer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need Help Passing Parameter in CTE Recurrsive Quer

Author

Topic

spdhiva
Starting Member

13 Posts

Posted - 2006-10-06 : 12:29:55

Hello,

I need some help to get the Hierarchy information (Recurrsive table data) in a standard format.

The Recursive table with the following column names

ID,Description,ParentID,Depth

I have the following values

1 USA NULL 0
2 FL 1 1
3 CA 1 1
4 GA 1 1
5 Miami 2 2
6 Tampa 2 2
7 PlanA 5 3
8 PlanB 6 3

I would like to pass USA ,Fl, Miami as a parameter and Get the Plan Information. I have written the CTE qry to pull the Hierarchy information for USA, But I don't know how to pass the 2nd parameter and get into the next level.

Could you guys help me?

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-06 : 13:23:57

quote:
I have written the CTE qry to pull the Hierarchy information for USA, But I don't know how to pass the 2nd parameter and get into the next level.

Please post the query that you've already written and we can help you with fixing it.

spdhiva
Starting Member

13 Posts

Posted - 2006-10-06 : 14:24:26

with NewTree (intValOptionDetailID,strDescription,intParentOptionDetailID,intTreeDepth)
as
(
Select
A.ID,
A.Description,
A.ParentID,
A.Depth
from dbo.Table1 A
where A.ID = 1

Union All

Select
A.ID,
A.Description,
A.ParentIDID,
A.Depth
from dbo.Table1 A,
NewTree B
where A.ParentID = B.ID
AND A.Depth >= B.Depth
)
Select * from NewTree

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-07 : 12:07:15

OK, this will give you what you want. Note that the first query in the CTE is the anchor query and only runs once, so what ever you put in there is the basis for selecting the starting rows and that's where you need to put the WHERE clause that selects FL, Miami etc. The recursive query then runs using those rows as the root and populates the rest of the rows. Note also that the depth is calculated by the recursive query, you don't put it in your source data (you can but it is not necessary.

See here for more info
http://msdn2.microsoft.com/en-us/library/ms186243.aspx

For anyone reading this, note that CTEs are a new feature of SQL Server 2005, don't try this on SQL Server 2000.



CREATE TABLE #table1 ( 
  Id          INT, 
  Description VARCHAR(20), 
  Parentid    INT) 

INSERT #table1 
SELECT 1, 
       'USA', 
       NULL 
UNION ALL 
SELECT 2, 
       'FL', 
       1 
UNION ALL 
SELECT 3, 
       'CA', 
       1 
UNION ALL 
SELECT 4, 
       'GA', 
       1 
UNION ALL 
SELECT 5, 
       'Miami', 
       2 
UNION ALL 
SELECT 6, 
       'Tampa', 
       2 
UNION ALL 
SELECT 7, 
       'PlanA', 
       5 
UNION ALL 
SELECT 8, 
       'PlanB', 
       6 

WITH Newtree(Intvaloptiondetailid,Strdescription,Intparentoptiondetailid,Inttreedepth) 
     AS (SELECT A.Id, 
                A.Description, 
                A.Parentid, 
                1 
         FROM   #table1 A 
         WHERE  A.Description = 'FL' 
         UNION ALL 
         SELECT A.Id, 
                A.Description, 
                A.Parentid, 
                B.Inttreedepth + 1 
         FROM   #table1 A, 
                Newtree B 
         WHERE  A.Parentid = B.Intvaloptiondetailid) 
SELECT * 
FROM   Newtree
/* Powered by General SQL Parser (www.sqlparser.com) */

Output

intValOptionDetailID strDescription       intParentOptionDetailID intTreeDepth
-------------------- -------------------- ----------------------- ------------
2                    FL                   1                       1
5                    Miami                2                       2
6                    Tampa                2                       2
8                    PlanB                6                       3
7                    PlanA                5                       3

spdhiva
Starting Member

13 Posts

Posted - 2006-10-10 : 11:03:16

Hi,

Thanks for your response.

As per your suggestion, It takes the Top node and display all itz children nodes. I would like to see the childern in a rowwise. In your result set , it takes me into two paths (Miami and Tampa). But I would like to see these two paths in a row wise.

USA FL Miami Plan A
USA FL Tampa Plan B

I would like to explain the process little bit in detail.

We receive the customer information in a text file with their geographical details and We need to compare the data with our tree structure and assign the plan based on their geo' locations.

It is easy to join the data with a flat table and get the Plan. We want to have a flexible model where we don't need to change the model often.

Is there any other options, where I could reach the END NODE?

Thanks.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-10 : 12:49:21

I can't try out the code rigth now, but it sounds like what you'd need to do is basically go backwards. So start with the city in the anchor query and use the recursive query to find its parent - rather than starting at the top with the country and finding all the children.

spdhiva
Starting Member

13 Posts

Posted - 2006-10-13 : 15:39:35

I find a one method to get into the point.
I created a view, which pulls all the tree view along with the Extra column, which combines all the Nodes with the Delimiter and I pass the Parameter in the same format to get the particular node.

View:
with NewTree (intValOptionDetailID,strDescription,intParentOptionDetailID,intTreeDepth,strString)
as
(
Select
A.ID,
A.Description,
A.ParentID,
A.Depth,
A.Description as strString
from dbo.Table1 A
where A.ID = 1

Union All

Select
A.ID,
A.Description,
A.ParentIDID,
A.Depth,
B.strString + '|'+A.Description
from dbo.Table1 A,
NewTree B
where A.ParentID = B.ID
AND A.Depth >= B.Depth
)
Select * from NewTree Where strString = @strInputParm

1 USA NULL 0 USA
2 FL 1 1 USA|FL
3 CA 1 1 USA|CA
4 GA 1 1 USA|GA
5 Miami 2 2 USA|FL|Miami
6 Tampa 2 2 USA|FL|Tampa
7 PlanA 5 3 USA|FL|Miami|Plan A
8 PlanB 6 3 USA|FL|Tampa|Plan B

@strInputParam = USA|FL|Tampa

Thanks for your help. If you could think about any other solution, that would be great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources