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