How to Refactor T-SQL Code to Databricks SQL Undefined function: 'IIF'

Question

When executing the following T-SQL code with Databricks SQL I get the error:

Undefined function: 'IIF'.

,ISNULL(RH.Exchange, ISNULL(IIF(SIB.Rate = 0, 1, CASE .....

This is because Databricks doesn't recognize IIF. Can someone let me know the equivalent with Databricks SQL?

Answer 1

In databricks SQL , you can use iff function to acheive the same requirement of T-SQL's iif function.

Syntax for iff function:

iff(cond, expr1, expr2)

here,

cond: a Boolean expression, expr1: any expression which needs to be executed when the cond is true, expr2: any expression which needs to be executed when the cond is false.

Reference: https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/iff