Re: How do I make 2 + Null = 2 ?

From: (wrong string) ît Stephenson <Benoit.Stephenson_at_dmr.ca>
Date: 1996/02/21
Message-ID: <4gfot4$jqa_at_wagner.spc.videotron.ca>#1/1

Russell Foster <Russell_Foster_at_mmacmail.jccbi.gov> wrote:

>I am writing a select statement for Oracle7-NT that needs to return a
>derived column that is the result of an arithmetic operation between two
>other columns. Ex: select col1, col2+(.5*(col3)) as derivedcol from
>table1;
 

>The problem is that if col3 is null in a row then the derivedcol returns
>null, regardless of the value of col2.
 

>I would like to know a way to write this in a way that treats a null col3
>as zero for the math, returning the value of col2 as derivedcol if col3
>is null. The statement must be dynamic SQL.
 

>Much thanks to any SQL Jockeys that can help..

You have to use the function NVL

select col1,

          col2 + (5 * NVL(col3,0) )
from table1;

---

Benoit Stephenson - DMR Group inc.
Benoit.Stephenson_at_dmr.ca Received on Wed Feb 21 1996 - 00:00:00 CET