On Wed, 15 Aug 2001, Mike Withers wrote:
> At 06:30 PM 8/14/01 -0700, you wrote:
> >On Wed, 15 Aug 2001, Mike Withers wrote:
> >
> >> Can anyone tell me how I might convert a null attribute value into a zero
> >> attribute value such that it can be multiplied in a query.
> >>
> >> In Oracle I could do:
> >>
> >> sal*12*NVL(COMM, 0) AS "Annual Income"
> >>
> >> where COMM is an attribute (a salesman commission, in an employes table)
> >> which has null values. This allows null commissions for non salesmen to
> >> give a zero calculated value. The NVL converts a null into zero.
> >
> >Try coalesce(COMM, 0)
> >
> Thanks, this works. Tried the other suggestion:
>
> sal * 12 * CAST(COMM AS float8) AS "Annual Income"
>
> which unfortunately didn't work.
Yeah, that'll still give you a null out. I guess oracle must have
done nvl before the standards group decided on coalesce for the name
of that.
