SearchOracle.com

COALESCE on three columns

By null

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

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.