my client will move epicor premise to epicor cloud. the big problem is we have a lot stored procedure based on the epicor database. now, we have to call rest api. I don’t know how to convert stored procedure into an efficient way to use LINQ to join multiple lists. Some stored procedure is very complicated not just a simple join. it includes cursor, with CTE and etc.
any ideas will be very appreciated.
You can invoke a BAQ via rest.
BAQS have CTE capabilities fully built into them.
We were questioning the same thing. We came to the same conclusion unfortunately.
We use SPs for a couple of enhanced production shortage reports and putting it through BAQ just takes way to long.
We’ll need a complete redesign to get it into BAQs.
but BAQ has performance issue. if the result is more than 1000 records, BAQ failed to bring.
BAQ is not the best solution. any other idea?
The 1000 records limit is in the BAQ designer. That goes away when you call that in other places (like a dashboard)
Make a rest call to the BAQ and tell me what happens. What do you see in the returned JSON, only 1000 rows?
Or is it timing out? I sense it’s what Brandon said.
that’s true. i will try to use BAQ.
for the baq, it give good number of records. now, i have one sql syntax need convert to baq
row_number() over (partition by table.field, table.field etc order by table.field asc)
it is to select top n record by group by. i don’t know how to do that in BAQ. also, I need calculate the running total. don’t know how to do it in BAQ either.
There are many posts on here that show you how to do row number and partition in a BAQ. I don’t have time to explain it right now otherwise I’d dhow you or find the posts.
Put the the over partition in a calculated field and then in subQuery Criteria change the table to calculated and your field
And that can be overridden there as well.