-1

I have 3 tables:

Purchase Order

PO ID PO Requestor
PO123 John Snow
PO567 Heather

Purchase Order Line Item:

PO Line Item ID PO Line Item PO ID Ordered Quantity
1234 Bike PO123 3
1235 Laptop PO567 2
1236 BMW PO123 1

And Receipt:

Receipt Line ID PO Line Item ID Received Quantity Received Date
R123 1234 1 01-Dec-20
R567 1236 1 15-Dec-20
R567 1234 2 15-Dec-20
R987 1235 2 20-Dec-20

I want to join 3 tables into 1 table as below, which is not a challenging task:

PO ID PO Line Item No PO Line Item Name Ordered Quantity Receipt Line ID Received Quantity Received Date Requestor
PO123 1 Bike 3 R123 1 01-Dec-20 John Snow
PO123 1 Bike 3 R567 2 15-Dec-20 John Snow
PO123 2 BMW 1 R567 1 15-Dec-20 John Snow
PO567 1 Laptop 2 R987 2 20-Dec-20 Heather

In the final table, I want to add a new column: PO Line Item No. Basically, if the group PO ID is the same, it will assign an increment value for each PO Line Item ID/Name. And please note that because 1 PO Line Item can be broken into multiple receipt dates and lines, so the final table may contain multiple lines for the same PO Line Item. In that case, the PO Line Item No should show the same value for those records in the final table

For example, PO123 has 2 PO Line Items: Bike (ID 1234) with quantity 3 and BMW (ID 1236) with quantity 1. On 1-Dec-2020, 1 Bike was received with receipt ID R123, and On 15-Dec-2020, Receipt ID R567 received BMW and other 2 bikes to complete the PO123. So in the final table, because bikes were received in 2 receipts, there are 2 records for Bike for PO123. the PO Line Item no should be the same for bikes

Please help me to work on this column without using any windows functions. Thank you so much

GMB
  • 216,147
  • 25
  • 84
  • 135
Wanderlust
  • 1
  • 1
  • 3
    Please provide your data as text rather than image links. Also please show us what you have tried so far. – GMB Dec 24 '20 at 01:29
  • At the moment, this post reads like a specification, not a question. – Shadow Dec 24 '20 at 01:48

1 Answers1

0

I think you want dense_rank(). Starting from the receipt table, The base idea is:

select r.*, 
    dense_rank() over(partition by po_id order by po_line_item_id) as rn
from receipt r

In earlier versions, you can emulate that with a subquery:

select r.*, 
    (
        select count(distinct po_line_item_id) + 1
        from receipt r1 
        where r1.po_id = r.po_id and r1.po_line_item_id < r.po_line_item_id 
    ) as rn
from receipt r
GMB
  • 216,147
  • 25
  • 84
  • 135
  • The last edit says without window functions. – Shadow Dec 24 '20 at 02:00
  • Groupwise ranking has been asked several times, so will close the question as duplicate. – Shadow Dec 24 '20 at 02:04
  • @Shadow: Sure. I think the challenge here was to identify the question as such. – GMB Dec 24 '20 at 02:06
  • I don't think this is GroupWise ranking, this is an incrementing number based on 2 different fields/groupings: PO ID and Receipt ID. I tried most of the solutions in the forum but couldn't find a solution without using window functions. I really appreciate if you can give me any links to the solution – Wanderlust Dec 24 '20 at 18:03
  • @Wanderlust: did you try the above queries? What happens when you run them? – GMB Dec 24 '20 at 19:09