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

Russell Foster <Russell_Foster_at_mmacmail.jccbi.gov> writes:

>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.

You want to use the NVL() function. The syntax is

        NVL(expr1, expr2)
where the function call returns expr2 if expr1 is NULL and returns expr1 otherwise. So, your example would be revised to

        SELECT Col1, NVL(Col2, 0)+(.5*NVL(Col3, 0)) DevivedCol FROM Table1;

-Ron Sires
Terrace Systems Corp.

-- 
========================================================================
     Ron Sires
     <rons_at_netcom.com> | <rsires_at_well.sf.ca.us>
========================================================================