A Friendly Reminder: One Plus NULL Equals NULL
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 | 1.06 |
| scalpel | 5.50 | NULL | NULL | 5.50 |
| mercury_fulminate | 200.00 | NULL | 35.00 | 235.00 |
| 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.
