Skip to main content

Using Math in Transformations

Mathematical operations in SQL allow you to perform calculations on numeric columns. This guide covers the four basic arithmetic operations: addition (+), subtraction (-), multiplication (*), and division (/).

Data Type Rules​

When performing math operations between different numeric types:

  • FLOAT * INTEGER = FLOAT
  • DECIMAL * INTEGER = DECIMAL
  • FLOAT * DECIMAL = FLOAT
  • INTEGER / INTEGER = DECIMAL
  • All operations return NULL if either input is NULL

Addition (+)​

-- Simple addition showing full reference vs column name
{{SRC}} + 100  -- Full reference
{{SRC_COL}} + 100  -- Just column name

-- Using source node name in a comment
-- Adding tax to {{SRC_NODE}} amount
{{SRC}} + ({{SRC_NODE}}."TAX_RATE" * {{SRC}})

-- Reference current node
-- Common uses
{{SRC}} + "ADJUSTMENTS"."AMOUNT"  -- Add adjustments
{{SRC}} + ("BASE"."AMOUNT" * 0.08)  -- Add 8% tax

Subtraction (-)​

-- Basic subtraction
{{TGT}} - 50

-- Subtracting columns
{{SRC}} - "ORDERS"."DISCOUNT_AMOUNT"

-- Calculate differences
{{SRC}} - "PREVIOUS"."BALANCE"  -- Change in balance
{{SRC}} - "COSTS"."AMOUNT"  -- Profit calculation

-- Common uses
{{SRC}} - ("ORDERS"."AMOUNT" * "DISCOUNTS"."RATE")  -- Apply discount
{{SRC}} - "RETURNS"."AMOUNT"  -- Subtract returns

Multiplication (*)​

-- Basic multiplication
{{SRC}} * 2

-- Multiplying columns
{{SRC}} * "ORDERS"."QUANTITY"

-- Common uses
{{SRC}} * -1  -- Negate amounts
{{SRC}} * 0.453592  -- Convert pounds to kilograms
{{SRC}} * (1 + "PRICING"."MARKUP_PCT")  -- Apply markup

Division (/)​

-- Basic division
{{SRC}} / 100  -- Convert to hundreds
{{SRC}} / 12  -- Monthly average

-- Dividing columns
{{SRC}} / "SALES"."TOTAL_UNITS"  -- Per unit calculation

-- Common uses
{{SRC}} / NULLIF("TOTALS"."COUNT", 0)  -- Safe division with NULLIF
{{SRC}} / 1000000  -- Convert to millions

Controlling Precision​

When using calculations, you might need to control the number of decimal places in the results. For example, you might want exactly two decimal places for currency or four decimal places for percentages. SQL provides two main functions to help: ROUND() for simple decimal control, and CAST() for more precise data type formatting.

ROUND​

ROUND() lets you specify how many decimal places you want to keep in a number.

ROUND(number, decimal_places)

number: The number you want to round. decimal_places: How many decimal places to keep.

-- Round to 2 decimal places
ROUND({{SRC}} * "ORDERS"."QUANTITY", 2)

-- Round to whole numbers
ROUND({{SRC}} / 12, 0)

-- Round percentage calculations
ROUND({{SRC}} / "TOTALS"."AMOUNT", 4)  -- 4 places for percentages

CAST​

CAST() converts a number into a specific data type and format. This is helpful when you need to:

  • Control both the total number of digits and decimal places.
  • Ensure consistent data types in your calculations.
  • Convert between different number formats.
CAST(number AS data_type(total_digits, decimal_places))
  • number: The value you want to convert.
  • data_type: The type of number you want.
  • total_digits: The total number of digits to allow.
  • decimal_places: How many digits after the decimal point.

For example, DECIMAL(18,2) means:

  • Up to 18 total digits
  • 2 decimal places
-- Cast as decimal with 2 decimal places
CAST({{SRC}} + O_TOTALPRICE AS DECIMAL(18,2))

-- Cast as integer
CAST({{SRC}} / 100 AS INTEGER)

-- Cast with specific precision
CAST({{SRC}} * "RATES"."MULTIPLIER" AS DECIMAL(38,6))

Using Math in Coalesce​

When using math operations in transforms and bulk editing, you can use Helper Tokens:

{{SRC}} * 1.08  -- Add 8% to source value
{{TGT}} / 100   -- Convert target to percentage

Examples using Helper Tokens:

-- Basic calculations
{{SRC}} + 100  -- Add flat amount
{{SRC}} * 2    -- Double the value

-- Combining with other columns
{{SRC}} * "TAX_RATE"

Best Practices​

  • Use parentheses to make order of operations clear.
  • Handle division by zero with NULLIF().
  • Consider precision needs when choosing between ROUND() and CAST().
  • Use appropriate scale for financial calculations, such as DECIMAL(38,6).
  • Test with edge cases (very large numbers, very small numbers, negative numbers).