Using case when in Cognos data item

I have put this in the Data Filter expression and it causes error when running the report. What I'm trying to achieve here is that when the Force Expired column has a value of Force then the action on the two other columns such as ([Policy Expiration Date] and [Policy Effective Date] should use the logic used against the current_date.

case when [Force Expired] = 'Inforce' then ([Policy Expiration Date] >= current_date and [Policy Effective Date] <= current_date) else end

2 Answers

Cognos is finicky about the exact syntax of conditionals in filters. I generally avoid CASE statements in filters for this reason. You can convert your filter to one of these two syntaxes instead:

IF ([Force Expired] = 'Inforce')
THEN ([Policy Expiration Date] >= current_date and [Policy Effective Date] <= current_date)
ELSE (1=1)
([Force Expired] = 'Inforce' AND [Policy Expiration Date] >= current_date AND [Policy Effective Date] <= current_date)
OR
[Force Expired] <> 'Inforce'

If you insist on using a CASE statement then I believe you can put parentheses around your WHEN clause and provide a result for ELSE and it should work:

CASE
WHEN ([Force Expired] = 'Inforce')
THEN ([Policy Expiration Date] >= current_date AND [Policy Effective Date] <= current_date)
ELSE (1=1)
END

For some reason, Cognos insists on an ELSE clause.

when you use case you have to specify what to do when the requirement is met - not to give another condition. something like: case when [bla] = "foo" then "return_value" else "nothing" end remember -whatever you write in a calculated vlue in a data item is translated to sql, and that's precisely how it also goes in sql...