Function Coalesce() | Xbase++

Returns the first non NIL/NULL expression among its arguments.

Coalesce( <Expression,...> ) --> <Expression>

Can be an expression of any type.

Returns the first non NIL/NULL value of its arguments evaluated from the left to the right most parameter passed. If all expressions evaluate to NIL the function returns NIL.

The function Coalesce() is used to determine the first valid value of a set of values. Instead of using the function Coalesce() the programmer can use IF/ELSEIF statements. However using nested IF statements in database expressions is not possible.

Furthermore the function Coalesce() is supported by many database management systems which facilitate business logic to be more efficient. It is considered good practice using Coalesce() or CoalesceEmpty() instead of writing IIF() in database expressions, specifically if Xbase++ applications accessing SQL database systems are developed.

The function Coalesce() is often used to substitute a default value for NULL/NIL values when data is displayed, for example:

// display description if given, otherwise short_description. If 
// booth have no valid data, display "(n/a)" as a last resort. 
? Coalesce(description, short_description, "(n/a)")

Coalesce()

// The following sample calculates the salary of all merchant-fleet employees. 
// Independent if their salary is fixed, variable based on hours worked 
// or depending on sales closed. 
// 

/* 
 * The Employees table has the following structure: 
 * 
 * Emp_ID      , N , 5  , 0 
 * Name        , C , 20 , 0 
 * Salary      , N , 10 , 0 Nullable 
 * Hourly_Rate , N ,  6 , 2 Nullable 
 * Sales       , N , 10 , 0 Nullable 
 * Commission  , N , 10 , 2 Nullable 
 * 
 * An employee receives only one type of pay. 
 * 
 */ 

// open employees table 
USE Employees 

/* 
 * Data is as following: 
 * 
 * Emp_ID Name         Salary     Hourly_Rate   Sales     Commission 
 * 1      Scotty       110000.00  NIL           NIL       NIL 
 * 2      Captian Kirk NIL        90.00         NIL       NIL 
 * 3      Spock        NIL        NIL           42        3800.00 
 */ 

// evaluate the QOut() code block for each record 
// of the Employees table. 
DbEval( {|| QOut( Emp_Id, ; 
                  Name,   ; 
                  Coalesce( Salary, Hourly_Rate * 40 * 52 , Sales * Commission)} ) 

/* 
 * The resulting output is: 
 * 
 * 1   Scotty        110000.00 
 * 2   Captian Kirk  187200.00 
 * 3   Mr. Spock     159600.00 
 */

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Function Coalesce() Foundation