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?

>Solution :

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

Syntax for iif 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