Common supported SQL functions
Review commonly supported SQL functions that you can use in IBM® Envizi ESG Suite account style rules.
| Function | Description | Syntax example | Notes |
|---|---|---|---|
| COALESCE | Returns the first non-NULL value from a list of expressions. Used to evaluate a NULL entry to a default value such as 0. | C_1 = COALESCE(C_2, 0) + COALESCE(C_3, 0) | If C_2 or C_3 are NULL, then the values instead default to 0, which enables the formula to proceed without error. |
| NULLIF | Returns NULL if the two given expressions are equal; otherwise, returns the first expression. | NULLIF(C_2, 0) | If C_2 is 0, then the two values are equal and NULL is returned. If C_2 is not zero, then the value of C_2 is returned. |
| DATEDIFF | Returns the difference between two dates in the specified unit, for example, days, months, years. | DATEDIFF(DAY, Start_Period, End_Period) | Useful for prorating when you need to determine the number of days between the start and end date of a record. |
| CASE | Evaluates conditions and returns a corresponding value when the first true condition is met; otherwise, returns an optional ELSE value. | C_1 = CASE WHEN C_2 = 1 THEN C_3 * 1 WHEN C_2 = 2 THEN C_3 * 5 ELSE 0 END | Useful for varying the result of a calculation based on different values in a drop-down list, such as is used for unit conversions. |
| ROUND | Used to round a value or formula result to a specified number of decimal places. | ROUND (…,4) | Rounding calculation results to four decimal places in Envizi ESG Suite, which helps prevent data loading issues with small numbers. |
| ABS | Returns the absolute nonnegative value of a number. | ABS(C_4) | If C_4 is -10, then the result is 10. |