CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
El Dj
El Dj

Reputation: 385

How to write a CASE statement in the WHERE clause in Snowflake

I am trying to rewrite a piece of SQL code that was initially written for Vertica. Vertica has the AGE_IN_YEARS() built-in function which was great to get someone's age. But, now, I need to find a way to have it to work in Snowflake and I did.

An equivalent statement that replaces AGE_IN_YEARS(DateOfBirth) in Snowflake can be:

case when dateadd(year, datediff(years, DateOfBirth, CURRENT_DATE), DateOfBirth) > CURRENT_DATE
then datediff(years, DateOfBirth, CURRENT_DATE) -1
else datediff(years, DateOfBirth, CURRENT_DATE)
end as AGE

The issue is when I have to use this in a WHERE clause. The original Vertica code is:

SELECT name, dob,
(SELECT AgeRangeCode
   FROM AgeRangeTable
   WHERE AGE_IN_YEARS(DateOfBirth) BETWEEN MinAge AND MaxAge) AS AgeRangeCode,
CURRENT_DATE as TODAY
from MemberInfoTable

and if i add the AGE_IN_YEARS() replacement to the above query, I get

SQL compilation error: Unsupported subquery type cannot be evaluated

How do I go about writing this case in the WHERE clause

Upvotes: 0

Views: 9123

Answers (3)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

not using a function to calculate age, just joining to the data you have like so works:

with age_range_table as (
    select * from values (0,16, 'A'),(16,18, 'B'),(18,1000, 'C') v(min_age, max_age, age_range_code)
), member_info_table as (
    select name, dob::date as dob from values ('user_80s', '1980-01-01'),('user_90s', '1990-01-01'),('user_10s', '2010-01-01') v(name, dob)
)
select m.name,
    m.dob,
    a.age_range_code
from member_info_table AS m
join age_range_table AS a
    ON m.dob < dateadd('years',-a.min_age, current_date)
    AND m.dob >= dateadd('years',-a.max_age, current_date);

giving:

NAME    DOB AGE_RANGE_CODE
user_80s    1980-01-01  C
user_90s    1990-01-01  C
user_10s    2010-01-01  A

but that is slightly gross to be doing function calls in a WHERE clause..

with age_range_table as (
    select * 
    from values 
        (0,16, 'A'),
        (16,18, 'B'),
        (18,1000, 'C') 
        v(min_age, max_age, age_range_code)
), member_info_table as (
    select name, dob::date as dob 
    from values 
       ('user_80s', '1980-01-01'),
       ('user_90s', '1990-01-01'),
       ('user_10s', '2010-01-01')
       v(name, dob)
), today_age_range_table as (
    select age_range_code
        ,dateadd('years',-min_age, current_date) as range_end_date
        ,dateadd('years',-max_age, current_date) as range_start_date
    from age_range_table
)
select m.name,
    m.dob,
    a.age_range_code
from member_info_table AS m
join today_age_range_table AS a
    ON m.dob < a.range_end_date
    AND m.dob >= a.range_start_date;

credit note: this is the same solution as Gordon's but with it explicitly shown. And the range logic changed to alter the range times once in a CTE verse altering user.dob for all rows. Based on the assumption you have more users than ranges, and if both are sub 1000 you have no problems ether.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The issue is when I have to use this in a WHERE clause.

How about adding the age to the dateofbirth instead?

where dateadd(year, minage, dateofbirth) <= current_date and
      dateadd(year, maxage + 1, dateofbirth) > current_date

Upvotes: 2

VTi
VTi

Reputation: 1319

If you have Age column in your agerange table and you want to compare each customer/participant age with that and also in the min and max range of the age range table then you can simply do it in 2 ways.

SELECT name, dob,
       [AgeRangeCode] = (SELECT AgeRangeCode FROM AgeRangeTable WHERE DATEDIFF(Year,mi.dob,CURRENT_DATE) = mi.age 
                                                         AND DATEDIFF(Year,mi.dob,CURRENT_DATE) BETWEEN MinAge AND MaxAge),
       CURRENT_DATE as TODAY
from MemberInfoTable mi

or with a simple join like below

SELECT mi.name, mi.dob, art.AgeRangeCode,
       CURRENT_DATE as TODAY
FROM  MemberInfoTable mi
JOIN  AgeRangeTable art ON art.age = DATEDIFF(Year,mi.dob,CURRENT_DATE)
WHERE DATEDIFF(Year,mi.dob,CURRENT_DATE) BETWEEN art.MinAge AND art.MaxAge

Upvotes: 0

Related Questions