KB7348: What is the ’COALESCE’ function in SQL, and how does the MicroStrategy SQL Generation Engine use it?

In relational databases, the 'COALESCE' function takes multiple input values and returns the first value it encounters that is not NULL for each row in the set. The most common purpose of the function is to combine two or more columns that contain some NULLs into a single column that does not contain NULLs.

MicroStrategy SQL generation engine uses COALESCE in two principal ways: to merge attribute ID columns in full outer joins, and (in some database platforms) for metric null checking.

 

Merging attribute ID columns in FULL OUTER JOIN
 For example, there is a database with the following two tables:

TABLE1:

 

TABLE2:
 

 

The STOREs in the two tables are from the same set of stores and the two METRICs represent different information. To generate a query that returns all of the information from both of these tables, a FULL OUTER JOIN is needed. The following SQL generates such a join:

select *
from table1 a
  full outer join table2 b
    on a.store = b.store

 

 

This does return the data correctly; however, there are two separate STORE columns with some NULLs and some duplicate information. If only one of the two STORE columns were present, some STORE information would be missing.

select a.store, a.metric1, b.metric2
from table1 a
  full outer join table2 b
    on a.store = b.store

 

 

This is an invalid result, for the reason that an attribute ID column should never be null. The column for A is missing the ID 4 because that value is present only in table 2, but table2.a is never used in the select clause.

 

To combine the two STORE columns into a single column, the COALESCE function can be used:

select coalesce(a.store, b.store), a.metric1, b.metric2
from table1 a
  full outer join table2 b
    on a.store = b.store

 

 

Now the two columns have been combined into a single column.

The MicroStrategy SQL Generation Engine uses the COALESCE function to ensure correctness and consistency in full outer join results.

In the MicroStrategy Tutorial, a report with the Subcategory attribute and the Freight and Discount metrics generates the following SQL (using Microsoft SQL Server):

select a13.SUBCAT_ID SUBCAT_ID,
  sum(((a11.FREIGHT * a12.QTY_SOLD) / a11.QTY_SOLD)) WJXBFS1
into #ZZSP00
from ORDER_FACT a11
  join ORDER_DETAIL a12
    on (a11.ORDER_ID = a12.ORDER_ID)
  join LU_ITEM a13
    on (a12.ITEM_ID = a13.ITEM_ID)
group by a13.SUBCAT_ID
select a12.SUBCAT_ID SUBCAT_ID,
  sum((a11.QTY_SOLD * a11.DISCOUNT)) WJXBFS1
into #ZZSP01
from ORDER_DETAIL a11
  join LU_ITEM a12
    on (a11.ITEM_ID = a12.ITEM_ID)
group by a12.SUBCAT_ID
select pa1.SUBCAT_ID SUBCAT_ID,
  a11.SUBCAT_DESC SUBCAT_DESC,
  pa1.WJXBFS1 WJXBFS1,
  pa2.WJXBFS1 WJXBFS2
from #ZZSP00 pa1
  join #ZZSP01 pa2
    on (pa1.SUBCAT_ID = pa2.SUBCAT_ID)
  join LU_SUBCATEG a11
    on (pa1.SUBCAT_ID = a11.SUBCAT_ID)

 

If Outer Join is enabled for both metrics (creating a full outer join situation), the following SQL is generated:

select a13.SUBCAT_ID SUBCAT_ID,
sum(((a11.FREIGHT * a12.QTY_SOLD) / a11.QTY_SOLD)) WJXBFS1
into #ZZSP00
from ORDER_FACT a11
  join ORDER_DETAIL a12
    on (a11.ORDER_ID = a12.ORDER_ID)
  join LU_ITEM a13
    on (a12.ITEM_ID = a13.ITEM_ID)
group by a13.SUBCAT_ID
select a12.SUBCAT_ID SUBCAT_ID,
  sum((a11.QTY_SOLD * a11.DISCOUNT)) WJXBFS1
into #ZZSP01
from ORDER_DETAIL a11
  join LU_ITEM a12
    on (a11.ITEM_ID = a12.ITEM_ID)
group by a12.SUBCAT_ID
select coalesce(pa1.SUBCAT_ID, pa2.SUBCAT_ID) SUBCAT_ID,
  a11.SUBCAT_DESC SUBCAT_DESC,
  pa1.WJXBFS1 WJXBFS1,
  pa2.WJXBFS1 WJXBFS2
from #ZZSP00 pa1
  full outer join #ZZSP01 pa2
    on (pa1.SUBCAT_ID = pa2.SUBCAT_ID)
  join LU_SUBCATEG a11
    on (coalesce(pa1.SUBCAT_ID, pa2.SUBCAT_ID) =a11.SUBCAT_ID)

 

NOTE: The use of the COALESCE function in both the SELECT and FROM clauses of the final pass.

 

Metric null checking
 When evaluating compound metric expressions in SQL, a null value from one of the component metrics may cause the entire expression to evaluate as null -- NULL + 100 = NULL. In that case, it may be preferable to replace the NULL with 0 prior to other operations. This behavior is controlled by the VLDB Property Metrics > Null check.

Some database platforms use the expression COALESCE(metric, 0) to accomplish this. Since COALESCE returns the first argument in the list that is not null, this expression will yield the metric value if it exists, otherwise 0.

select a11.QUARTER_ID QUARTER_ID,
max(a12.QUARTER_DESC) QUARTER_DESC,
(COALESCE(sum(a11.TOT_DOLLAR_SALES), 0) - COALESCE(sum(a11.TOT_COST), 0)) WJXBFS1
from QTR_CATEGORY_SLS a11
join LU_QUARTER a12
on (a11.QUARTER_ID = a12.QUARTER_ID)
group by a11.QUARTER_ID