Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am trying to do a case expression with mixed data types with no avail. Please could I get some help with what I'm doing wrong, please see my statement below
Case
when c.student_age > 19
and c.count_forms > 3
and ss.value :: date <@ fiscal_year.f_year
then "Y"
else "N"
end as student_r_year
I'm using both int and date data in the first part of the case statement and that's where the issue is.
I could not find the definition of <@, but from your description, I assume that you mean that ss.value is the same year as fiscal_year.f_year:
Case
when c.student_age > 19
and c.count_forms > 3
and ss.value :: date BETWEEN (fiscal_year.f_year::varchar || '-01-01')::date
AND (fiscal_year.f_year::varchar || '-12-31')::date
then 'Y'
else 'N'
end as student_r_year
An alternative is to extract the year from ss.value:
Case
when c.student_age > 19
and c.count_forms > 3
and extract(year from ss.value :: date) = fiscal_year.f_year
then 'Y'
else 'N'
end as student_r_year
@> is the "contains" operator postgresql.org/docs/current/functions-range.html
– a_horse_with_no_name
May 2 '19 at 11:30
THENand theELSE- not those used to evaluate the expression. What is the error you get? Do you really have a column named"Y"- or do you want to return a string constant?'Y'is a string constant,"Y"is an identifier. – a_horse_with_no_name May 2 '19 at 10:24ss.value::date < fiscal_year.f_year? What data type isss.value. And as I have already mentioned"Y"is not a string constant. Those need single quotes, e.g.'Y'– a_horse_with_no_name May 2 '19 at 10:58