0

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.

7
  • The data type of the expression is determined by the expressions after the THEN and the ELSE - 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:24
  • Hi, I want to return a string constant "Y" or "N" depending if the first part is true, i.e. age, count and the date is within the fiscal year. – Mavis Banda May 2 '19 at 10:40
  • The error that I get is; "ERROR operator does not exist: @ date range" – Mavis Banda May 2 '19 at 10:41
  • Did you mean to write ss.value::date < fiscal_year.f_year? What data type is ss.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
  • Hi, apologies, in my script it's written at 'Y', somebody when I'm typing I keep using double quotes. as.value is an answer column with mixed data, text, int and dates, however I already have a where clause that limits the value to the dates and this come out correctly in the output. I need the '< @' as "fiscal_year.f_year" is a date range – Mavis Banda May 2 '19 at 11:23
0

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
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.