How to Use the CharIndex with Databricks SQL

[Carlton]

When applying the following T-SQL I don't get any errors on MS SQL Server

SELECT DISTINCT
  *
 
FROM dbo.account
LEFT OUTER JOIN dbo.crm2cburl_lookup
  ON account.Id = CRM2CBURL_Lookup.[Key]
LEFT OUTER JOIN dbo.organizations 
ON CRM2CBURL_Lookup.CB_URL_KEY = organizations.cb_url
cross apply (values (charindex('://', homepage_url))) a(a)
cross apply (values (iif(a = 0, 1, a + 3))) b(b)
cross apply (values (charindex('/', homepage_url, b))) c(c)
cross apply (values (iif(c = 0, len(homepage_url) + 1, c))) d(d)
cross apply (values (substring(homepage_url, b, d - b))) e(e)

However, when I apply the similar query (identical except for the database used) I get the error message missing 'JOIN'.

The error relates to:

cross apply (values (charindex('://', homepage_url))) a(a)

The query on Databricks is as follows:

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 apply (values (charindex('://', homepage_url))) a(a)
cross apply (values (iif(a = 0, 1, a + 3))) b(b)
cross apply (values (charindex('/', homepage_url, b))) c(c)
cross apply (values (iif(c = 0, len(homepage_url) + 1, c))) d(d)
cross apply (values (substring(homepage_url, b, d - b))) e(e)

My feeling is the problem is a syntax issue with the way Databricks deals with charindex, but I'm not sure.

Any thoughts?

[-werners-]

cross apply is not a function in databricks sql.

[Carlton]

Hi Werners,

Thanks for reaching out

Can you let me know what I could use instead of cross apply?

[-werners-]

No idea as I have no idea what cross apply does.

Maybe if you can explain what you want to achieve I can propose an alternative

[Carlton]

Werners, thanks again from getting in touch.

I managed to fix this by replacing cross apply with cross join.

However, I'm not getting the error:

Undefined function: iif. This function is neither a built-in/temporary function, nor a persistent function that is qualified as spark_catalog.default.iif.

Is there a equivalent method to iif in Databricks

[-werners-]

I think it is iff.

Here is a list with all the functions:

https://docs.databricks.com/sql/language-manual/sql-ref-functions-builtin-alpha.html

[Carlton]

In that case, its strange that I'm getting the suggesting iff isn;t supported

This function is neither a built-in/temporary function, nor a persistent function that is qualified as spark_catalog.default.iif.

[-werners- · Accepted solution]

it seems you use IIF instead of IFF.

[Carlton]

You're correct Werners. Thank you very much.