CODEWITHSUNDEEP
snowflake-cloud-data-platform
aks85
aks85

Reputation: 805

Snowflake - SQL compilation error: Missing column specification

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

Upvotes: 15

Views: 47891

Answers (2)

Dhruv
Dhruv

Reputation: 51

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

Upvotes: 4

aks85
aks85

Reputation: 805

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

Upvotes: 35

Related Questions