====== {$page} ======



===== Explanation of SQL =====



This query produces a SQL of an order and it's line items.  It can be used as is, but it often useful as a basis for other Order SQL's where the data should be displayed in tree format.



===== Risk of Data Corruption if Run Improperly =====

**None**. This is a selection query and no data is modified in the running of it.



===== SQL =====



[[code_formatsql]]



declare @OrderNumber int;



declare @IncludeVariationRow bit;



set @OrderNumber = 127410;



set @IncludeVariationRow = 1;



declare @TransHeaderID int



set @TransHeaderID = (Select ID from TransHeader where TransactionType = 1 and OrderNumber = @OrderNumber)



Select 



  case when GrandChildItem.ID is not NULL then SPACE(4 * 4) 



    else case when ChildItem.ID is not NULL then SPACE(4 * 3)  



    else case when TopItem.ID is not NULL then SPACE(4 * 2) 



    else case when TV.ID is not NULL then SPACE(4 * 1) 



    else case when TH.ID is not NULL then SPACE(4 * 0) 



    end end end end end +



    Coalesce(GrandChildItem.GoodsItemCode, ChildItem.GoodsItemCode, TopItem.GoodsItemCode, TV.VariationName, 'Order '+Cast(TH.OrderNumber as VarChar(12))) AS FormattedName,



  Coalesce(GrandChildItem.GoodsItemCode, ChildItem.GoodsItemCode, TopItem.GoodsItemCode, TV.VariationName, 'Order '+Cast(TH.OrderNumber as VarChar(12))) AS NodeName,



  Coalesce(GrandChildItem.ID, ChildItem.ID, TopItem.ID, TV.ID, TH.ID) AS NodeID,



  Coalesce(GrandChildItem.ClassTypeID, ChildItem.ClassTypeID, TopItem.ClassTypeID, TV.ClassTypeID, TH.ClassTypeID) AS NodeClassTypeID,



  1 AS IsActive, -- just kept for compatibility



  case when coalesce(GrandChildItem.ChildItemCount, ChildItem.ChildItemCount, TopItem.ChildItemCount, 1) > 0 Then 1 else 0 end  as HasChildren,



  Coalesce(GrandChildItem.ID - GrandChildItem.ID + 3, ChildItem.ID - ChildItem.ID + 2, TopItem.ID - TopItem.ID + 1, 0, 0) AS Depth,



  Coalesce(GrandChildItem.ParentID, ChildItem.ParentID, TopItem.VariationID, TV.ParentID) AS ParentID,



  Coalesce(GrandChildItem.ParentClassTypeID, ChildItem.ParentClassTypeID, case when TopItem.VariationID is not NULL then 10400 else NULL end, TV.ParentClassTypeID) AS ParentClassTypeID,



  Coalesce(GrandChildItem.GoodsItemCode, ChildItem.GoodsItemCode, TopItem.GoodsItemCode) AS ProductName,



  ('Order '+cast(TH.OrderNumber as Varchar(12)) + ' ' + cast(TH.Description as varchar(100)) ) as Category1Name,



  TV.VariationName as Category2Name,



  Coalesce(ChildItem.GoodsItemCode, TopItem.GoodsItemCode) AS Category3Name,



  ChildItem.GoodsItemCode AS Category4Name,



  GrandChildItem.GoodsItemCode as Category5Name,



  



  coalesce(CAST(TH.OrderNumber as varchar(12)), '') + '/' +



    coalesce(right('0000000'+convert(varchar(8), TV.SortIndex), 8) + '/', '') +



    coalesce(right('0000000'+ltrim(convert(varchar(8), TopItem.LineItemIndex)), 8)+'/', '') +



    coalesce(right('0000000'+ltrim(convert(varchar(8), ChildItem.LineItemIndex)), 8)+'/', '') +



    coalesce(right('0000000'+ltrim(convert(varchar(8), GrandChildItem.LineItemIndex)), 8)+'/', '') as SortIndex,



  TH.ID as TransHeaderID,



  TV.ID as TransVariationID,



  TopItem.ID as TopItemID,



  ChildItem.ID as ChildItemID,



  GrandChildItem.ID as GrandChildItemID,



  coalesce(case TH.TransactionType when 1 then 'Ord ' when 2 then 'Est ' else 'Other ' end + Cast(TH.OrderNumber as VarChar(10))+'\', '') +



    coalesce(TV.VariationName + '\', '') + 



    coalesce(TopItem.LineItemNumber + '\', '') +



    coalesce(ChildItem.LineItemNumber + '\', '') +



    coalesce(GrandChildItem.LineItemNumber + '\', '') FormattedPath



from TransHeader as TH



join (Select 1 as IsHeader Union Select 0) TempH on 1=1 -- create two rows for each of the above



left join TransVariation as TV on TV.ParentID = TH.ID and TempH.IsHeader = 0 



join (Select 1 as IsVariation Union Select 0) TempV on 1=1



left join TransDetail as TopItem on TopItem.ParentID = TH.ID and TopItem.VariationID = TV.ID and TempV.IsVariation = 0 



join (Select 1 as IsTopItem Union Select 0) TempTI on 1=1



left join TransDetail as ChildItem on ChildItem.ParentID = TopItem.ID and ChildItem.VariationID = TV.ID and TempTI.IsTopItem = 0 



join (Select 1 as IsChildItem Union Select 0) TempCI on 1=1



left join TransDetail as GrandChildItem on GrandChildItem.ParentID = GrandChildItem.ID and GrandChildItem.VariationID = TV.ID 



join (Select 1 as IsGrandChildItem Union Select 0) TempGCI on 1=1



where (TH.ID = @TransHeaderID) and (IsHeader+IsVariation+IsTopItem+IsChildItem+IsGrandChildItem=1)



  and ((IsHeader=1 and TV.ID is NULL)



       or (IsVariation=1 and TopItem.ID is NULL)



       or (IsTopItem=1 and TopItem.ID is not NULL)



       or (IsChildItem=1 and ChildItem.ID is not NULL)



       or (IsGrandChildItem=1 and GrandChildItem.ID is not NULL)



      )



  and (@IncludeVariationRow = 1 or IsVariation = 0)



order by SortIndex



[[code]]



===== Version Information =====
  * Entered : 1/8/2010
  * Version : Control 4.0+



===== Related SQLs =====
  * Backlinks [[include_pagepage_componentbacklinks]]