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.
Start for free
Join the thousands of companies using Fivetran to centralize and transform their data.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.