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)...
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
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
