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.MasterJo
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
, 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.WipMast
INNER JOIN (SELECT
[Customer]
, [Name] AS CustomerName
FROM [SysproCompanyF].[dbo].[Ar
ON C.Customer = WM.Customer
LEFT JOIN SysproCompanyF.dbo.Program
ON PM.Job = WM.Job
LEFT JOIN SysproCompanyF.dbo.SorMast
ON SM.SalesOrder = WM.SalesOrder
LEFT JOIN SysproCompanyF.dbo.SorDeta
ON SD.SalesOrder = WM.SalesOrder
LEFT JOIN SysproCompanyF.dbo.WipMast
ON MJ.Job = WM.MasterJob
LEFT JOIN SysproCompanyF.dbo.CurrOpe
ON CO.Job = WM.Job
LEFT JOIN SysproCompanyF.dbo.WipJobA
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].[Pr
INNER JOIN SysproCompanyF.dbo.WipJobA
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].[Ma
LEFT JOIN (SELECT
SJ.MasterJob
, SUM(SJ.SJ_FlowDays) AS SJ_FlowDays
FROM SysproCompanyF.dbo.MasterJ
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].[Ma
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;
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.MasterJo
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
, 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.WipMast
INNER JOIN (SELECT
[Customer]
, [Name] AS CustomerName
FROM [SysproCompanyF].[dbo].[Ar
ON C.Customer = WM.Customer
LEFT JOIN SysproCompanyF.dbo.Program
ON PM.Job = WM.Job
LEFT JOIN SysproCompanyF.dbo.SorMast
ON SM.SalesOrder = WM.SalesOrder
LEFT JOIN SysproCompanyF.dbo.SorDeta
ON SD.SalesOrder = WM.SalesOrder
LEFT JOIN SysproCompanyF.dbo.WipMast
ON MJ.Job = WM.MasterJob
LEFT JOIN SysproCompanyF.dbo.CurrOpe
ON CO.Job = WM.Job
LEFT JOIN SysproCompanyF.dbo.WipJobA
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].[Pr
INNER JOIN SysproCompanyF.dbo.WipJobA
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].[Ma
LEFT JOIN (SELECT
SJ.MasterJob
, SUM(SJ.SJ_FlowDays) AS SJ_FlowDays
FROM SysproCompanyF.dbo.MasterJ
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].[Ma
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;
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
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.
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.
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.
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.
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.
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.
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
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.
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.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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?