What is the error in this case...when statement?

Question

Could anyone say what is the wrong with the below statement.

Currently I'm working in cognos 10.1.1

case     
  when extract(month,_last_of_month (current_date))=1 then ('January')
  when extract(month,_last_of_month (current_date))=2 then ('January','February')  
end

I couldn't correct it.

The error code is qe-def-0459 cclexception parsing error

If I'm understanding your logic correctly, I think you mean to return only `February` in the second case, not both `January` and `February`, right? That gives an error, coz it's supposed to return just one value per `THEN`. — BeemerGuy, Jan 17 '13 at 14:53
@BeemerGuy.net If the second case is true, I wanna return both **January** and **February**. — arunpandiyarajhen, Jan 17 '13 at 15:05

score 1 · Answer 1 · answered Jan 17 '13 at 14:51

1

it should return only one value,

case
   when extract(month,_last_of_month(current_date)) = 1 then 'January'
   when extract(month,_last_of_month(current_date)) = 2 then 'January, February'
end

answered Jan 17 '13 at 14:51

John Woo

258,903
69
498
492

Thanks a lot JW, but I'd like to have FEBRUARY in a separate line. Could you help? – arunpandiyarajhen Jan 17 '13 at 14:58
@WKordos I'm using it in cognos. It is like a condition. – arunpandiyarajhen Jan 17 '13 at 15:11

score 1 · Answer 2 · answered Jan 17 '13 at 15:46

I also believe in all Case Statements in Cognos you must have an else condition as well. I don't have cognos in front of me and cant remember the syntax of the functions you are using but it looks good.

Also i remember in some versions of cognos you had to convert the eval expression to the same as the return type meaning since you are returning text instead of expr = 1 you would have to Cast(expre, varchar) = Cast(1, varchar) --- Not sure if this is the case in 10 but it definitely caused problems sometimes as recently as 8.3 and 8.4

What is the error in this case...when statement?

2 Answers2