16

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

Improve this question

2 Answers 2

Reset to default
36

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Even a type cast expression like age::double needs to be replaced with age::double as age.
and IFNULL as well . Was in a time crunch; glad good ol' SO came through. Thanks.
4

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.