How to Resolve Missing Join issue

Carlton Patterson 741 Reputation points
2022-10-13T13:24:46.277+00:00

I recieved assistance by a community member called Niko with the following query, however I am getting error missing 'JOIN' at cross apply (values (charindex('://', homepage_url))) a(a)

The problems appears to specifically located at 'apply'

Can someone let me know what might be the problem?

The query is

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)  

Sample data is as follows:

CREATE TABLE organizations (  
    name nvarchar(100))  
  
INSERT organizations VALUES  
(N'Learner-Centered Collaborative'),  
(N'Filesolve'),  
(N'BrainChild Nutritionals')  
  
SELECT * FROM organizations  
  
  
CREATE TABLE crm2cburl_lookup (  
    Key varchar(50),  
    CB_URL_KEY varchar(150),  
    Index int,  
    CB bit)  
  
INSERT crm2cburl_lookup VALUES  
('0005c2e6-8b1d-e911-a982-00224800ce20','https://www.crunchbase.com/organization/jsquare',1,CONVERT(bit, 'True')),  
('001066b4-e17a-e811-a95c-00224800c9ff','https://www.crunchbase.com/organization/cams-consulting',1,CONVERT(bit, 'True')),  
('001166b4-e17a-e811-a95c-00224800c9ff','https://www.crunchbase.com/organization/mirae-asset-global-investments',1,CONVERT(bit, 'True')),  
('001266b4-e17a-e811-a95c-00224800c9ff','https://www.crunchbase.com/organization/dgm',1,CONVERT(bit, 'True'))  
  
SELECT * FROM crm2cburl_lookup  
  
  
CREATE TABLE account (  
    mpe_flowlastrunoutcome varchar(50))  
  
INSERT account VALUES  
('Succeeded'),  
('Succeeded'),  
('Succeeded'),  
('Succeeded'),  
('Succeeded'),  
('Succeeded'),  
('Succeeded')  
  
SELECT * FROM account  

Any thoughts greatly welcomed

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Carlton Patterson 741 Reputation points
    2022-10-13T16:54:01.983+00:00

    I'm going to delete this question, as the problem appears to be platform specific. ie I am attempting to apply a query designed to be executed on SQL Server using T-SQL on a platform called databricks which uses its own flavour of SQL called Databricks SQL. As such, I think the problem is a syntax issue.

    Thanks anyway