CODEWITHSUNDEEP
databricksazure-databricks
Patterson
Patterson

Reputation: 2757

Databricks AnalysisException: Column 'l' does not exist

I have a very strange occurrence with my code.

I keep on getting the error

AnalysisException: Column 'homepage_url' does not exist

However, when I do a select with cross Joins the column does actually exist. Can someone take a look at my cross joins and let me know if that is where the problem is

SELECT DISTINCT
  account.xpd_relationshipstatus AS CRM_xpd_relationshipstatus
 ,REPLACE(owneridname,'Data.Import #','') AS xxxxxxx
 ,account.ts_montaguoffice AS Montagu_Office
 ,CAST(account.ts_reminderdatesetto AS DATE) AS testname
 ,CAST(account.ts_lastdatestatuschanged AS DATE) AS YearofCRMtslastdatestatuschanged
 ,organizations.name AS nameCB
 ,organizations.homepage_url
 ,iff(e like 'www.%', e, 'www.' + e) AS website
 ,left(category_list,charindex(',',category_list  +','  )-1) AS category_CB
-- ,case when charindex(',',category_list,0) > 0 then left(category_list,charindex(',',category_list)-1) else category_list end as category_CB
 ,organizations.category_groups_list AS category_groups_CB

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

Without the cross Joins

enter image description here

Upvotes: 1

Views: 966

Answers (1)

Saideep Arikontham
Saideep Arikontham

Reputation: 6104

  • The main reason for cross join (or any join) to recognize the column when you select not when using table valued functions is that joins are used on tables only.

  • To use table valued functions, one must use cross apply or outer apply. But these are not supported in Databricks sql.

  • The following is the demo data I am using:

enter image description here

  • I tried using inner join on a table valued function using the following query and got the same error:
select d1.*,a from demo1 inner join (values(if(d1.team = 'OG',2,1))) a;

enter image description here

  • Instead, using the select query, the joins work as that is how they function:
select d1.*,a.no_of_wins from demo1 d1 inner join (select id,case team when 'OG' then 2 when 'TS' then 1 end as no_of_wins from demo1) a on d1.id=a.id;

enter image description here

  • So, the remedy for this problem is to replace all the table valued functions on which you are using joins with SELECT statements.

Upvotes: 1

Related Questions