Case when condition

[cbyrne123]

Hi Please can you help  with the below code, i basically have a data item in a list view page and the below code works, however it doesn't work out the bottom two for some reason, is this because it is selecting the above <1 first?


case
when ([Cast Service Code])='6M EXAM' AND _days_between ([Service Date],CURRENT_DATE) >= 1 then 'STILL WITHIN FOR 6M EXAM'
when ([Cast Service Code])='6M EXAM' AND _days_between ([Service Date],CURRENT_DATE) <1 then 'OVER SERVICE FOR 6M EXAM'
when ([Cast Service Code])='12M INS' AND _days_between ([Service Date],CURRENT_DATE) >= 1 then 'STILL WITHIN FOR 12M INS'
when ([Cast Service Code])='12M INS' AND _days_between ([Service Date],CURRENT_DATE) <1 then 'OVER SERVICE FOR 12M INS'
when ([Cast Service Code])='TACKLE' AND _days_between ([Service Date],CURRENT_DATE) < 1 then 'OVER SERVICE FOR TACKLE'
when ([Cast Service Code])='TACKLE' AND _days_between ([Service Date],CURRENT_DATE) >= 1 then 'STILL WITHIN SERVICE FOR TACKLE'
when ([Cast Service Code])='12M INS' AND _days_between ([Service Date],CURRENT_DATE)<= +045 then 'SERVICE WITHIN 45 DAYS'
when ([Cast Service Code])='TACKLE' AND _days_between ([Service Date],CURRENT_DATE)<= +045 then 'SERVICE WITHIN 45 DAYS'
end

[blom0344]

A case is evaluated in the order of the defined WHEN parts. For each record the first WHEN that applies is used and the evaluation stops then and there.

Code Select Expand


+045


is a string , so the evaluation may not work as you expect in any case..

[cbyrne123]

Thanks for the reply, do you know of any other way i can get this to work properly? Will it work if i nest two case statements or maybe if i use case if.

Thanks in advance

Chris

[blom0344]

Code Select Expand


when ([Cast Service Code])='12M INS' AND _days_between ([Service Date],CURRENT_DATE) >= 1 then 'STILL WITHIN FOR 12M INS'
when ([Cast Service Code])='12M INS' AND _days_between ([Service Date],CURRENT_DATE) <1 then 'OVER SERVICE FOR 12M INS'


literally means that EVERY row will fall in EITHER category, provided:

1. [Cast Service Code])='12M INS'
2. [Service Date] is not a null (unknown) value

Which means the condition in red

when ([Cast Service Code])='12M INS' AND _days_between ([Service Date],CURRENT_DATE)<= 45 then 'SERVICE WITHIN 45 DAYS'


will never be evaluated and thus will never be true

when ([Cast Service Code])='12M INS' AND _days_between ([Service Date],CURRENT_DATE) between 1 and 45 then 'SERVICE WITHIN 45 DAYS'

would make sense, but you would need it in the CASE expression as the 1st condition to be evaluated