With the unveiling of Fivetran Transformations, you now have even more reason to perform arithmetic operations across columns within your data warehouse. Unfortunately, a case may arise where a value is missing, but its absence should not invalidate an entire record.
Suppose you have sales records from a variety of jurisdictions and product categories. Not all jurisdictions have sales taxes and some product categories come with mandatory hazardous materials fees:
item |
price |
tax |
hazmat_fee |
oat_bran |
1.00 |
0.06 |
NULL |
scalpel |
5.50 |
NULL |
NULL |
mercury_fulminate |
200.00 |
NULL |
35.00 |
cesium_iodide |
450.00 |
45.00 |
35.00 |
If you tried to calculate the total cost for each record using the following query:
SELECT item, price, tax, hazmat_fee, price + tax + hazmat_fee as total_cost FROM sales.records
Your results will look like so:
item |
price |
tax |
hazmat_fee |
total_cost |
oat_bran |
1.00 |
0.06 |
NULL |
NULL |
scalpel |
5.50 |
NULL |
NULL |
NULL |
mercury_fulminate |
200.00 |
NULL |
35.00 |
NULL |
cesium_iodide |
450.00 |
45.00 |
35.00 |
530.00 |
What you really want in this case is to default to a value of 0 whenever a NULL is encountered. You can do this using the COALESCE function, which takes the first non-NULL argument from a list):
SELECT item, price, tax, hazmat_fee, price + COALESCE(tax, 0) + COALESCE(hazmat_fee, 0) as total_cost FROM sales.records
Returning:
item |
price |
tax |
hazmat_fee |
total_cost |
oat_bran |
1.00 |
0.06 |
NULL |
NULL |
scalpel |
5.50 |
NULL |
NULL |
NULL |
mercury_fulminate |
200.00 |
NULL |
35.00 |
NULL |
cesium_iodide |
450.00 |
45.00 |
35.00 |
530.00 |
Remember - when adding across columns, one plus NULL equals NULL! This is true for subtraction, multiplication, and division, too.
Learn more about Fivetran Transformations with a personalized demo or a free trial.