Imran Hemani Imran Hemani - 5 months ago 8
SQL Question

Oracle SQL - getting value based on a column

I have two tables:

TR_TRN (ID_WS, STORE, STATUS)
TR_RTL (ID_WS, STORE, ID_LY)


The status in TR_TRN is either 19 or 20.

Some records have both the statuses 19 and 20.
and some have only 19 (meaning that they are pending transactions)

the two tables are joined with:

ID_WS and STORE


I need all the records from TR_RTL that has only the status of 19 in the TR_TRN table.

How do I do that ?

Answer

You can use EXISTS() and use the HAVING clause in the inner query to determine if this record has only status = 19 with CASE EXPRESSION :

SELECT * FROM TR_RTL t
WHERE EXISTS(SELECT 1 FROM TR_TRN s
             WHERE s.id_ws = t.id_ws and s.store = t.store
             HAVING COUNT(CASE WHEN s.status = 19 THEN 1 END) = 1
                AND COUNT(CASE WHEN s.status = 20 THEN 1 END) = 0)