topic Re: How to Use the CharIndex with Databricks SQL in Data Engineering

How to Use the CharIndex with Databricks SQL

Carlton — Thu, 13 Oct 2022 16:00:08 GMT

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?

Re: How to Use the CharIndex with Databricks SQL

-werners- — Fri, 14 Oct 2022 08:14:41 GMT

cross apply is not a function in databricks sql.

Re: How to Use the CharIndex with Databricks SQL

Carlton — Fri, 14 Oct 2022 08:26:47 GMT

Hi Werners,

Thanks for reaching out

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

Re: How to Use the CharIndex with Databricks SQL

-werners- — Fri, 14 Oct 2022 08:32:27 GMT

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

Re: How to Use the CharIndex with Databricks SQL

Carlton — Fri, 14 Oct 2022 09:17:02 GMT

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

Re: How to Use the CharIndex with Databricks SQL

-werners- — Fri, 14 Oct 2022 09:19:19 GMT

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

Re: How to Use the CharIndex with Databricks SQL

Carlton — Fri, 14 Oct 2022 09:28:39 GMT

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.

Re: How to Use the CharIndex with Databricks SQL

-werners- — Fri, 14 Oct 2022 09:38:02 GMT

it seems you use IIF instead of IFF.

Re: How to Use the CharIndex with Databricks SQL

Carlton — Fri, 14 Oct 2022 10:12:33 GMT

You're correct Werners. Thank you very much.