COALESCE on three columns

I have three columns (NUM1, NUM2, NUM3) that are of "number" datatype, and while inserting to another table using INSERT INTO table2 SELECT * FROM table1, I want to insert the three columns into one column (NUM) in my table2. In my case, only one of the three columns will have a value in a row, while the other two will be null. I'm using Oracle8i.

The answer for the nulls problem is to use the COALESCE function. This is a standard SQL function that takes a parameter consisting of a list of expressions:

COALESCE( expression1, expression2, ...)

The result of the function is the first non-null value starting from the left. If all the expressions in the function are null, then the result is null.

Thank you for mentioning the database system that you're using. Fortunately, Oracle supports COALESCE (otherwise you would have to build a CASE structure, which is more verbose).

insert 
  into table2 
     ( foo
     , bar
     , num )
select foo
     , bar
     , coalesce( num1, num2, num3, 0 )
  from table1

Notice that the 0 is added as a fourth expression to guard against all three of NUM1, NUM2, NUM3 being null.

For More Information


Dig Deeper on Oracle development languages

Data Management
Business Analytics
SearchSAP
TheServerSide.com
Data Center
Content Management
HRSoftware
Close