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.
Author |
Topic |
kt
Yak Posting Veteran
88 Posts |
Posted - 2013-09-24 : 15:51:03
|
The query inside in condion worked great. I now what to list those items from cib table but got error below:Incorrect syntax near the keyword 'WITH'.Msg 319, Level 15, State 1, Line 5Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.Msg 102, Level 15, State 1, Line 24Incorrect syntax near ')'.Can you pls help?select t_item, t_kitmfrom cibwhere t_item in ( WITH RecursiveBOM (LEVEL,t_mitm, t_sitm,t_kitm) AS (SELECT 1,ROOT.t_mitm, ROOT.t_sitm, itm.t_kitm FROM ttibom010110 ROOT with (nolock) inner join ttcibd001110 itm with (nolock) on ROOT.t_sitm= itm.t_item and itm.t_kitm =2 WHERE ltrim(ROOT.t_mitm) = '120400-221265TM' Union All SELECT PARENT.LEVEL+1, CHILD.t_mitm, CHILD.t_sitm,pr.t_kitm FROM RecursiveBOM PARENT, ttibom010110 CHILD,ttcibd001110 pr WHERE PARENT.t_sitm = CHILD.t_mitm and pr.t_item = CHILD.t_mitm ) SELECT Rec.t_sitm FROM RecursiveBOM as Rec INNER JOIN tticpr007110 as icpr with (nolock) ON Rec.t_sitm = icpr.t_item where t_ecpr_1 ='0' and Rec.t_kitm =2 OPTION (MAXRECURSION 10)) as xand t_kitm =1 |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-09-24 : 16:15:55
|
Maybe this:with recursivebom (level,t_mitm,t_sitm,t_kitm) as (select 1 ,root.t_mitm ,root.t_sitm ,itm.t_kitm from ttibom010110 as root with (nolock) inner join ttcibd001110 as itm with (nolock) on root.t_sitm=itm.t_item and itm.t_kitm=2 where ltrim(root.t_mitm)='120400-221265TM' union all select parent.level+1 ,child.t_mitm ,child.t_sitm ,pr.t_kitm from recursivebom as parent inner join ttibom010110 as child on child.t_mitm=parent.t_sitm inner join ttcibd001110 as pr on pr.t_item=child.t_mitm )select t_item ,t_kitm from cib where t_item in (select rec.t_sitm from recursivebom as rec inner join tticpr007110 as icpr with (nolock) on icpr.t_item=rec.t_sitm where t_ecpr_1='0' and rec.t_kitm=2 option (maxrecirsion 10) ) |
|
|
|
|
|
|
|