Hello Everybody,
I have a requirement where the query needs to looped.
I have a table f553002A. In this table i have iykit,iyitm as columns.
for every iykit,there can be a set of iyitm
like this
select KIT, ITM from F553002A where KIT = 11758
KIT ITM
11758 11494
11758 11705
11758 11713
11758 11868
Again for every ITM as KIT in the same table ,there can have set of values(ITM) like this
select KIT, ITM from F553002A where KIT = 11713 (parent’s ITM)
KIT ITM
11713.0 2917.0
11713.0 2910.0
11713.0 5013.0
11713.0 2890.0
11713.0 2873.0
11713.0 2843.0
11713.0 2856.0
11713.0 2836.0
11713.0 2879.0
11713.0 7974.0
11713.0 2789.0
11713.0 2842.0
11713.0 2915.0
11713.0 2885.0
11713.0 2908.0
11713.0 2858.0
11713.0 2871.0
11713.0 2841.0
the same with 11494,11705,11868.
Again 2917.0 (ITM) in the above example as KIT can have another set of ITM's in the same table
I want all the END ITM's along with there Grand parent KIT and Immediate Parent Kit returned in a result set.
For Ex
11758 11713.0 2917.0
....
...
..
.
11758 11494 ...........................
........
...
..
.
However i know i can achieve this with Stored procdeure. I want this to be implemented in SQL Query where i can embeed it in a VBscript
Please help me in writing the query for this. Thanks in advance
We are using sql server 2000. not 2005
here you go..
Code Snippet
Create Table #f553002a (
[KIT] int ,
[ITM] int
);
Insert Into #f553002a Values(1,2);
Insert Into #f553002a Values(1,3);
Insert Into #f553002a Values(1,4);
Insert Into #f553002a Values(1,5);
Insert Into #f553002a Values(2,6);
Insert Into #f553002a Values(3,8);
Insert Into #f553002a Values(4,9);
Insert Into #f553002a Values(5,10);
Insert Into #f553002a Values(6,11);
Insert Into #f553002a Values(7,12);
Insert Into #f553002a Values(8,13);
Insert Into #f553002a Values(9,14);
Insert Into #f553002a Values(10,2);
Insert Into #f553002a Values(11,3);
;With CTE(KIT,ITM,Level)
as
(
Select KIT,ITM,1 Level from #f553002a Where KIT = 3 --@.StartKitValue
Union ALL
Select Child.KIT,Child.ITM,Main.Level + 1 From CTE Main
Join #f553002a Child On Main.Itm = Child.Kit
)
Select KIT,ITM From CTE Order By Level
|||Can any body through a light why the query below is returning syntax error
With CTE(IYKIT,IYITM,Level)
as
(
Select IYKIT,IYITM,1 Level from f553002a Where IYKIT = 11758 --@.StartKitValue
Union ALL
Select Child.IYKIT,Child.IYITM,Main.Level + 1 From CTE Main
Join f553002a Child On Main.IYItm = Child.IYKit
)
Select IYKIT,IYITM From CTE Order By Level
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.
I am running this on sql server 2005 Management studio.
How ever in my question,i requested for a solution which can work in SQL server 2000, but this works only in sql server 2005
|||CTE only work with SQL Server 2005. You missed the semicolon ; before the WITH.
In 2000 you can use recursive sp call... let me try this..
sql