Db2 – SQL DB2 query with where clause

db2

Can you please help me out with the below query?

I need to extract from column VAL_DT only items with date older than today's date but this rule should not apply to items that contain the following wordings in column MVT_DS: "NOSTRO", "SCREV", "SCRIN", "MAN".

here is my attempt

SELECT * FROM table_name 
WHERE VAL_DT < CURRENT DATE  IF MVT_DS <> 'NOSTRO' AND MVT_DS <> 'SCREV' AND MVT_DS <> 'SCRIN' AND MVT_DS <> 'MAN'

Best Answer

I would amend your logic to:

WHERE 
    VAL_DT < CURRENT DATE 
    OR 
    (
        MVT_DS = 'NOSTRO' 
        OR MVT_DS = 'SCREV' 
        OR MVT_DS = 'SCRIN' 
        OR MVT_DS = 'MAN'
    )

or even

WHERE 
    VAL_DT < CURRENT DATE 
    OR MVT_DS IN (
      'NOSTRO', 'SCREV', 'SCRIN', 'MAN'
    )

I changed it so that VAL_DT should be earlier than the current date or the other field should equal the respective values. So to get a "true" result - either VAL_DT < "current date" or MVT_DS is one of those values. If it's one of those values it will therefore bypass the "current date" check, effectively.

Related Question