Offset and Fetch in BAQ

Kinetic ERP
, ,
1

Hi, i am trying to create a BAQ where it lists all my Price Breaks as columns rather than in Rows using (So i can easily pop them into Text boxes in a kinetic menu item)

There will never be more than 3 price breaks per part num.
I can easily retrieve the first and last by using MIN() & MAX() but retrieving the 2nd Price Break in the table for that Part Num is proving more difficult than i think it should.

I thought i would be able to create a duplicate subquery which loads all 3 price breaks into my Top Level query (this works), i then thought in the sub query i could use Offset and Fetch to forget about the first row, bring me the 2nd and load no more rows.

My issue is it loads the top row of the entire query, it doesn’t take into account that i need to group by ListCode and PartNum…

a copy of how my query currently looks. (i have filtered to 1 ListCode and 1 PartNum for easy viewing.

image
image926Ă—89 8.14 KB

2

I got around this by creating another 2 sub query which returned MIN Qty and MAX Qty respectively and when I joined them to main query i did a NOT relationship join on the Qty field which left me with the middle Qty break.

1 Like