Thread: Null Conversion
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. Thanks in anticipation Mike Withers University of Western Sydney Australia
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)
> 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" sal * 12 * CAST(COMM AS float8) AS "Annual Income" Cheers Jason -- Indigo Industrial Controls Ltd. 64-21-343-545 jasont@indigoindustrial.co.nz
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) > > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > Thanks, this works. Tried the other suggestion: sal * 12 * CAST(COMM AS float8) AS "Annual Income" which unfortunately didn't work.
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.