Using CTE to assign level code to item how to retrieve the last item that already in the trail string

Question

I am using the following query to assign level code to item.

  ;with C ( Ingredient_Item_No,
      Lvl,        
      Trail) 
  as (
  select       
        Matl.Ingredient_Item_No, 
        2 as Lvl,           
        CAST(('/' + Matl.Ingredient_Item_No + '/') as varchar(max)) as Trail 
  from Materials as Matl
  and Matl.Product_Item_No = Lvl.Item_No      
  where Lvl.Level_Code = 1


  union all

    select     
        Matl.Ingredient_Item_No, 
        C.Lvl + 1,           
        C.trail +  CAST((Matl.Ingredient_Item_No +'/') as varchar(max))
    from NVA_Work_Rollup_BOM_Materials as Matl
    inner join C
    on Matl.Product_Item_No = C.Ingredient_Item_No  
    where CHARINDEX(CAST((Matl.Ingredient_Item_No + '/') as varchar(max)),     C.trail) = 0 
)
select * from C

The "Material" table structure is like this:

Product                Ingredient
   A                       B
   B                       C
   D                       E
   E                       F
   C                       A

The Level_Code have a list of item that already been assigned with level 1. I use the trial column to store the ingredient hierarchy. Whenever the ingredient is already in the trail for the item, I will not assign it with another level. But that also means there is bad record that not supposed to be in the material table. For example, if I have A (1) -> B (2) -> C (3) -> A, A could not be the ingredient of the product C since it is a low level product. it also means that the pair (c (product), A (ingredient)) is a wrong record that need to be take out from 'Material' table. My problem is that I could use the trail to keep track the right order. But how could I retrieve the last pair that with wrong order, such as C -> A?

Edit:

Here is what table 'Lvl' Looks like

   item_no             level_Code
      A                     1
      B                     1

Any help will be appreciated!

I'm not quite clear on the question, but [this answer](http://stackoverflow.com/questions/15080922/infinite-loop-cte-with-option-maxrecursion-0/15081353#15081353) is an example of how to detect and terminate infinite loops in "bad" hierarchies. — HABO, Sep 28 '15 at 21:24
@HABO, I just dont know how could i get that pair from this query — TaroYuki, Sep 28 '15 at 21:34
If you are trying to find a row that causes a loop, try running the example from the answer I linked. (It's a self-contained example with the data and query.) Rows with `Loop` equal to `1` are the rows that you want. Or I still don't understand the question. — HABO, Sep 28 '15 at 21:40
You seem to be missing a `WHERE` clause in the first subquery. — John Bollinger, Sep 28 '15 at 21:43
And what the heck is table `Lvl` that is referenced in your stray predicates? — John Bollinger, Sep 28 '15 at 21:44
Where does `Level_Code` come from? It isn't in your `Material` table, so it's hard to find a starting point. — HABO, Sep 28 '15 at 21:48

HABO · Accepted Answer · 2015-09-28T22:02:12.213

Updated answer: Sorry, that was rather buggy.

With some horribly named Level i stuff:

-- Sample data.
declare @Material as Table ( Product VarChar(10), Ingredient VarChar(10) );
insert into @Material ( Product, Ingredient ) values
  ( 'A', 'B' ), ( 'B', 'C' ), ( 'D', 'E' ), ( 'E', 'F' ), ( 'C', 'A' );
--select * from @Material;
declare @Lvl as Table ( ImALevel1Product VarChar(10) );
insert into @Lvl ( ImALevel1Product ) values ( 'A' ), ( 'B' );
select *
  from @Material as M left outer join
    @Lvl as L on L.ImALevel1Product = M.Product;
declare @False as Bit = 0, @True as Bit = 1;

-- Run through the hierarchy looking for loops.
with Children as (
  select Product, Ingredient,
    Convert( VarChar(4096), '|' + Convert( VarChar(10), Product ) + '|' ) as Path, @False as Loop
    from @Material
    where Product in ( select ImALevel1Product from @Lvl )
  union all
  select Child.Product, Child.Ingredient,
    Convert( VarChar(4096), Path + Convert( VarChar(10), Child.Product ) + '|' ),
    case when Path like '%|' + Convert( VarChar(10), Child.Ingredient ) + '|%' then @True else @False end
    from @Material as Child inner join
      Children as Parent on Parent.Ingredient = Child.Product
    where Parent.Loop = 0 )
  select *
    from Children
    option ( MaxRecursion 0 )

Using CTE to assign level code to item how to retrieve the last item that already in the trail string

1 Answers1