When you look at it on the general tab can you see where the error is?
No it’s one of those where it could be any expression so I had to remove each one until the error went away
Can you paste the SQL from the general tab so I can look and see if there is a syntax error?
Here you go. I appreciate anything you may see
with [ActivePartRev] as
(select
[AllPartRevs].[PartRev_PartNum] as [PartRev_PartNum],
[AllPartRevs].[PartRev_RevisionNum] as [PartRev_RevisionNum],
[AllPartRevs].[PartRev_AltMethod] as [PartRev_AltMethod],
[AllPartRevs].[PartRev_EffectiveDate] as [PartRev_EffectiveDate]
from (select
[PartRev].[PartNum] as [PartRev_PartNum],
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
[PartRev].[AltMethod] as [PartRev_AltMethod],
[PartRev].[EffectiveDate] as [PartRev_EffectiveDate],
[PartRev].[Approved] as [PartRev_Approved],
(rank() over (partition by PartRev.PartNum Order By PartRev.EffectiveDate desc, PartRev.RevisionNum desc)) as [Calculated_Rank]
from Erp.PartRev as PartRev
inner join Erp.PartPlant as PartPlant on
PartPlant.Company = PartRev.Company
and PartPlant.PartNum = PartRev.PartNum
and PartPlant.Plant = PartRev.Plant
and ( PartPlant.Plant = @CurrentPlant and PartPlant.SourceType = 'M' )
inner join Erp.Part as Part on
Part.Company = PartPlant.Company
and Part.PartNum = PartPlant.PartNum
and ( Part.InActive = 0 )) as AllPartRevs
where (AllPartRevs.Calculated_Rank = 1))
,[Anchor] as
(select
[ActivePartRev].[PartRev_PartNum] as [PartRev_PartNum],
(0) as [Calculated_Level],
(cast(ActivePartRev.PartRev_PartNum as nvarchar(1000))) as [Calculated_SortOrder],
[Part1].[PartNum] as [Part1_PartNum],
(cast(@Quantity as decimal)) as [Calculated_QtyPer],
(cast(@Quantity as decimal)) as [Calculated_ExtQty],
(0) as [Calculated_MtlSeq]
from ActivePartRev as ActivePartRev
inner join Erp.Part as Part1 on
ActivePartRev.PartRev_PartNum = Part1.PartNum
and ( Part1.Company = @CompanyID and Part1.LowLevelCode = 0 )
where (ActivePartRev.PartRev_PartNum = @ParentPartNum)
union all
select
[PartMtl].[PartNum] as [PartMtl_PartNum],
(Anchor.Calculated_Level + 1) as [Calculated_Level],
(cast(Anchor.Calculated_SortOrder + '-'+
right('0000' + cast(PartMtl.MtlSeq as nvarchar(4)),4)
as nvarchar(1000))) as [Calculated_SortOrder],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
(cast(PartMtl.QtyPer as decimal)) as [Calculated_QtyPer],
(cast(QtyPer * Anchor.Calculated_ExtQty as decimal)) as [Calculated_ExtQty],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq]
from Anchor as Anchor
inner join ActivePartRev as ActivePartRev1 on
Anchor.PartRev_PartNum = ActivePartRev1.PartRev_PartNum
inner join Erp.PartMtl as PartMtl on
ActivePartRev1.PartRev_PartNum = PartMtl.PartNum
and ActivePartRev1.PartRev_RevisionNum = PartMtl.RevisionNum
and ActivePartRev1.PartRev_AltMethod = PartMtl.AltMethod
and ( PartMtl.Company = @CompanyID ))
select
[Anchor1].[PartRev_PartNum] as [PartRev_PartNum],
[Anchor1].[Part1_PartNum] as [Part1_PartNum],
[ActivePartRev2].[PartRev_RevisionNum] as [PartRev_RevisionNum],
(sum(PartWhse.OnHandQty)) as [Calculated_onhand],
(iif(onhand < Anchor1.Calculated_ExtQty,1,0)) as [Calculated_short],
(replicate(' ', Anchor1.Calculated_Level)+'+'+ Anchor1.PartRev_PartNum) as [Calculated_IndentedView]
from Anchor as Anchor1
left outer join ActivePartRev as ActivePartRev2 on
Anchor1.PartRev_PartNum = ActivePartRev2.PartRev_PartNum
left outer join Erp.PartWhse as PartWhse on
Anchor1.PartRev_PartNum = PartWhse.PartNum
group by [Anchor1].[PartRev_PartNum],
[Anchor1].[Part1_PartNum],
[ActivePartRev2].[PartRev_RevisionNum]
It’s that parens. It’s not necessary. Can you figure out why it’s trying to add that in there? Or you can add another one after the ‘M’.
I’ll try that and let you know. Really appreciate it!
Thanks, this will help me a lot and just took a few minutes to tailor to our system.
– Lisa