Databricks SQL mismatch issue with T-SQL

Question

When I appy the following query using T-SQL on MS Server I don't have any problems:

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 (identical except for the database used) I get the error message missing 'JOIN' at 'apply'(line 14, pos 6) line 14 refers 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 the problem is a syntax error with 'charindex', but I'm not sure.

Any thoughts?

Answer 1

I have use cross apply for demo employee and department tables I have created and I have got the same error.

missing ‘JOIN’ at ‘APPLY’

most queries which employ CROSS APPLY can be rewritten using an INNER JOIN as both of them yield same output.

select * from department d inner join employee e on d.dept_id=e.dep_id;