The COALESCE function returns the first non-null SQL value of a list of column names or literals. If all the operands are NULL, the result is NULL. Column and literal operands for this function must be of the same data type. The syntax is:
COALESCE('string'/colname, 'string'/colname, 'string'/colname, …)
In the following example, assume that only one of the columns of column names COL1, COL2, and COL3 contains a value for an application in any one row. The remaining columns are SQL NULL. All the columns could be NULL. The COALESCE function can display the one value in the columns or a default value.
“SELECT COALESCE(COL1, COL2, COL3, 'NOVALUE') FROM APPLTAB WHERE NAME='APPL10'”
Because this is the first COALESCE function reference in the SQL statement, the REXX variable stem created for the function value is COALESCE_1. If COL2=YES, then COALESCE_1.1=YES. If all the columns are NULL, COALESCE_1.1=NOVALUE.
|
Copyright © 2014 CA.
All rights reserved.
|
|