Occasionally receiving errors happen, causing unexpected changes in part costs. I am trying to write a BAQ to compare the highest-numbered PartTran.TranNum with the next highest one, flagging entries with unit cost changes greater than a threshold.
To this end, I’ve created a BAQ with a main subquery to pull in recent PartTran rows for a part in question. And I’ve created a subquery (joined to PartTran with matches on Company & PartNum). What I want from the subquery’s result set is not “All” but “Top 2” (and I’ll filter out the 1st row in the join), ordering the results on TranNum.
I’m doing something wrong and can’t make it work. Ideas welcome.
If I apply a Top option and a row number quantity (even a very large number), the query results set is nothing. If I leave “All” in place, naturally I get too many rows.
Top 2 rows for each part number, enabling cost comparisons between them.
(In building the query and with an attempt to keep the results set manageable, I’m currently filtering on one specific part. But once I’m able to compare the cost associated with the newest row and the second row, then I’ll remove the part number filter and see several thousand results–one for each part in my inventory.
Here’s one way to do it. Make 2 subqueries, on returning top 2 rows, the other returning only the top row. Then join those to on company and part number, and where trannum <> to trannum. This will get your 2 rows into one, and you can attach your parttran cost information to come along for the ride.
This will only work though if you are filtering by the part number all of the way through. If you want to be able to make this work with more than one part number at a time, I think you will need to use windowing.
select
[TwoRows].[PartTran_Company] as [PartTran_Company],
[TwoRows].[PartTran_PartNum] as [PartTran_PartNum],
[TwoRows].[PartTran_TranNum] as [PartTran_TranNum],
[OneRow].[PartTran1_Company] as [PartTran1_Company],
[OneRow].[PartTran1_PartNum] as [PartTran1_PartNum],
[OneRow].[PartTran1_TranNum] as [PartTran1_TranNum]
from (select top (2)
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranNum] as [PartTran_TranNum]
from Erp.PartTran as PartTran
where (PartTran.PartNum = 'WN06C')
order by PartTran.TranNum Desc) as TwoRows
inner join (select top (1)
[PartTran1].[Company] as [PartTran1_Company],
[PartTran1].[PartNum] as [PartTran1_PartNum],
[PartTran1].[TranNum] as [PartTran1_TranNum]
from Erp.PartTran as PartTran1
where (PartTran1.PartNum = 'WN06C')
order by PartTran1.TranNum Desc) as OneRow on
TwoRows.PartTran_Company = OneRow.PartTran1_Company
and TwoRows.PartTran_PartNum = OneRow.PartTran1_PartNum
and not TwoRows.PartTran_TranNum = OneRow.PartTran1_TranNum
So if you make a calculated field like this to get your row numbers, this will number your rows 1-x with the max being first. Then you can filter by row number 1 and 2. I would still probably do 2 nearly identical subqueries with this field in them, then filter one by RowNumber 1 and the second by RowNumber 2 and then you can do the math that you need on one row.
Row_Number() over(partition by PartTran.PartNum order by PartTran.TranNum desc)
Here’s the full query. From here, you should be able to tack on your cost information from part tran
select
[TwoRows].[PartTran_Company] as [PartTran_Company],
[TwoRows].[PartTran_PartNum] as [PartTran_PartNum],
[TwoRows].[PartTran_TranNum] as [PartTran_TranNum],
[OneRow].[PartTran1_Company] as [PartTran1_Company],
[OneRow].[PartTran1_PartNum] as [PartTran1_PartNum],
[OneRow].[PartTran1_TranNum] as [PartTran1_TranNum],
[TwoRows].[Calculated_RowNumbers] as [Calculated_RowNumbers],
[OneRow].[Calculated_RowNumbers] as [Calculated_RowNumbers01]
from (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranNum] as [PartTran_TranNum],
(Row_Number() over(partition by PartTran.PartNum order by PartTran.TranNum desc)) as [Calculated_RowNumbers]
from Erp.PartTran as PartTran) as TwoRows
inner join (select
[PartTran1].[Company] as [PartTran1_Company],
[PartTran1].[PartNum] as [PartTran1_PartNum],
[PartTran1].[TranNum] as [PartTran1_TranNum],
(Row_Number() over(partition by PartTran1.PartNum order by PartTran1.TranNum desc)) as [Calculated_RowNumbers]
from Erp.PartTran as PartTran1) as OneRow on
TwoRows.PartTran_Company = OneRow.PartTran1_Company
and TwoRows.PartTran_PartNum = OneRow.PartTran1_PartNum
and ( OneRow.Calculated_RowNumbers = 1 )
where (TwoRows.Calculated_RowNumbers = 2)
Hi @Banderson,
If I had a local instance of Epicor E10 and could point Microsoft SSMS at it, I believe your SQL would work perfectly or get me 98% of the way home. I’m a cloud customer and thus must work through the BAQ tool. The General tab in BAQ shows me the Query Phrase and I so much wish I could edit in there, but I can’t.
Either I must filter by part in the sub-queries (like your first example) or learn a few more BAQ tricks. Specifically, I’m hung up on this clause:
You can type that into a calculated field. I didn’t write the SQL from scratch (well I typed the calculated field), I did all of it using the BAQ tool. No external references is what I mean. Remember you can use most any SQL functions in the BAQ. The menu on the side only has the basic stuff, but you can use much more.
Create a new calculated field, call it whatever you want, type integer.
Then type what’s shown below. “Row_Number()”, “Over” and “Partition by” do no show up the menus, but they are allowed to be used. So have to type that part. You can double click on the fields that you want to use (in fact I recommend doing that).
At this point, go ahead and test the query. You will see that if you sort by part number, you will see row numbers that start over at each part number.
Hi @Banderson,
I failed to notice that you had uploaded the code; mea culpa.
You’d make a good teacher with all your patience.
Your profile seems to show that you’re located a few hours west of me; I rarely get to your section of the state. But if you’re ever in the Twin Cities, I’d be happy to treat you to lunch.
The next thing you want to do is make this a subquery instead of a top level. We do that in the subquery options section.
Now in the same screen we can make the next subquery, which is going to be basically the same thing.
Now we go to the phrase build tab, grab the PartTran Table again. Since we already have one, a popup will appear, saying “you have to call it something different, dude” (My words not epicors) You can accept and it will call PartTran1
Now that you are returning the rows you need, you can go back and get the information that you need to come along for the ride (like unit cost) and do a calculated field on the top level to see your difference.
