COALESCE


There is now a COALESCE function (not to be confused with coalesce for indexes) which returns the first non-null item in a list.

SQL> SELECT product_id, list_price, min_price,
 1   COALESCE(0.9*list_price, min_price, 5) "Sale"
 2   FROM product_information
 3   WHERE supplier_id = 102050;

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      2382        850        731        765
      3355                                5
      1770                    73         73
      2378        305        247      274.5
      1769         48                  43.2