BAQ - Create a breaking sequence field

Epicor ERP 10
1

Hey,

I’m displaying all the serial numbers that go with an order line. I’d like to give each row a sequence number starting at one each time the order/line changes.

I’ve tried ROW_NUMBER with OVER but managed only to rank the rows against the entire results.

What I’d like to see:

Order 1, Line 1, Serial 12345, Sequence 1
Order 1, Line 1, Serial 12346, Sequence 2
Order 1, Line 1, Serial 12347, Sequence 3
Order 1, Line 2, Serial 12345, Sequence 1
Order 1, Line 2, Serial 12346, Sequence 2
Order 1, Line 2, Serial 12347, Sequence 3

Wisdom from the sages?

Thanks,

Joe

2

I believe you need to do a PARTITION BY and a ORDER BY in your OVER.

1 Like
3

Thanks, John. I’ll give it a try.

Joe

4

you will want to partition by order & line, and then order by the serial number.

1 Like
5

try ROW_NUMBER() over(partition by Company, OrderNum)

if you want the numbering to follow the order lines & serials sort use this:

ROW_NUMBER() over(partition by Company, OrderNum order by Company, OrderNum, OrderLine, SerialNumber)

1 Like
6

Hi,

I finally got back to this. Worked great.

Thanks,

Joe

1 Like