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)

​x
 
Code        alias   user--------    -----   -------7305553     BPP     (null)8136852     BPP     AYU8136852     BPP     TKL7305553     BPFX    (null)8136848     BPFX    YAO11903927    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'

 
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

 
SELECT    CODEFROM    MY_TABLEWHERE    ALIAS = 'BPP'    AND (USER = 'TEST' OR USER IS NULL)    ORDER BY USER NULLS LAST    FETCH FIRST ROWS ONLY​

Cheers!!

Advertisement

Answer