| From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
|---|---|
| To: | Mike Withers <M(dot)withers(at)uws(dot)edu(dot)au> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Null Conversion |
| Date: | 2001-08-15 04:38:05 |
| Message-ID: | Pine.BSF.4.21.0108142137160.72144-100000@megazone23.bigpanda.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | s8854617 | 2001-08-15 05:19:39 | How to create a stand alone plpgsql proc |
| Previous Message | Tom Lane | 2001-08-15 04:23:24 | Re: explain, planner and more.. |