July 13, 2021 at 1:17 pm
I would say the biggest improvement was switching to OUTER APPLY. The CTE wasn't necessary - but it wasn't really causing any issues.
As for your question about views, this becomes a problem if you end up referencing a view that has the same tables as another view or this query. You can end up with multiple scans against the same table or other problems. That doesn't mean you cannot reference views in your queries - just that you should not be using a view of a view of a view.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2021 at 1:30 pm
WITH SubQuery AS
(
SELECTjh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode,
CAST(CASE WHEN NOT jh.Date03 IS NULL THEN jh.Date03 WHEN jh.Number01 <> 0 THEN DATEADD(d, - jh.Number01, jo.StartDate) ELSE jo.StartDate END AS DateTime) AS jhPaintDate,
CASE WHEN NOT jh.Date03 IS NULL THEN 1 ELSE 0 END AS Planned
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum INNER JOIN
[ERP10-Live].Erp.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jo.OpCode IN ('71', '81', '82')) AND (jh.JobReleased = 1) AND (jo.OpComplete = 0)
),
SubQuery2 AS
(
SELECT jh.Company, jm.JobNum, jm.AssemblySeq,
SUM(CASE WHEN (p.TypeCode = 'M' AND (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty > 0)) THEN 1 ELSE 0 END) AS MShortages,
SUM(CASE WHEN (p.TypeCode = 'P' AND (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty > 0)) THEN 1 ELSE 0 END) AS PShortages, jh.DueDate
FROM [ERP10-Live].Erp.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON ja.Company = jh.Company AND ja.JobNum = jh.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm ON jm.Company = ja.Company AND jm.JobNum = ja.JobNum AND jm.AssemblySeq = ja.AssemblySeq INNER JOIN
[ERP10-Live].Erp.Part AS p ON p.Company = jm.Company AND p.PartNum = jm.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq ON pq.Company = p.Company AND pq.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jm.IssuedComplete = 0) AND (p.SearchWord <> 'JIG/FIXT')
GROUP BY jm.JobNum, jh.Company, jm.AssemblySeq, jh.DueDate
),
SubQuery3 AS
(
SELECT jh.Company, jm.JobNum, jm.AssemblySeq,
COUNT(jm.PartNum) AS ComponentParts
FROM [ERP10-Live].Erp.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON ja.Company = jh.Company AND ja.JobNum = jh.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm ON jm.Company = ja.Company AND jm.JobNum = ja.JobNum AND jm.AssemblySeq = ja.AssemblySeq INNER JOIN
[ERP10-Live].Erp.Part AS p ON p.Company = jm.Company AND p.PartNum = jm.PartNum
WHERE (jh.JobComplete = 0) AND (p.SearchWord <> 'JIG/FIXT') AND (p.TypeCode = 'M')
GROUP BY jm.JobNum, jh.Company, jm.AssemblySeq)
SELECT jh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode, ja.FinalOpr, jo.EstSetHours, jo.EstProdHours, jo.ProdStandard, jo.StdFormat,
CAST(jo.EstSetHours + jo.EstProdHours AS decimal(12, 2)) AS OpTime, jh.JobComplete, jh.JobClosed, jh.JobReleased, jh.JobFirm, CAST(ja.StartDate AS datetime) AS jaStartDate, CAST(ja.DueDate AS datetime) AS jaDueDate,
CAST(jo.LastLaborDate AS datetime) AS LastLaborDate, CAST(jo.RunQty AS int) AS joRunQty, CAST(jo.QtyCompleted AS int) AS joQtyCompleted, jo.OpComplete, CAST(jh.ProdQty AS int) AS ProdQty,
CAST(jh.QtyCompleted AS int) AS jhQtyCompleted, ja.PartNum, p.ProdCode, p.SearchWord, p.OnHoldReason_c, p.CheckBox02 AS Engineering, p.CheckBox04 AS Programming, p.OnHold, jo.SetupComplete,
CAST(jo.StartDate AS datetime) AS joStartDate, jo.StartHour AS joStartHour, CAST(jo.DueDate AS DATETIME) AS joDueDate, CAST(jh.CreateDate AS datetime) AS CreateDate, ja.TLALaborCost, ja.TLAMaterialCost,
ja.TLASubcontractCost, ja.LLALaborCost, ja.LLAMaterialCost, ja.LLASubcontractCost, CAST(jh.ReqDueDate AS datetime) AS jhReqDueDate, jh.Date01 AS jhScheduleDate,
CAST(jh.Date05 AS DateTime) AS jhWeldDate, CAST(jh.Date06 AS DateTime) AS jhInsertDate, CAST(jh.Date04 AS DateTime) AS jhAssemblyDate, jh.ShortChar03 AS Operator_Weld,
jh.ShortChar06 AS Operator_Insert, jh.ShortChar05 AS Operator_Assy, jh.ShortChar04 AS WeldComments, jo.DaysOut, CAST(jh.DueDate AS datetime) AS jhDueDate, jo.ActSetupHours, jo.ActProdHours,
CAST(ja.RequiredQty - ja.PullQty AS int) AS ProductionQty, jo.VendorNum, jh.Character01 AS jhComments, CAST(jh.Number01 AS int) AS jhExpedite, jh.ShortChar01 AS jhQJob,
jh.ShortChar02 AS jhLifecycle, CAST(ja.RequiredQty AS int) AS RequiredQty, CAST(ja.PullQty AS int) AS PullQty, p.ShortChar02 AS pMatType, p.ShortChar03 AS pMatThick,
jo.CommentText, jo.ShortChar01 AS ToolTape, jo.Character02 AS QualityAlert, jo.Character03 AS MesFeedback, CASE WHEN NOT jh.Date01 IS NULL
THEN jh.Date01 - 365 WHEN NOT jh.Date05 IS NULL THEN jh.Date05 WHEN jh.Number01 <> 0 THEN DATEADD(d, jh.Number01, jo.StartDate) ELSE jo.StartDate END AS OpDate, jm.PartNum AS Powder,
sq.jhPaintDate,
sq3.ComponentParts,
IsNull(sq2.MShortages,0) AS MShortages, IsNull(sq2.PShortages,0) AS PShortages
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum LEFT OUTER JOIN
SubQuery AS sq ON jh.Company = sq.Company AND jh.JobNum = sq.JobNum LEFT OUTER JOIN
SubQuery2 AS sq2 ON jh.Company = sq2.Company AND jh.JobNum = sq2.JobNum LEFT OUTER JOIN
SubQuery3 AS sq3 ON jh.Company = sq3.Company AND jh.JobNum = sq3.JobNum INNER JOIN
[ERP10-Live].dbo.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq OUTER APPLY
(
SELECT *
FROM [ERP10-Live].Erp.JobMtl jm
WHERE ja.Company = Company
AND ja.JobNum = JobNum
AND ja.AssemblySeq = AssemblySeq
AND (jm.PartNum LIKE 'POW%')
) AS jm
LEFT OUTER JOIN
[ERP10-Live].dbo.Part AS p ON ja.Company = p.Company AND ja.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jh.ProdQty - jh.QtyCompleted > 0)
July 13, 2021 at 1:32 pm
The above SQL executes in around 00:00:02 and returns 28,801 records.
My overall cte report now executes quickly but I'm unsure of how to bring into this SQL Code additional aggregate data - eg is it good practice to continue to add SubQueries or is there an alternative technique to adopt?
July 13, 2021 at 1:34 pm
I am still using the cte - but the execution has reduced to 00:00:00 with 1635 records.
But the cte adopts the OUTER APPLY that you advised earlier.
July 13, 2021 at 1:37 pm
WITH SubQuery AS
(
SELECTjh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode,
CAST(CASE WHEN NOT jh.Date03 IS NULL THEN jh.Date03 WHEN jh.Number01 <> 0 THEN DATEADD(d, - jh.Number01, jo.StartDate) ELSE jo.StartDate END AS DateTime) AS jhPaintDate,
CASE WHEN NOT jh.Date03 IS NULL THEN 1 ELSE 0 END AS Planned
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum INNER JOIN
[ERP10-Live].Erp.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jo.OpCode IN ('71', '81', '82')) AND (jh.JobReleased = 1) AND (jo.OpComplete = 0)
),
SubQuery2 AS
(
SELECT jh.Company, jm.JobNum, jm.AssemblySeq,
SUM(CASE WHEN (p.TypeCode = 'M' AND (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty > 0)) THEN 1 ELSE 0 END) AS MShortages,
SUM(CASE WHEN (p.TypeCode = 'P' AND (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty > 0)) THEN 1 ELSE 0 END) AS PShortages, jh.DueDate
FROM [ERP10-Live].Erp.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON ja.Company = jh.Company AND ja.JobNum = jh.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm ON jm.Company = ja.Company AND jm.JobNum = ja.JobNum AND jm.AssemblySeq = ja.AssemblySeq INNER JOIN
[ERP10-Live].Erp.Part AS p ON p.Company = jm.Company AND p.PartNum = jm.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq ON pq.Company = p.Company AND pq.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jm.IssuedComplete = 0) AND (p.SearchWord <> 'JIG/FIXT')
GROUP BY jm.JobNum, jh.Company, jm.AssemblySeq, jh.DueDate
),
SubQuery3 AS
(
SELECT jh.Company, jm.JobNum, jm.AssemblySeq,
COUNT(jm.PartNum) AS ComponentParts
FROM [ERP10-Live].Erp.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON ja.Company = jh.Company AND ja.JobNum = jh.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm ON jm.Company = ja.Company AND jm.JobNum = ja.JobNum AND jm.AssemblySeq = ja.AssemblySeq INNER JOIN
[ERP10-Live].Erp.Part AS p ON p.Company = jm.Company AND p.PartNum = jm.PartNum
WHERE (jh.JobComplete = 0) AND (p.SearchWord <> 'JIG/FIXT') AND (p.TypeCode = 'M')
GROUP BY jm.JobNum, jh.Company, jm.AssemblySeq)
SELECT jh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode, ja.FinalOpr, jo.EstSetHours, jo.EstProdHours, jo.ProdStandard, jo.StdFormat,
CAST(jo.EstSetHours + jo.EstProdHours AS decimal(12, 2)) AS OpTime, jh.JobComplete, jh.JobClosed, jh.JobReleased, jh.JobFirm, CAST(ja.StartDate AS datetime) AS jaStartDate, CAST(ja.DueDate AS datetime) AS jaDueDate,
CAST(jo.LastLaborDate AS datetime) AS LastLaborDate, CAST(jo.RunQty AS int) AS joRunQty, CAST(jo.QtyCompleted AS int) AS joQtyCompleted, jo.OpComplete, CAST(jh.ProdQty AS int) AS ProdQty,
CAST(jh.QtyCompleted AS int) AS jhQtyCompleted, ja.PartNum, p.ProdCode, p.SearchWord, p.OnHoldReason_c, p.CheckBox02 AS Engineering, p.CheckBox04 AS Programming, p.OnHold, jo.SetupComplete,
CAST(jo.StartDate AS datetime) AS joStartDate, jo.StartHour AS joStartHour, CAST(jo.DueDate AS DATETIME) AS joDueDate, CAST(jh.CreateDate AS datetime) AS CreateDate, ja.TLALaborCost, ja.TLAMaterialCost,
ja.TLASubcontractCost, ja.LLALaborCost, ja.LLAMaterialCost, ja.LLASubcontractCost, CAST(jh.ReqDueDate AS datetime) AS jhReqDueDate, jh.Date01 AS jhScheduleDate,
CAST(jh.Date05 AS DateTime) AS jhWeldDate, CAST(jh.Date06 AS DateTime) AS jhInsertDate, CAST(jh.Date04 AS DateTime) AS jhAssemblyDate, jh.ShortChar03 AS Operator_Weld,
jh.ShortChar06 AS Operator_Insert, jh.ShortChar05 AS Operator_Assy, jh.ShortChar04 AS WeldComments, jo.DaysOut, CAST(jh.DueDate AS datetime) AS jhDueDate, jo.ActSetupHours, jo.ActProdHours,
CAST(ja.RequiredQty - ja.PullQty AS int) AS ProductionQty, jo.VendorNum, jh.Character01 AS jhComments, CAST(jh.Number01 AS int) AS jhExpedite, jh.ShortChar01 AS jhQJob,
jh.ShortChar02 AS jhLifecycle, CAST(ja.RequiredQty AS int) AS RequiredQty, CAST(ja.PullQty AS int) AS PullQty, p.ShortChar02 AS pMatType, p.ShortChar03 AS pMatThick,
jo.CommentText, jo.ShortChar01 AS ToolTape, jo.Character02 AS QualityAlert, jo.Character03 AS MesFeedback, CASE WHEN NOT jh.Date01 IS NULL
THEN jh.Date01 - 365 WHEN NOT jh.Date05 IS NULL THEN jh.Date05 WHEN jh.Number01 <> 0 THEN DATEADD(d, jh.Number01, jo.StartDate) ELSE jo.StartDate END AS OpDate, jm.PartNum AS Powder,
sq.jhPaintDate,
sq3.ComponentParts,
IsNull(sq2.MShortages,0) AS MShortages, IsNull(sq2.PShortages,0) AS PShortages
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum LEFT OUTER JOIN
SubQuery AS sq ON jh.Company = sq.Company AND jh.JobNum = sq.JobNum LEFT OUTER JOIN
SubQuery2 AS sq2 ON jh.Company = sq2.Company AND jh.JobNum = sq2.JobNum LEFT OUTER JOIN
SubQuery3 AS sq3 ON jh.Company = sq3.Company AND jh.JobNum = sq3.JobNum INNER JOIN
[ERP10-Live].dbo.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq OUTER APPLY
(
SELECT *
FROM [ERP10-Live].Erp.JobMtl jm
WHERE ja.Company = Company
AND ja.JobNum = JobNum
AND ja.AssemblySeq = AssemblySeq
AND (jm.PartNum LIKE 'POW%')
) AS jm
LEFT OUTER JOIN
[ERP10-Live].dbo.Part AS p ON ja.Company = p.Company AND ja.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jh.ProdQty - jh.QtyCompleted > 0)
If you really want to help yourself in the future, give meaningful names to your CTEs.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2021 at 1:39 pm
Apologies - I'm not sure what you mean - there is no cte in the above SQL statement.
July 13, 2021 at 1:41 pm
Apologies - I'm not sure what you mean - there is no cte in the above SQL statement.
Actually, there are 3. The first one starts right after the word WITH.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2021 at 1:47 pm
Gotcha, my understanding of a cte was a recursive cte using UNOIN ALL.
July 13, 2021 at 2:00 pm
That would be an rCTE (Recursive CTE). If the recursion in based on incremental values and RBAR proceessing, a well written WHILE Loop will be faster and less resource intensive.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2021 at 2:03 pm
This is my 1st view where I build up the data:
SELECT jh.Company, jh.JobNum AS ParentJob, jh.JobFirm, jh.JobReleased, ja.AssemblySeq, jh.PartNum AS ParentPart, jm.MtlSeq, jm.PartNum AS ComponentPart, pp.SourceType, CAST(jm.QtyPer AS int) AS QtyPer,
CAST(jm.RequiredQty AS int) AS RequiredQty, CAST(jm.IssuedQty AS int) AS IssuedQty, jm.FixedQty, CAST(pq.OnHandQty AS int) AS OnHandQty,
CAST((CASE WHEN jm.RequiredQty - jm.IssuedQty <= pq.OnHandQty THEN 0 ELSE (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty) END) AS int) AS Shortage, jm.RelatedOperation, jo.OpCode,
jhc.JobNum AS ComponentJob, jhc.DueDate, jm.ReqDate, CAST(jhc.ProdQty AS int) AS ProdQty, CAST(jhc.QtyCompleted AS int) AS QtyCompleted
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm ON ja.Company = jm.Company AND ja.JobNum = jm.JobNum AND ja.AssemblySeq = jm.AssemblySeq LEFT OUTER JOIN
[ERP10-Live].Erp.JobOper AS jo ON jm.Company = jo.Company AND jm.JobNum = jo.JobNum AND jm.AssemblySeq = jo.AssemblySeq AND jm.RelatedOperation = jo.OprSeq LEFT OUTER JOIN
[ERP10-Live].Erp.PartPlant AS pp ON jm.Company = pp.Company AND jm.PartNum = pp.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq ON jm.Company = pq.Company AND jm.PartNum = pq.PartNum OUTER APPLY
(SELECT TOP 1 *
FROM [ERP10-Live].Erp.JobHead jh2
WHERE jh2.Company = jm.Company AND jh2.PartNum = jm.PartNum AND jh2.JobClosed = 0 AND jh2.JobComplete = 0 AND jh2.ProdQty - jh2.QtyCompleted > 0
ORDER BY jh2.DueDate) AS jhc
WHERE (jh.JobComplete = 0) AND (jh.DueDate <= GETDATE() + 35) AND (jh.ProdQty - jh.QtyCompleted > 0) AND (jm.IssuedComplete = 0) AND (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty > 0) AND (pp.SourceType = 'M')
July 13, 2021 at 2:03 pm
The cte as follows:
WITH SubQuery1 AS
(
SELECT 0 AS ProductLevel, Company, ParentJob AS FinishedJob, ParentJob, AssemblySeq, ProdQty, QtyCompleted, ParentPart, MtlSeq, ReqDate, ComponentPart,
SourceType, QtyPer, RequiredQty, IssuedQty, OnHandQty, ComponentJob, DueDate, Shortage, RelatedOperation, OpCode
FROM dbo.RM_JobMtl_1 AS anchor
WHERE (ComponentJob <> ParentJob)
UNION ALL
SELECT ar.ProductLevel + 1 AS ProductLevel, ar.Company, ar.FinishedJob, rc.ParentJob, rc.AssemblySeq, rc.ProdQty, rc.QtyCompleted, rc.ParentPart, rc.MtlSeq,
rc.ReqDate, rc.ComponentPart, rc.SourceType, rc.QtyPer, rc.RequiredQty, rc.IssuedQty, rc.OnHandQty, rc.ComponentJob, rc.DueDate, rc.Shortage, rc.RelatedOperation,
rc.OpCode
FROM dbo.RM_JobMtl_1 AS rc INNER JOIN
SubQuery1 AS ar ON ar.Company = rc.Company AND ar.ComponentJob = rc.ParentJob
)
SELECT ProductLevel, Company, FinishedJob, ParentJob, AssemblySeq, ParentPart, MtlSeq, ReqDate, ComponentPart, SourceType, ComponentJob, CAST(ProdQty AS int) AS ProdQty,
CAST(QtyCompleted AS int) AS QtyCompleted, DueDate, QtyPer, RequiredQty, IssuedQty, OnHandQty, Shortage, RelatedOperation, OpCode
FROM SubQuery1 AS SubQuery2
July 13, 2021 at 2:08 pm
Execution on the cte is 00:00:00 returning 1662 rows.
My biggest issue now is building up a data set with multiple aggregate values and accomplishing this without reducing performance.
I am currently at 00:00:02 (the sql code post you replied "If you really want to help yourself in the future, give meaningful names to your CTEs.") but still require more data to be added but I'm not sure of the most efficient method.
July 13, 2021 at 3:38 pm
How will you be querying this view - once it is completed? I am thinking this might be better as a stored procedure - with parameters to filter to the criteria you will be using when querying the view.
What is the tool you are using for the reports? Is it SSRS - or some other reporting tool?
Looking at your subqueries (CTEs) - you are referencing the job header table, which may not be necessary. In fact, in each one you can avoid referencing both JobHead and JobAsmbl because the outer query has those tables. An outer apply for subquery3 could be:
OUTER APPLY (
SELECT COUNT(jm.PartNum) AS ComponentParts
FROM [ERP10-Live].Erp.JobMtl AS jm3
ON jm.Company = ja.Company AND jm.JobNum = ja.JobNum AND jm.AssemblySeq = ja.AssemblySeq
INNER JOIN [ERP10-Live].Erp.Part AS p3 ON p3.Company = jm3.Company AND p3.PartNum = jm3.PartNum
WHERE jm3.Company = ja.Company
AND jm3.JobNum = ja.JobNum
AND jm3.AssemblySeq = ja.AssemblySeq
AND p3.SearchWord <> 'JIG/FIXT'
AND p3.TypeCode = 'M'
) As sq3
You can add the outer apply and test to see if this returns the same results as the SubQuery3.
It may be possible to get rid of the CTEs and the OUTER APPLY by using windowed functions (e.g. SUM(column) OVER(PARTITION BY ... ORDER BY ...). Not sure, but it might be possible.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2021 at 8:32 pm
Not sure where to place this code segment - can you advise.
I receive and error: Incorrect syntax near the keyword 'ON' having slotted it after SubQuery and part of SubQuery2.
WITH SubQuery AS
(
SELECTjh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode,
CAST(CASE WHEN NOT jh.Date03 IS NULL THEN jh.Date03 WHEN jh.Number01 <> 0 THEN DATEADD(d, - jh.Number01, jo.StartDate) ELSE jo.StartDate END AS DateTime) AS jhPaintDate,
CASE WHEN NOT jh.Date03 IS NULL THEN 1 ELSE 0 END AS Planned
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum INNER JOIN
[ERP10-Live].Erp.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jo.OpCode IN ('71', '81', '82')) AND (jh.JobReleased = 1) AND (jo.OpComplete = 0)
),
SubQuery2 AS
(
SELECT jh.Company, jm.JobNum, jm.AssemblySeq,
SUM(CASE WHEN (p.TypeCode = 'M' AND (jm.RequiredQty - jm.IssuedQty - IsNull(pq.OnHandQty,0) > 0)) THEN 1 ELSE 0 END) AS MShortages,
SUM(CASE WHEN (p.TypeCode = 'P' AND (jm.RequiredQty - jm.IssuedQty - IsNull(pq.OnHandQty,0) > 0)) THEN 1 ELSE 0 END) AS PShortages, jh.DueDate
FROM [ERP10-Live].Erp.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON ja.Company = jh.Company AND ja.JobNum = jh.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm ON jm.Company = ja.Company AND jm.JobNum = ja.JobNum AND jm.AssemblySeq = ja.AssemblySeq INNER JOIN
[ERP10-Live].Erp.Part AS p ON p.Company = jm.Company AND p.PartNum = jm.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq ON pq.Company = p.Company AND pq.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jm.IssuedComplete = 0) AND (p.SearchWord <> 'JIG/FIXT')
GROUP BY jm.JobNum, jh.Company, jm.AssemblySeq, jh.DueDate
)
SELECT jh.Company, jh.JobNum, ja.AssemblySeq, jo.OprSeq, jo.OpCode, ja.FinalOpr, jo.EstSetHours, jo.EstProdHours, jo.ProdStandard, jo.StdFormat,
CAST(jo.EstSetHours + jo.EstProdHours AS decimal(12, 2)) AS OpTime, jh.JobComplete, jh.JobClosed, jh.JobReleased, jh.JobFirm, CAST(ja.StartDate AS datetime) AS jaStartDate, CAST(ja.DueDate AS datetime) AS jaDueDate,
CAST(jo.LastLaborDate AS datetime) AS LastLaborDate, CAST(jo.RunQty AS int) AS joRunQty, CAST(jo.QtyCompleted AS int) AS joQtyCompleted, jo.OpComplete, CAST(jh.ProdQty AS int) AS ProdQty,
CAST(jh.QtyCompleted AS int) AS jhQtyCompleted, ja.PartNum, p.ProdCode, p.SearchWord, p.OnHoldReason_c, p.CheckBox02 AS Engineering, p.CheckBox04 AS Programming, p.OnHold, jo.SetupComplete,
CAST(jo.StartDate AS datetime) AS joStartDate, jo.StartHour AS joStartHour, CAST(jo.DueDate AS DATETIME) AS joDueDate, CAST(jh.CreateDate AS datetime) AS CreateDate, ja.TLALaborCost, ja.TLAMaterialCost,
ja.TLASubcontractCost, ja.LLALaborCost, ja.LLAMaterialCost, ja.LLASubcontractCost, CAST(jh.ReqDueDate AS datetime) AS jhReqDueDate, jh.Date01 AS jhScheduleDate,
CAST(jh.Date05 AS DateTime) AS jhWeldDate, CAST(jh.Date06 AS DateTime) AS jhInsertDate, CAST(jh.Date04 AS DateTime) AS jhAssemblyDate, jh.ShortChar03 AS Operator_Weld,
jh.ShortChar06 AS Operator_Insert, jh.ShortChar05 AS Operator_Assy, jh.ShortChar04 AS WeldComments, jo.DaysOut, CAST(jh.DueDate AS datetime) AS jhDueDate, jo.ActSetupHours, jo.ActProdHours,
CAST(ja.RequiredQty - ja.PullQty AS int) AS ProductionQty, jo.VendorNum, jh.Character01 AS jhComments, CAST(jh.Number01 AS int) AS jhExpedite, jh.ShortChar01 AS jhQJob,
jh.ShortChar02 AS jhLifecycle, CAST(ja.RequiredQty AS int) AS RequiredQty, CAST(ja.PullQty AS int) AS PullQty, p.ShortChar02 AS pMatType, p.ShortChar03 AS pMatThick,
jo.CommentText, jo.ShortChar01 AS ToolTape, jo.Character02 AS QualityAlert, jo.Character03 AS MesFeedback, CASE WHEN NOT jh.Date01 IS NULL
THEN jh.Date01 - 365 WHEN NOT jh.Date05 IS NULL THEN jh.Date05 WHEN jh.Number01 <> 0 THEN DATEADD(d, jh.Number01, jo.StartDate) ELSE jo.StartDate END AS OpDate, Powder,
sq.jhPaintDate,
IsNull(sq2.MShortages,0) AS MShortages, IsNull(sq2.PShortages,0) AS PShortages
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum LEFT OUTER JOIN
SubQuery AS sq ON jh.Company = sq.Company AND jh.JobNum = sq.JobNum LEFT OUTER JOIN
SubQuery2 AS sq2 ON jh.Company = sq2.Company AND jh.JobNum = sq2.JobNum INNER JOIN
[ERP10-Live].dbo.JobOper AS jo ON ja.Company = jo.Company AND ja.JobNum = jo.JobNum AND ja.AssemblySeq = jo.AssemblySeq OUTER APPLY
(
SELECT jm.PartNum AS Powder
FROM [ERP10-Live].Erp.JobMtl jm
WHERE ja.Company = Company
AND ja.JobNum = JobNum
AND ja.AssemblySeq = AssemblySeq
AND (jm.PartNum LIKE 'POW%')
) AS jm
LEFT OUTER JOIN
[ERP10-Live].dbo.Part AS p ON ja.Company = p.Company AND ja.PartNum = p.PartNum
WHERE (jh.JobComplete = 0) AND (jh.JobClosed = 0) AND (jh.ProdQty - jh.QtyCompleted > 0)
July 13, 2021 at 8:42 pm
As part of testing I removed the JobHead and JobAsmbl tables from both SubQuery statements but the execution jumped from 00:00:01 to return 28,800 records to 00:00:06.
The JobOper and JobMtl tables hold the fields - JobNum and AssemblySeq allowing me to join.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply