troubleshooting Question

Trying to join a CTE with a query but my Order By is out of scope

Avatar of BAM
BAMFlag for United States of America asked on
Microsoft SQL Server
10 Comments3 Solutions174 ViewsLast Modified:
I apologize in advance, but I don't know any way to ask this but to paste the entire mess here. I inherited this query from my predecessor and it returns exactly what I need except for the essential hierarchical sorting. I got a great answer here a few weeks ago (thanks Ste5an) on how to use CTE to build a hierarchy. Now I'm trying to take that result and merge it with a query while maintaining the hierarchy and I cannot make it work. The Part # needs to have the hierarchical sorting provided by the CTE, but no matter how I do it, the Order By is out of scope.

The CTE is this:

;WITH Hierarchy AS
      (
            SELECT      A.MasterJob,
                  A.Job,      
                  A.Job AS [Master Job],      
                  A.StockCode,
                  0 AS [Level],
                  '\\' + CAST(A.Job AS VARCHAR(MAX)) AS [Path]
            FROM      WipMaster A
            WHERE      A.MasterJob IS NULL OR Len(RTRIM(LTRIM(A.MasterJob))) = 0
            UNION ALL
            SELECT      C.Job AS [Root],      
                  C.Job,
                  P.[Master Job],
                  C.StockCode,
                  P.[Level] + 1,
                  P.[Path] + '\' + C.Job
            FROM      Hierarchy P
                  INNER JOIN WipMaster C ON P.Job = C.MasterJob
      )
      SELECT      H.[Master Job],                   
            REPLICATE(' ', H.[Level] * 8) + H.StockCode AS [Part #]
      FROM      Hierarchy H
      ORDER BY H.[Path];

The query I need to incorporate this into is:

SELECT
  t1.*
  , CASE
      WHEN t1.[Job Type] <> 'S' THEN M_ECD.ECD
      ELSE S_ECD.ECD
    END                                                                                                                                          AS [ECD]
FROM   (SELECT
          WM.Job                                                                                                                        AS [Job# (MSO)]
          , C.Customer                                                                                                                  AS [Customer Code]
          , LTRIM(RTRIM(C.CustomerName))                                                        AS [Customer Name]
              , CASE
                  WHEN PM.PM IS NULL THEN ''
                  ELSE dbo.zfGetInitials(PM.PM)
                  END                                                                                                                              AS [PM]
          , WM.StockCode                                                                                                            AS [Part #]
          , CAST(CASE
                   WHEN SD.MLineShipDate IS NOT NULL THEN SD.MLineShipDate
                   ELSE WM.JobDeliveryDate
                 END AS DATE)                                                                   AS [PO Due Date]
          , CAST(SD.MOrderQty AS DECIMAL(10, 0))                                                                        AS [Line Order Qty]
          , CAST(SD.MShipQty AS DECIMAL(10, 0))                                                                              AS [Shipped Quantity]
          , CAST(SD.MBackOrderQty AS DECIMAL(10, 0))                                                                  AS [Back Order Qty]
          , CAST(SD.MPrice AS DECIMAL(10, 4))                                                                              AS [Unit Price]
          , CAST(CASE
                        WHEN SD.MPrice IS NULL THEN NULL
                        ELSE SD.MPrice * MOrderQty
                        END AS DECIMAL(18, 4))                                                                                          AS [Total Price]
              , CAST(WM.QtyToMake AS INT)                                                           AS [Job Qty]
          , WM.JobType                                                                                                                  AS [Job Type]
          , CASE
              WHEN WM.HoldFlag = 'Y' THEN 'YES'
              WHEN WM.HoldFlag = 'N' THEN 'NO'
              ELSE 'N/A'
            END                                                                                 AS [On Hold]
          , WM.Complete                                                                         AS [Job Complete]
          , SM.CustomerPoNumber                                                                                                      AS [Customer PO]
          , CAST(SM.EntrySystemDate AS DATE)                                                                              AS [System Entry Date]          
              , WM.SalesOrder                                                                                                            AS [Sales Order]
          , CASE
              WHEN SM.OrderStatus = '\' THEN 'Cancelled'
              WHEN SM.OrderStatus = '9' THEN 'Complete'
              WHEN SM.OrderStatus = '1' THEN 'Open Order'
              WHEN SM.OrderStatus = 'S' THEN 'Suspense'
              WHEN SM.OrderStatus = '*' THEN 'Cancelled at Entry'
              WHEN SM.OrderStatus = '4' THEN 'In Warehouse'
              WHEN SM.OrderStatus = '8' THEN 'Ready to Invoice'
              WHEN SM.OrderStatus = '2' THEN 'Open BackOrder'
              ELSE 'LOOKUP'
            END                                                                                                                              AS [Order Status]
              , MJ.Job                                                                              AS [Master Job]
          , MJ.StockCode                                                                        AS [Master P/N]
          , CASE
              WHEN SM.Branch = '10' THEN 'MFG'
              WHEN SM.Branch = '20' THEN 'MFD'
              ELSE NULL
            END                                                                                 AS [Branch]
          , (WM.MatCostToDate1 + WM.LabCostToDate1) - (WM.MatValueIssues1 + WM.LabValueIssues1) AS [Current WiP]
          , POps.PreviousOpperation                                                             AS [Last Operation]
          , POps.[Last Scan Date]                                                               AS [Last Operation Clock Date]
              , WJ.WorkCentre                                                                                                            AS [Work Center #]
              , WJ.WorkCentreDesc                                                                                                    AS [WC Description]
        FROM   SysproCompanyF.dbo.WipMaster WM
               INNER JOIN (SELECT
                             [Customer]
                             , [Name]                                                                                          AS CustomerName
                           FROM [SysproCompanyF].[dbo].[ArCustomer]) C
                       ON C.Customer = WM.Customer
               LEFT JOIN SysproCompanyF.dbo.Program_Managers PM
                      ON PM.Job = WM.Job
               LEFT JOIN SysproCompanyF.dbo.SorMaster SM
                      ON SM.SalesOrder = WM.SalesOrder
               LEFT JOIN SysproCompanyF.dbo.SorDetail SD
                      ON SD.SalesOrder = WM.SalesOrder
               LEFT JOIN SysproCompanyF.dbo.WipMaster MJ
                      ON MJ.Job = WM.MasterJob
               LEFT JOIN SysproCompanyF.dbo.CurrOperations CO
                      ON CO.Job = WM.Job
               LEFT JOIN SysproCompanyF.dbo.WipJobAllLab WJ
                      ON WJ.Job = CO.Job
                         AND WJ.Operation = CO.CurrOperation
               LEFT JOIN (SELECT
                            t1.[CurrntOp]
                            , t1.[Job]
                            , t1.[PreviousOpperation]
                            , CAST(t2.ActualFinishDate AS DATE)                                                      AS [Last Scan Date]
                            , t2.WorkCentre
                            , t2.WorkCentreDesc
                          FROM   [SysproCompanyF].[dbo].[PreviousOps] t1
                                 INNER JOIN SysproCompanyF.dbo.WipJobAllLab t2
                                         ON t2.Job = t1.Job
                                            AND t2.Operation = t1.PreviousOpperation) POps
                      ON POps.Job = CO.Job
                         AND POps.CurrntOp = CO.CurrOperation) t1
       LEFT JOIN (SELECT
                    t1.MasterJob
                    , DATEADD(D, t1.Total_FlowDays, CAST(Sysdatetime() AS DATE))                        AS ECD
                  FROM   (SELECT
                            MJ.MasterJob
                            , MJ.JobType
                            , MJ.MJ_FlowDays
                            , Isnull(SJ.SJ_FlowDays, 0)                                                                  AS SJ_FlowDays
                            , MJ.MJ_FlowDays + Isnull(SJ.SJ_FlowDays, 0)                                    AS Total_FlowDays
                          FROM   [SysproCompanyF].[dbo].[MasterJob_Standalone_FlowDays] MJ
                                 LEFT JOIN (SELECT
                                              SJ.MasterJob
                                              , SUM(SJ.SJ_FlowDays) AS SJ_FlowDays
                                            FROM   SysproCompanyF.dbo.MasterJob_SubJob_FlowDays SJ
                                            GROUP  BY SJ.MasterJob) SJ
                                        ON SJ.MasterJob = MJ.MasterJob) t1) M_ECD
              ON M_ECD.MasterJob = t1.[Job# (MSO)]
       LEFT JOIN (SELECT
                    SJ.[SubJob]
                    , DATEADD(D, SJ.[SJ_FlowDays], CAST(Sysdatetime() AS DATE))                              AS ECD
                  FROM   [SysproCompanyF].[dbo].[MasterJob_SubJob_FlowDays] SJ) S_ECD
              ON S_ECD.SubJob = t1.[Job# (MSO)]
         WHERE [PM] = IsNull(@PM, [PM])
         AND LOWER([Customer Name]) Like '%' + IsNull(@CustomerName, '%') + '%'
         AND LOWER([Part #]) LIKE '%' + IsNull(@PartNumber, '%') + '%'
         AND [Job Complete] = IsNull(@Status, [Job Complete])
         AND ([Branch] LIKE @Branch OR [Branch] IS NULL)
         AND [PO Due Date] >= @StartDate AND [PO Due Date] <= @EndDate;
ASKER CERTIFIED SOLUTION
Richard Obenchain

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros