Yea, you probably need to filter it to only one parent part number.
It seemed to prompt for a parent part number.
@tthayer did you eventually find a happy place where the BAQ would complete in a reasonable amount of time?
I really wanted to find the parent where the Effective Date was not greater than the getdate(), and I was able to speed up the query by taking the PartPlant and Part tables and criteria out of the AllPartRevs subquery.
But I am not quite certain as to what ramifications that may have exactly. Although it actually seems to be placing expected parts through a method that I am looking at on a sample part.
I did get it to run faster but it was not by taking out tables. Even though the query was running in a specific company I found if I put a filter in on each table for company = ???. That made a huge difference. What I am thinking is by putting in the filters that criteria is then passed on to sql. Where if I did not do that sql returned everything and then epicor filtered it on the return.
That was my similar train of thought, that the Company and Plant if needed, are on PartRev anyways. So I moved criteria there and then poofed the PartPlant and Part tables, giving up also the Source and InActive parameters, which in my specific business use case appears to cause no issue. But, I can see where that would be useful, so maybe I’ll create myself another iteration and apply the criteria as you mention and see where I end up.
Originally I did boost QueryTimeout to 60 seconds and observed the timeout, decided it was better to optimize the query.
Thank you for your reply.
Firstly thank you to you all for the sample query and feedback. I am trying this out in our environment (10.2.700.23). The query imported and had no issues. No results for a top level part. I then removed the Company/Plant filters as we are single company/plant and thought it would speed the BAQ up. It did not and still got no results even when I moved it over to a Dashboard where it ran a little longer but nothing came up.
Also tried removing the PartPlant and Part tables from the AllPartRevs subquery with still no results.
Any ideas as to how I can get this BAQ to work and return results?
I downloaded the BAQ as well but I get this error Incorrect syntax near ‘(’.
I traced it to the calculated field RANK. When I use this expression in a different BAQ it works fine. If I remove it I get other errors related to the field RANK is not valid
rank() over (partition by PartRev.PartNum Order By PartRev.EffectiveDate desc, PartRev.RevisionNum desc)
Has anyone else seen this or gotten the BAQ to work for them?
Are you really running 10.1.4… what version of SQL?
Reason I ask, some BAQ examples will only work using the same or later versions they were built in.
Also, have you tried searching Indented"? I think you’ll find a lot of examples for BOM’s.
e.g.
10.2.200.5 But the syntax works in another BAQ. I’ll see if I can find other examples
Thanks
Note in addition to the indenting of your BOM’s levels,
you’ll (probably) need to make sure revisions are handled correctly too.
2 Likes
and REVISIONS is what makes this one of the tougher queries to build because you don’t want ALL the revisions of the children’s parts. you only want to see the CURRENT.
How do you know that the Rank() is the part that is giving you problems?
Because when I keep that calculated field and remove the expression I replace it with 0. The syntax error goes away then. As soon as I add the expression back for the RANK calculated field the syntax error comes back
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