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"

sal * 12 * CAST(COMM AS float8) AS "Annual Income"

Cheers

Jason
--
Indigo Industrial Controls Ltd.
64-21-343-545
jasont@indigoindustrial.co.nz

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)

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.