SQL with conditional where

Could someone please assist with the below query ?

I have a table like below :

Code        alias   user
--------    -----   -------
7305553     BPP     (null)
8136852     BPP     AYU
8136852     BPP     TKL
7305553     BPFX    (null)
8136848     BPFX    YAO
11903927    CIX     (null)

And I want to retrieve the “Code” value by passing “alias” and “user” values, however when the “user” doesn’t match/exist, I’d need the row with the null user. Eg :

select Code from my_table where alias = 'BPP' and user = 'TEST'

should return the 1st line code value (7305553) , as user “TEST” doesn’t exist. Is this kind of best-matching behavior possible with some kind of conditional “where” ? (with some kind of case statement?)

Answer

Try this:

SELECT
    CODE
FROM
    MY_TABLE
WHERE
    ALIAS = 'BPP'
    AND (USER = 'TEST' OR USER IS NULL)
    ORDER BY USER NULLS LAST
    FETCH FIRST ROWS ONLY

Cheers!!

Advertisement

Answer