Number Rows in BAQ

[tthayer (Trygve Thayer)]

Does anyone have an example of the correct syntax to be able to number the rows in a query?

It would be sincerely appreciated.

[josecgomez]

BAQ return only 1 row per part number

You would recreate this SQL using the BAQ editor. It can be challenging if you don’t know SQL. But you can compare what shows up on the general tab for the BAQ this to see if you are on the right track. The one Ken Posted is a little advanced. These are the fields you grab from part tran table in field selection, except for RowNum. That’s a calculated field. Select a.PartNum, a.TranType, a.TranDate, a.TranQty, b._RowNum This is what you put in the calculated field go get your row numbers. RO…

[tthayer (Trygve Thayer)]

So I have a calculated field in the Epicor10 BAQ and put this in OrderDtl.SysRowID_RowNum but saying it is invalid. There are quite a few tables joined in this query but I am sure the SysRowID is unique to all rows.

[josecgomez]

No you need a calculated field here @Banderson explains it in more detail in this thread.

BAQ to evaluate consecutive rows in table

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” b…

[Banderson (Brandon Anderson)]

row_number() over (partition by table.field, table.field etc order by table.field asc)

You use the groupings if for what you want to group by (you might be able to skip that if you literally only want row numbers) and order by is what the numbering uses to order by. You can also add asc or dec for which way you want the order to go by.

[cchang]

Did you ever get this resolved? Trying to do the same thing to just number the number of rows returned in the query displayed at the time of retrieval.

[tthayer (Trygve Thayer)]

Could not use sysrowid. Had to go another route.

[mriannejoy (marianne)]

Not sure what exactly you need, but this would give you row numbers for each baq line result

row_number() over(order by field you want to sort it)