Help with CTE error: The multi-part identifier "PartMtl1.MtlPartNum" could not be bound

Kinetic ERP
1

I’m hoping someone has seen this and fixed it?

I’m trying to work through the Ice tools CTE example and I’m facing this error? Here is the Query:

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
with [SubQuery1] as 
(select 
	[PartMtl].[Company] as [PartMtl_Company],
	[PartMtl].[PartNum] as [PartMtl_PartNum],
	[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
	[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	[PartMtl].[QtyPer] as [PartMtl_QtyPer],
	[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
	[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
	[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
	[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
	(0) as [Calculated_Hierarchy],
	(cast(substring('........',1,(Hierarchy + 1)) + PartMtl.MtlPartNum as nvarchar(25))) as [Calculated_Ind1]
from Erp.PartMtl as PartMtl
where (PartMtl.PartNum = @PartNum)

union all
select 
	[PartMtl1].[Company] as [PartMtl1_Company],
	[PartMtl1].[PartNum] as [PartMtl1_PartNum],
	[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
	[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
	[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
	[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
	[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
	[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
	[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
	[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
	[SubQuery1].[Calculated_Hierarchy] as [Calculated_Hierarchy],
	[SubQuery1].[Calculated_Ind1] as [Calculated_Ind1]
from Erp.PartMtl as PartMtl1
inner join  SubQuery1  as SubQuery1 on 
	PartMtl1.MtlPartNum = SubQuery1.PartMtl_MtlPartNum
	and PartMtl1.Company = SubQuery1.PartMtl_Company)

select 
	[SubQuery11].[PartMtl_Company] as [PartMtl_Company],
	[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
	[SubQuery11].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
	[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
	[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
	[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
	[SubQuery11].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
	[SubQuery11].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
	[SubQuery11].[PartMtl_PlanAsAsm] as [PartMtl_PlanAsAsm],
	(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
	(cast ( substring('........',1 ,(Hierarchy2 + 1) ) + PartMtl1.MtlPartNum as nvarchar(25))) as [Calculated_Ind2]
from  SubQuery1  as SubQuery11
2

In your cast at the end, did you try PartMtl.MtlPartNum instead?

3

I tried that and the error just reverted to unable to bind PartMtl? So no joy there.

4

I think it might be this. You should be joining the Material from the first query to the Part on the second query.

5

I recreated the query and it now works. Ghost in the machine! lol.

2 Likes