Hi all,
As the title suggests, whenever I delete an Inner SubQuery in a BAQ, the Calculated Fields in the Top Subquery disappears.
How do I stop this?
Thanks
hymal7
1 Like
Can you copy the query phrase out of the general tab. It sounds like an issue I encountered before when then changes don’t actually update the query phrase which typically means a rebuild of the query from scratch.
Matt
I have renamed the unwanted Subquery as ‘x’ and deleted its contents.
This happens occasionally on other BAQs too.
select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[CreateDate] as [JobHead_CreateDate],
[JobAudit1].[ChangedBy] as [JobAudit1_ChangedBy],
[JobAudit1].[ChangeDescription] as [JobAudit1_ChangeDescription],
[JobHead].[DueDate] as [JobHead_DueDate],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[PartDescription] as [JobHead_PartDescription],
[JobProd].[ProdQty] as [JobProd_ProdQty],
[JobProd].[ReceivedQty] as [JobProd_ReceivedQty],
[JobProd].[WIPQty] as [JobProd_WIPQty],
[JobHead].[QtyCompleted] as [JobHead_QtyCompleted],
(JobAsmbl.TLALaborCost+JobAsmbl.TLABurdenCost+JobAsmbl.TLAMaterialCost+JobAsmbl.TLASubcontractCost+JobAsmbl.TLAMtlBurCost+JobAsmbl.TLASetupHours+JobAsmbl.TLAProdHours+JobAsmbl.LLALaborCost+JobAsmbl.LLABurdenCost+JobAsmbl.LLAMaterialCost+JobAsmbl.LLASubcontractCost+JobAsmbl.LLAMtlBurCost+JobAsmbl.LLASetupHours+JobAsmbl.LLAProdHours) as [Calculated_Total_cost],
[JobHead].[CommentText] as [JobHead_CommentText],
[JobAsmbl].[CommentText] as [JobAsmbl_CommentText],
((case when JobProd.ReceivedQty=JobProd.ProdQty then 1 else 0 end)) as [Calculated_Mtl_isd],
((case when JobProd.ReceivedQty>JobProd.ProdQty then 1 else 0 end)) as [Calculated_mtl_isd2],
(case
when substring(JobHead.JobNum,1 ,1 ) = ‘0’ then substring(JobHead.JobNum,2 ,5 )
else NULL
end) as [Calculated_OrderNo],
(substring(JobHead.CommentText,1 ,1 )) as [Calculated_JobCommentOrderNoStart],
(substring(JobHead.CommentText,2 ,5)) as [Calculated_JobCommentOrderNo],
((case when JobCommentOrderNoStart = ‘0’ then JobCommentOrderNo else OrderNo end)) as [Calculated_LinkedSO],
[JobProd].[TargetJobNum] as [JobProd_TargetJobNum],
[JobHead2].[JobClosed] as [JobHead2_JobClosed]
from Erp.JobHead as JobHead
inner join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
and ( JobProd.OrderNum = 0 )
left outer join Erp.JobHead as JobHead2 on
JobHead2.Company = JobProd.Company
and JobHead2.JobNum = JobProd.TargetJobNum
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
and ( JobAsmbl.AssemblySeq = 0 )
inner join (select
[JobAudit].[Company] as [JobAudit_Company],
[JobAudit].[JobNum] as [JobAudit_JobNum],
(min(JobAudit.SysRevID)) as [Calculated_earliest]
from Erp.JobAudit as JobAudit
group by [JobAudit].[Company],
[JobAudit].[JobNum]) as SubQuery2 on
JobHead.Company = SubQuery2.JobAudit_Company
and JobHead.JobNum = SubQuery2.JobAudit_JobNum
inner join Erp.JobAudit as JobAudit1 on
SubQuery2.JobAudit_Company = JobAudit1.Company
and SubQuery2.JobAudit_JobNum = JobAudit1.JobNum
and SubQuery2.Calculated_earliest = JobAudit1.SysRevID
where (JobHead.JobClosed = 0 and not JobHead.JobNum like ‘%UFM%’ and JobHead.ReqDueDate < dateadd (month, -7, Constants.Today) and JobHead.JobEngineered = 1 and JobHead.JobReleased = 1)
and (JobAsmbl.TLALaborCost+JobAsmbl.TLABurdenCost+JobAsmbl.TLAMaterialCost+JobAsmbl.TLASubcontractCost+JobAsmbl.TLAMtlBurCost+JobAsmbl.TLASetupHours+JobAsmbl.TLAProdHours+JobAsmbl.LLALaborCost+JobAsmbl.LLABurdenCost+JobAsmbl.LLAMaterialCost+JobAsmbl.LLASubcontractCost+JobAsmbl.LLAMtlBurCost+JobAsmbl.LLASetupHours+JobAsmbl.LLAProdHours) = 0 and ((case when JobCommentOrderNoStart = ‘0’ then JobCommentOrderNo else OrderNo end)) is null
This query has a top and a subquery named subquery2 does this BAQ still contain a top level and 1 sub Query? If it doesn’t then the query isn’t updating correctly.
Yes, this one has a 2nd innersubquery as well called “SubQuery2”.
The problem occurred when I tried to delete the 3rd innersubquery .
Just fixed it.
I converted the unwanted InnerSubQuery into TopSubquery, and converted the original TopSubquery into InnerSubQuery.
I then deleted the unwanted SubQuery.
Then restored the original TopSubquery as TopSubquery .
1 Like
Excellent. Good to hear.
1 Like