Databricks中iif函数不支持报错，寻求等效替代方案

Databricks SQL不支持iif()函数，你可以用标准SQL的CASE WHEN语句作为等效替代，这是Databricks原生支持的条件判断语法。

修改后的完整SQL语句如下：

SELECT DISTINCT
  
  *

FROM basecrmcbreport.account
LEFT OUTER JOIN basecrmcbreport.CRM2CBURL_Lookup
  ON account.Id = CRM2CBURL_Lookup.Key
LEFT OUTER JOIN basecrmcbreport.organizations
  ON CRM2CBURL_Lookup.CB_URL_KEY = organizations.cb_url
cross Join (values (charindex('://', homepage_url))) a(a)
cross Join (values (CASE WHEN a = 0 THEN 1 ELSE a + 3 END)) b(b)
cross Join (values (charindex('/', homepage_url, b))) c(c)
cross Join (values (CASE WHEN c = 0 THEN len(homepage_url) + 1 ELSE c END)) d(d)
cross Join (values (substring(homepage_url, b, d - b))) e(e)

替换说明：

• 原iif(a = 0, 1, a + 3)替换为CASE WHEN a = 0 THEN 1 ELSE a + 3 END
• 原iif(c = 0, len(homepage_url) + 1, c)替换为CASE WHEN c = 0 THEN len(homepage_url) + 1 ELSE c END

内容的提问来源于stack exchange，提问作者Patterson