Whenever I apply a CROSS JOIN to my Databricks SQL query I get a message letting me know that a column does not exists, but I'm not sure if the issue is with the CROSS JOIN.
For example, the code should identify characters such as http, https, ://, / and remove those characters and add the results to a column called websiteurl without the characters aforemention. i.e.
The code is a follows:
SELECT tt.homepage_url
,websiteurl = LEFT(v1.RightString,COALESCE(NULLIF(CHARINDEX('/',v1.RightString)-1,-1),150))
FROM basecrmcbreport.organizations tt
CROSS join (VALUES(SUBSTRING(homepage_url,CHARINDEX('//',homepage_url)+2,150)))v1(RightString)
However, the above returns the following:
Error in SQL statement: AnalysisException: Column 'homepage_url' does not exist. Did you mean one of the following? []; line 4 pos 31;
'Project ['tt.homepage_url, unresolvedalias(('websiteurl = 'LEFT('v1.RightString, 'COALESCE('NULLIF(('CHARINDEX(/, 'v1.RightString) - 1), -1), 150))), None)]
+- 'Join Cross
:- SubqueryAlias tt
: +- SubqueryAlias spark_catalog.basecrmcbreport.organizations
: +- Relation basecrmcbreport.organizations[uuid#2439,name#2440,type#2441,permalink#2442,cb_url#2443,rank#2444,created_at#2445,updated_at#2446,legal_name#2447,roles#2448,domain#2449,homepage_url#2450,country_code#2451,state_code#2452,region#2453,city#2454,address#2455,postal_code#2456,status#2457,short_description#2458,category_list#2459,category_groups_list#2460,num_funding_rounds#2461,total_funding_usd#2462,... 22 more fields] parquet
+- 'SubqueryAlias v1
+- 'UnresolvedSubqueryColumnAliases [RightString]
+- 'UnresolvedInlineTable [col1], [['SUBSTRING('homepage_url, ('CHARINDEX(//, 'homepage_url) + 2), 150)]]
Any thoughts on how to fix this?