{$page}

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.

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

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

• Entered : 1/8/2010
• Version : Control 4.0+

• Backlinks include_pagepage_componentbacklinks