CODEWITHSUNDEEP
sqloracle-databasecase
artemis
artemis

Reputation: 7241

Oracle SQL - Using CASE WHEN to select a proper date field

I have a complicated problem and I cannot for the life of me figure out the logic in my head.

I am trying to tackle the issue of building a query reporting the proper dates for a material event. Essentially, my company is trying to track inventory management by seeing when we buy (quantity+) a part, when we manufacture (quantity+) a part, when we sell (quantity-) a part, or when we use that part to build a higher assembly part (quantity-).

The table structures I am working with look like this:
enter image description here

Where there can be 4 types of events, each having their own unique identifier, and each having a date on which that event occurs.

I then have the "Happenings" table, which actually has the list of transactions, that looks like this: enter image description here

(Note I have eliminated some irrelevant field names).

What I am trying to accomplish is using a CASE...WHEN statement to select the proper date based on if the current line's foreign key.

So something to the effect of:

CASE
    WHEN Type = "Purchase Order" THEN (SELECT Date FROM Events WHERE Events.Number = Happenings.PO_ID)
    WHEN TYPE = "Make Order" THEN (SELECT Date FROM Events WHERE Events.Number = Happenings.MO_ID)
    WHEN TYPE = "Sale" THEN (SELECT Date FROM Events WHERE Events.Number = Happenings.Sale_ID)
    WHEN TYPE = "Create Order" THEN (SELECT Date FROM Events WHERE Events.Number = Happenings.CO_ID)
    ELSE NULL AS Event_Date

Unfortunately, I cannot post too much data as it is company proprietary, but that is the gist of what I am trying to accomplish; using CASE...WHEN to properly select a value from another table when my current row contains the unique identifier.

Upvotes: 1

Views: 63

Answers (2)

GustyWind
GustyWind

Reputation: 16

try to use nvl function for organize Happenings table. NVL( PO_ID, NVL( MO_ID, NVL(CO_ID, NVL(Sale_ID, 0) ))) AS IDS

then

Events.Number = IDS

hope it helps

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

Try a case on the join condition

select e1.date 
from events e1
inner join happenings h2
on e1.Number = case when e1.type = 'Purchase' then h2.PO_ID
                    when e1.type = 'Make' then h2.MO_ID
                    ...
               end

Upvotes: 2

Related Questions