On Fri, 19 Oct 2012 07:40:32 +0200, Nols Smit <[email protected]> wrote: >>No need for three COALESCEs if you want to multiply: > >>and coalesce(p.total_revenue*p.exchange_rate*p.cgsshare_pct,0) = 0)) > > There are cases where these variables may have null values.
That is the reason this is used: NULL * <number> = NULL. So the above multiplication is identical to your original condition which used COALESCE(<operand>, 0) = 0 on each individual operand and combined them with OR: COALESCE(<operand1>, 0) = 0 OR COALESCE(<operand2>, 0) = 0 OR COALESCE(<operand3>, 0) = 0 <=> COALESCE(<operand1> * <operand2> * <operand3>, 0) = 0 Mark
