We help IT Professionals succeed at work.

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

BAM
BAM asked
on
169 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;
Comment
Watch Question

Koen Van WielinkBusiness Intelligence Specialist

Commented:
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?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
BAM

Author

Commented:
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

Author

Commented:
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.
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

Author

Commented:
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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
BAM

Author

Commented:
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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
BAM

Author

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.