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.

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
  • Entered : 1/8/2010
  • Version : Control 4.0+
You could leave a comment if you were logged in.