I have this query:
create table analytics.as_screenings_activity_stopped as ( select ka.vhost, ka.SFDC_ACCOUNT_KEY, ka.count_units, min(d.go_live_date), s.RECORD_TYPE_NAME, s.vplus_stage, s.screenings_enabled_date, s.screenings_first_use_date, sd.most_recent_screen_date, case when sd.most_recent_screen_date >= dateadd(days, -90, current_date) then 'N' else 'Y' end as last_screen_90_or_more_days_ago from analytics.key_account_yn as ka left join analytics.go_live_date as d on ka.SFDC_ACCOUNT_KEY = d.SFDC_ACCOUNT_KEY left join analytics.screenings_enabled_first_use_date s on ka.SFDC_ACCOUNT_KEY = s.SFDC_ACCOUNT_KEY left join analytics.most_recent_screen_date sd on ka.vhost = sd.vhost where ka.key_account_yn = 'Y' and s.vplus_stage = 'Active' group by ka.vhost, ka.SFDC_ACCOUNT_KEY, ka.count_units, s.RECORD_TYPE_NAME, s.vplus_stage, s.screenings_enabled_date, s.screenings_first_use_date, sd.most_recent_screen_date order by ka.vhost); It runs just fine when I don't try to create a table. I'm using the same code format as I've used with other queries. I'm getting the following error:
SQL compilation error: Missing column specification What do I need to do to fix that? Thanks, Alissa
2 Answers
Problem solved - I needed to provide an alias for min(d.go_live_date). thanks to this article.
Updated query:
create table analytics.as_screenings_activity_stopped as ( select ka.vhost, ka.SFDC_ACCOUNT_KEY, ka.count_units, min(d.go_live_date) as go_live_date, s.RECORD_TYPE_NAME, s.vplus_stage, s.screenings_enabled_date, s.screenings_first_use_date, sd.most_recent_screen_date, case when sd.most_recent_screen_date >= dateadd(days, -90, current_date) then 'N' else 'Y' end as last_screen_90_or_more_days_ago from analytics.key_account_yn as ka left join analytics.go_live_date as d on ka.SFDC_ACCOUNT_KEY = d.SFDC_ACCOUNT_KEY left join analytics.screenings_enabled_first_use_date s on ka.SFDC_ACCOUNT_KEY = s.SFDC_ACCOUNT_KEY left join analytics.most_recent_screen_date sd on ka.vhost = sd.vhost where ka.key_account_yn = 'Y' group by ka.vhost, ka.SFDC_ACCOUNT_KEY, ka.count_units, s.RECORD_TYPE_NAME, s.vplus_stage, s.screenings_enabled_date, s.screenings_first_use_date, sd.most_recent_screen_date order by ka.vhost); while using any aggregate function, its needed to provide an alias to that aggregate in DBT
