SQL/snowflake: invalid identifier

0
votes

Trying to understand why my query is getting the error "Error running query: 000904 (42000): SQL compilation error: error line 16 at position 48 invalid identifier 'S1.V'".

Query:

with step1 as (
select
    v:_id
from 
    PUBLIC.BUSINESSES 
where 
    v:created::date between DATEADD(dd, 1, last_day(DATEADD(mm, -3, GETDATE()))) and getdate()
group by 1
),

step2 as (
select
    distinct b.v:_id
from
    step1 s1
    inner join public.businesses b on b.v:_id = s1.v:_id
where 
    b.v:businessSetting = 'enabled'
)
    
select 'Businesses signedup' as step, count(*) as count from step1
union 
select 'Enabled' as step, count(*) as count from step2
order by count desc

Is anyone able to help?

1
sqlsnowflake-cloud-data-platform
WITH step1 AS ( is missing from the start of your SQL. - Jon Armstrong
What is v:_id and v:created? - Gordon Linoff
Sorry, I should have specified! My database is replicated into a Snowflake database that creates the json variable v per object - Maandeep

1 Answers

1
votes

You do not return v from step1 but you return "V:_ID". So it should be:

with step1 as (
select
    v:_id
from 
    PUBLIC.BUSINESSES 
where 
    v:created::date between DATEADD(dd, 1, last_day(DATEADD(mm, -3, GETDATE()))) and getdate()
group by 1
),
step2 as (
select
    distinct b.v:_id
from
    step1 s1
    inner join public.businesses b on b.v:_id = s1."V:_ID"
where 
    b.v:businessSetting = 'enabled'
)
    
select 'Businesses signedup' as step, count(*) as count from step1
union 
select 'Enabled' as step, count(*) as count from step2
order by count desc;

I would recommend to define an alias for the column and use it:

with step1 as (
select
    v:_id AS ID
from 
    PUBLIC.BUSINESSES 
where 
    v:created::date between DATEADD(dd, 1, last_day(DATEADD(mm, -3, GETDATE()))) and getdate()
group by 1
),

step2 as (
select
    distinct b.v:_id
from
    step1 s1
    inner join public.businesses b on b.v:_id = s1.id
where 
    b.v:businessSetting = 'enabled'
)
select 'Businesses signedup' as step, count(*) as count from step1
union 
select 'Enabled' as step, count(*) as count from step2
order by count desc;