Yeah, for the most part I think. This is the one for the the assemblies
This is the one for the materials
Then the union is joined to a UD table which has the job number, assembly sequence, and material sequences as key 1 2 and 3.
It just ran for over an hour, then I killed it and it shows 2939 rows and says it’s can’t continue because it’s in kill state.
any tips on how I read this? I had to change the extension to txt because I couldn’t upload .sqlplan to the website.
PackOrNotUpdateableTest.txt (99.9 KB)
?
Because I know you can read SQL easily Ken here is the query
select
[UD08].[Key1] as [UD08_Key1],
[UD08].[Key2] as [UD08_Key2],
[UD08].[Key3] as [UD08_Key3],
[UD08].[Key4] as [UD08_Key4],
[UD08].[Key5] as [UD08_Key5],
[UD08].[ShortChar02] as [UD08_ShortChar02],
[UD08].[Number01] as [UD08_Number01],
[JobMtlTable].[JobMtl_RequiredQty] as [JobMtl_RequiredQty],
[JobMtlTable].[JobMtl_Company] as [JobMtl_Company],
[JobMtlTable].[JobMtl_JobNum] as [JobMtl_JobNum],
[JobMtlTable].[JobMtl_AssemblySeq] as [JobMtl_AssemblySeq],
[JobMtlTable].[JobMtl_MtlSeq] as [JobMtl_MtlSeq],
[JobMtlTable].[JobMtl_PartNum] as [JobMtl_PartNum],
[JobMtlTable].[JobMtl_Description] as [JobMtl_Description],
[Part].[ClassID] as [Part_ClassID],
[JobMtlTable].[JobAsmbl1_PartNum] as [JobAsmbl1_PartNum],
[JobMtlTable].[JobOper_OpCode] as [JobOper_OpCode],
[UD08].[ShortChar01] as [UD08_ShortChar01],
[UD08].[Character01] as [UD08_Character01],
[PackStatusSuggest].[Calculated_CountofStatus] as [Calculated_CountofStatus],
[PackStatusSuggest].[Calculated_StatusSuggest] as [Calculated_StatusSuggest]
from Ice.UD08 as UD08
inner join (select
[JobMtl].[Company] as [JobMtl_Company],
[JobMtl].[JobNum] as [JobMtl_JobNum],
[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
[JobMtl].[MtlSeq] as [JobMtl_MtlSeq],
[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobMtl].[Description] as [JobMtl_Description],
[JobMtl].[RequiredQty] as [JobMtl_RequiredQty],
[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
[JobOper].[OpCode] as [JobOper_OpCode]
from Erp.JobMtl as JobMtl
inner join Erp.JobAsmbl as JobAsmbl1 on
JobMtl.Company = JobAsmbl1.Company
and JobMtl.JobNum = JobAsmbl1.JobNum
and JobMtl.AssemblySeq = JobAsmbl1.AssemblySeq
inner join Erp.JobOper as JobOper on
JobMtl.JobNum = JobOper.JobNum
and JobMtl.Company = JobOper.Company
and JobMtl.AssemblySeq = JobOper.AssemblySeq
and JobMtl.RelatedOperation = JobOper.OprSeq
where (JobMtl.JobNum = @JobNum)
union
select
[JobAsmbl].[Company] as [JobAsmbl_Company],
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
(0) as [Calculated_MtlSequenceAssy],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[Description] as [JobAsmbl_Description],
[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
[JobAsmbl2].[PartNum] as [JobAsmbl2_PartNum],
[JobOper1].[OpCode] as [JobOper1_OpCode]
from Erp.JobAsmbl as JobAsmbl
inner join Erp.JobAsmbl as JobAsmbl2 on
JobAsmbl.Company = JobAsmbl2.Company
and JobAsmbl.JobNum = JobAsmbl2.JobNum
and JobAsmbl.Parent = JobAsmbl2.AssemblySeq
inner join Erp.JobOper as JobOper1 on
JobAsmbl.Company = JobOper1.Company
and JobAsmbl.JobNum = JobOper1.JobNum
and JobAsmbl.Parent = JobOper1.AssemblySeq
and JobAsmbl.RelatedOperation = JobOper1.OprSeq
where (JobAsmbl.JobNum = @JobNum)) as JobMtlTable on
UD08.Company = JobMtlTable.JobMtl_Company
and UD08.Key1 = JobMtlTable.JobMtl_JobNum
and UD08.Key2 = JobMtlTable.JobMtl_AssemblySeq
and UD08.Key3 = JobMtlTable.JobMtl_MtlSeq
inner join Erp.Part as Part on
JobMtlTable.JobMtl_Company = Part.Company
and JobMtlTable.JobMtl_PartNum = Part.PartNum
left outer join (select
[GroupPackStatus].[UD081_ShortChar01] as [UD081_ShortChar01],
[GroupPackStatus].[UD081_ShortChar03] as [UD081_ShortChar03],
(count( GroupPackStatus.UD081_ShortChar02 )) as [Calculated_CountofStatus],
((case when CountofStatus = 1 then max( GroupPackStatus.UD081_ShortChar02 ) else '' end)) as [Calculated_StatusSuggest]
from (select
[UD081].[ShortChar01] as [UD081_ShortChar01],
[UD081].[ShortChar03] as [UD081_ShortChar03],
[UD081].[ShortChar02] as [UD081_ShortChar02]
from Ice.UD08 as UD081
group by [UD081].[ShortChar01],
[UD081].[ShortChar03],
[UD081].[ShortChar02]) as GroupPackStatus
group by [GroupPackStatus].[UD081_ShortChar01],
[GroupPackStatus].[UD081_ShortChar03]) as PackStatusSuggest on
UD08.ShortChar01 = PackStatusSuggest.UD081_ShortChar01
and UD08.ShortChar03 = PackStatusSuggest.UD081_ShortChar03
where (UD08.Key1 = @JobNum)