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
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.
+045
is a string , so the evaluation may not work as you expect in any case..
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
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