Snowflake - SQL compilation error: Missing column specification

I have this query: 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: What do I need to do to fix that? Thanks, Alissa

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); 
1

while using any aggregate function, its needed to provide an alias to that aggregate in DBT

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobG9vZGiCeIOOrKWor5ahrqyxjKyopWWTpLqxtcuaq6KnnmKys77Oq2SmoaOotq%2BzjJympa2do3q0vMScoJ%2Bhk5bBqrvN

 Share!