Conditional functions - e6data Docs

ReferenceApplies to: Unified engine deployments

Conditional expressions choose or normalize values. Their result branches must resolve to compatible types. Explicit casts are useful when mixing numeric, text, or temporal arguments.

Function or expression	Returns	Null behavior
`coalesce(a, b, ...)`	Common argument type	First non-null argument; NULL if all are null
`nvl(a,b)`, `ifnull(a,b)`	Common argument type	Alias of coalesce
`nullif(a,b)`	Type compatible with a	NULL if a equals b; otherwise a
`greatest(a,b,...)`	Common argument type	Greatest comparable value under the resolved function’s null rules
`least(a,b,...)`	Common argument type	Least comparable value under the resolved function’s null rules
`CASE WHEN condition THEN a ELSE b END`	Common branch type	A WHEN branch is chosen only when its condition is TRUE

Missing labels

SELECT i_item_sk,
       COALESCE(i_category, 'Unknown') AS category,
       NULLIF(i_category, 'Books') AS category_except_books,
       CASE WHEN i_current_price IS NULL THEN 'Missing'
            WHEN i_current_price < 20 THEN 'Budget'
            ELSE 'Standard' END AS price_band
FROM demo.tpcds.item
ORDER BY i_item_sk
LIMIT 10;

Bounding a value

Use values of the intended common type. This example compares prices as DOUBLE.

SELECT i_item_sk,
       GREATEST(CAST(i_current_price AS DOUBLE), 10.0) AS minimum_ten,
       LEAST(CAST(i_current_price AS DOUBLE), 50.0) AS maximum_fifty
FROM demo.tpcds.item
WHERE i_current_price IS NOT NULL
ORDER BY i_item_sk
LIMIT 5;

COALESCE can normalize a missing grouping label, but replacing NULL with a real business key can change join semantics. Keep missing-value presentation separate from matching keys when that distinction matters.

Last modified on November 4, 2026