Avatar of BAM
BAMFlag for United States of America

asked on 

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

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 AS [Master Job],      
                  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],      
                  P.[Master Job],
                  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:

  , CASE
      WHEN t1.[Job Type] <> 'S' THEN M_ECD.ECD
    END                                                                                                                                          AS [ECD]
          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
                             , [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.[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
                    , DATEADD(D, t1.Total_FlowDays, CAST(Sysdatetime() AS DATE))                        AS ECD
                  FROM   (SELECT
                            , 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
                                              , 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)]
                    , 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;
Microsoft SQL Server

Avatar of undefined
Last Comment
Avatar of Koen Van Wielink
Koen Van Wielink
Flag of Netherlands image

The query I need to incorporate this into is:

Can you elaborate on this? It's not clear what you're trying to do and how the 2 queries are related. On what query do you get the error that the order by is out of scope?
Avatar of Richard Obenchain
Richard Obenchain

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of BAM
Flag of United States of America image


A new day and a cup of coffee later, maybe I can explain this a bit better. The query returns the correct data, but it doesn't keep the Part #'s together. This is a manufacturing company where we need to see the master parts and all the components that go into making that part. The CTE produces this:

Master Job      Part #
74742      3C3506-10 REV 1              
74742              3C3504-30 REV 1              
74742                      3C3503-03 REV 1              
74742                              P1-FLAT PATTERN              
74742                      P1-W/0 PROGRAMING            
74742              P1-W/0 PROGRAMING            

In this case the product contains two parts, one of which also needs assembly as does one of its parts. The end result must maintain this order with the indentations. Richard appears to grasp what I'm trying to do, but his solution, though closer than I was able to get, still doesn't get me there. WM.MasterJob is out of scope in this solution. Moving the join up puts it in scope, but puts the order by clause out of scope. I was trying to avoid reworking the original query, but it's looking like I'm not going to be able to get this sorting to work with the query as it is.
Avatar of BAM
Flag of United States of America image


To try and clarify this a bit more, the key column is WM.StockCode as [Part #] on line 15 of the query. This needs to be sorted hierarchically as the above comment shows. The problem appears to be with one or the other or both of the last two LEFT JOINs. I'm a programmer, not a DBA, so I'm not seeing what is probably obvious to a more knowledgeable person. It was suggested to me that CTE was the best way to approach this problem, but I'm open to any other ideas on solving this.
Avatar of Richard Obenchain
Richard Obenchain

Alright, I've finally managed to parse through this query you've got.  Whoever wrote it for you didn't do you any favors: the naming conventions for the subqueries are anything but clear, and there are even columns and calculations selected for subqueries that never get used.

Regardless, in the version I posted above, try changing the Hierarchy join to this:

            ON t1.[Master Job] = H.[MasterJob]

I'm not sure that this is the correct field  (there's also a  [Job# (MSO)] column in t1 that other subqueries link to as "MasterJob" - again, the naming conventions are far from clear - so that might be more appropriate).  However, that version of t1 should be in scope (WM is a subquery of it).

As far as the rest of the ordering goes, we're to the point where table structures and sample data would probably be needed for me to clean this up and make any further refinements (I could try doing it without the structures, but there's the realistic possibility of error).  Given the number of tables involved here, that's a pretty onerous task.
Avatar of BAM
Flag of United States of America image



Thank you so much for your efforts so far. I did not expect anyone to be interested in trying to tackle this. I knew the query was problematic, but I'm still familiarizing myself with the database and didn't dare mess too much with the query until I had a better understanding of the data. Without having access to the tables, I knew it would be an onerous task for anyone to just sort out this poorly constructed query, never mind getting the hierarchy to work. Your latest change got me a step closer, but the REPLICATE line is still unhappy. Moving that around got me results with indentations, but it doesn't look accurate so I'll have to dig into that and see what is amiss.

It's not going to happen today, but I'll put together some sample data and maybe cleaning this mess up will make things more clear. I didn't think there was going to be a way to make this work, but after looking at your latest change, it now looks doable.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of BAM
Flag of United States of America image


I caught the field name and fixed that. The problem is still the scope. H does not want to be in that location.

Thanks much for cleaning up that mess. I'll email you the other 200+ I inherited so you can do those too. :-D This one at least returns accurate data. Many of them do not.

Thanks again for your help.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of BAM
Flag of United States of America image


I'm still tweaking this thing because the results are not quite what I'm after, but I'm marking this as answered because the issues I'm still having are not about my original question. The solution offered here to get the hierarchy incorporated works perfectly. Thanks so much.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo