IF statement within a SELECT - SQL

Question

I'm trying to write a query, that selects a number of values. I only want it to select one of the values if it isn't null.

I'm trying to use a case when but it is erroring.

SELECT pick_no, 
   pd.product, 
   from_warehouse, 
   to_warehouse, 
   qty_pick, 
   qty_check, 
   qty_picked, 
   qty_checked, 
   long_description, 
   ROUND(qty_pick / stk.pallet_unit_qty, 2) as [PalletQty], 
   ph.date_picking,
   stk.bin_no,
   CASE WHEN qty_picked <> null
            THEN ROUND(qty_picked / stk.pallet_unit_qty, 2) as [pltCheck]
            ELSE '0' END

FROM

Or simply `coalesce(ROUND(qty_picked / stk.pallet_unit_qty, 2), 0) as [pltCheck]` — jarlh, Mar 08 '18 at 16:00
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — a_horse_with_no_name, Mar 08 '18 at 16:00

score 2 · Accepted Answer · answered Mar 08 '18 at 16:01

Null is a tricky beast, you can't use equality operands on it. Also your else clause was returning a CHAR when the first clause returns a FLOAT :

CASE WHEN qty_picked IS NOT NULL
        THEN ROUND(qty_picked / stk.pallet_unit_qty, 2) as [pltCheck]
        ELSE 0 END

A simpler way to achieve your goal is to use COALESCE :

COALESCE(ROUND(qty_picked / stk.pallet_unit_qty, 2),0) as [pltCheck]

IF statement within a SELECT - SQL

1 Answers1