Avatar of BAM
BAM
Flag 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,      
                  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;
Microsoft SQL Server

Avatar of undefined
Last Comment
BAM

8/22/2022 - Mon
Koen Van Wielink

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?
SOLUTION
Richard Obenchain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
BAM

ASKER
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.
BAM

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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.
BAM

ASKER
Richard,

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.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
BAM

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
BAM

ASKER
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.