Conditional Functions
Syntax | Description | Example | Normal Query | Pushdown Query | Defined as Computed Column | Suggested as Computed Column |
---|---|---|---|---|---|---|
CASE value WHEN value1 THEN result1 WHEN valueN THEN resultN ELSE resultZ END | Simple case | CASE OPS_REGION WHEN 'Beijing' THEN 'BJ' WHEN 'Shanghai' THEN 'SH'WHEN 'Hongkong' THEN 'HK' END FROM KYLIN_SALES = HK SH BJ | ✔️ | ✔️ | ✔️ | ✔️ |
CASE WHEN condition1 THEN result1 WHEN conditionN THEN resultN ELSE resultZ END | Searched case | CASE WHEN OPS_REGION='Beijing'THEN 'BJ' WHEN OPS_REGION='Shanghai' THEN 'SH' WHEN OPS_REGION='Hongkong' THEN 'HK' END FROM KYLIN_SALES = HK SH BJ | ✔️ | ✔️ | ✔️ | ✔️ |
NULLIF(value, value) | Return NULL if the values are the same. Otherwise, return the first value. | NULLIF(5,5) = null | ✔️ | ✔️ | ✔️ | ✔️ |
COALESCE(value, value [, value ]*) | Return the first not null value. | COALESCE(NULL,NULL,5) = 5 | ✔️ | ✔️ | ✔️ | ✔️ |
IFNULL(value1, value2) | Return value2 if value1 is NULL. Otherwise, return value1. | IFNULL('kylin','apache') = 'kylin' | ✔️ | ✔️ | ✔️ | ✔️ |
ISNULL(value) | Return true if value is NULL. Otherwise, return false. | ISNULL('kylin') = false | ✔️ | ✔️ | ✔️ | ✔️ |
NVL(value1, value2) | Return value2 if value1 is NULL. Otherwise, return value1. Value1, value2 must have same data type. | NVL('kylin','apache') = 'kylin' | ✔️ | ✔️ | ✔️ | ✔️ |