Error --> Can someone explain (to a newbie) how to join outside of a sub-query within a select statement?

Question

Code:

select
 cal.fiscal_week, 
 h.role_type, 
 h.se,
 sub.*
from (
  select
    ds_denali__call_disposition__c as disposition, 
    datepart(hour, dateadd(hour, -5, ds_denali__call_started__c)) as call_time,
    datepart(dw, ds_denali__call_started__c) as day_of_week,
    ds_denali__owner__c as owner
  from homer.sfdc.ds_denali__dialsource_action__c 
  where ds_denali__call_disposition__c in ('No Contact', 'Voicemail', 'Internal', 'Progression - Decs Mkr', 'Progression - Other', 'No Prog - Decs Mkr', 'Decision Positive', 'Decision Negative', 'No Prog - Other')
  and trunc(ds_denali__call_started__c) >= 2018-11-01) sub
left join homer.homer_mapping.insidesales_hierarchy h on ds.ds_denali__owner__c = h.sfdc_id
left join homer.homer_mapping.adp_fiscal_calendar cal on trunc(ds.ds_denali__call_started__c) = cal.day_date

I need to join everything, but the 'homer.sfdc.ds_denali__dialsource_action_c' is a monster table, so I'm trying to cut down as much time as I can by sub-querying. But I still need to join it to two other tables we have (listed in the left join). The error I'm running into is that it keeps telling me that 'ds.' doesn't exist. I'm just unsure if this is a foundational piece that I'm not familiar with since I'm self-taught and pretty new. Any and all help is appreciated.

Answer 1

You dont seem to understand aliasing. I presume the column ds.ds_denali__owner__c is meant to reference the column of the homer.sfdc.ds_denali__dialsource_action__c table in your subquery. At the moment it is looking for a table called ds in default database / schema hence the error you are seeing.

Next you dont seem to understand subqueries properly. When you write a subquery you are essentially creating a new table that can then be used elsewhere in your query. in this case the table you made is aliased (named) sub and contains 4 columns. (disposition, call_time, day_of_week & owner)

Putting these together this

left join homer.homer_mapping.insidesales_hierarchy h on ds.ds_denali__owner__c = h.sfdc_id

should become

left join homer.homer_mapping.insidesales_hierarchy h on sub.owner = h.sfdc_id

and to fix

left join homer.homer_mapping.adp_fiscal_calendar cal on trunc(ds.ds_denali__call_started__c) = cal.day_date

you first would need to add ds_denali__call_started__c to your subquery.

As a final point it would suprise me greatly if your subquery actually spead things up. i would fully expect it to slow things down compared to joining directly to homer.sfdc.ds_denali__dialsource_action__c