Solved

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

Posted on 2014-11-19
10
111 Views
Last Modified: 2014-11-26
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;
0
Comment
Question by:BZ
  • 5
  • 4
10 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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?
0
 
LVL 3

Assisted Solution

by:Richard Obenchain
Richard Obenchain earned 500 total points
Comment Utility
I'm thinking you should use your CTE with the larger query, incorporating Hierarchy into it where needed and ordering on the larger query.

Something like (changes in bold/underline):

;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
   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]
          , REPLICATE(' ', H.[Level] * 8) + H.StockCode AS [Part #]
         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)]
          left join Hierarchy H
            ON WM.MasterJob = H.[MasterJob]

          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;
        ORDER BY H.[Path];

... though I assume you'll want more in your ORDER BY than just the path.
0
 

Author Comment

by:BZ
Comment Utility
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.
0
 

Author Comment

by:BZ
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:Richard Obenchain
Comment Utility
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:BZ
Comment Utility
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.
0
 
LVL 3

Assisted Solution

by:Richard Obenchain
Richard Obenchain earned 500 total points
Comment Utility
" the REPLICATE line is still unhappy"

Okay, I missed that - there's already a [Part#] field in the query, so it would have to be labeled something else.  Maybe:

            , REPLICATE(' ', H.[Level] * 8) + H.StockCode AS [Indented Part #]

But yeah, if you want to do anything more intensive, just post back here.  Also, I've attached a version of the query that (I hope) is "cleaned up": I removed columns that are in subqueries but never used, changed subquery labels to eliminate duplicated names, and redid the indenting so that it's easier to read/parse (both so that you have it and so that I have it for reference later).
cleaned-up.txt
0
 

Author Comment

by:BZ
Comment Utility
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.
0
 
LVL 3

Accepted Solution

by:
Richard Obenchain earned 500 total points
Comment Utility
Bah.  Too many subqueries.

Move that line to the first level.  So, the start should look something like:

SELECT
      t1.*
      , CASE
            WHEN t1.[Job Type] <> 'S' THEN M_ECD.ECD
            ELSE S_ECD.ECD
            END AS [ECD]
      , REPLICATE(' ', H.[Level] * 8) + H.StockCode AS [Indented Part #]

That puts H.StockCode and H.Level in scope at the outermost query.
0
 

Author Closing Comment

by:BZ
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now