CASE statement in Report Studio filters

Question

I wrote the following CASE statement in a Report Studio filter (Cognos 8 running on DB2):

CASE
    WHEN (
        _WEEK_OF_YEAR(?paramDate?) > _WEEK_OF_YEAR(_ADD_MONTHS(?paramDate?; -3))
    )
    THEN (
        [BusinessView].[QueryItem].[Week] <= _WEEK_OF_YEAR(?paramDate?)
        AND [BusinessView].[QueryItem].[Week] >= _WEEK_OF_YEAR(_ADD_MONTHS(?paramDate?; -3))
        AND [BusinessView].[QueryItem].[Year] = _YEAR(?paramDate?)
    )
    ELSE (
        ([BusinessView].[QueryItem].[Week] <= _WEEK_OF_YEAR(?paramDate?)
            AND [BusinessView].[QueryItem].[Year] = _YEAR(?paramDate?))
        OR ([BusinessView].[QueryItem].[Week] >= _WEEK_OF_YEAR(_ADD_MONTHS(?paramDate?; -3))
            AND [BusinessView].[QueryItem].[Year] = _YEAR(_ADD_MONTHS(?paramDate?; -3)))
    )
END

But I get the error XQE-PLN-0312: Filter expressions must evaluate to Boolean. Notice that the expressions under both WHEN and THEN and ELSE all do evaluate to boolean. Also, such expressions all work when put individually in a filter. So I argue the problem is about the CASE statement itself. Also notice that I wrote other CASE filters in other reports and they work as I expect. Hence I suspect some trouble with date functions interacting with CASE.

I found out this answer, but it didn't fix the problem: as you can see in my code I already put all the parentheses in place.

Please don't suggest me to avoid the CASE statement and use instead AND and OR alone; this actually works, but I would like to understand how to make the CASE working as well.

Answer 1

Your CASE statement is invalid. All paths of a CASE statement must return a value. Try running your result expressions independently of the CASE statement. For example...

[BusinessView].[QueryItem].[Week] <= _WEEK_OF_YEAR(?paramDate?)
AND [BusinessView].[QueryItem].[Week] >= _WEEK_OF_YEAR(_ADD_MONTHS(?paramDate?; -3))
AND [BusinessView].[QueryItem].[Year] = _YEAR(?paramDate?)

...will result in an error.

Also, "The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion." (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017)

The result of a filter must be either true or false.

So something like this?

CASE
  WHEN A=B
    THEN 'value1'
  WHEN A=C
    THEN 'value2'
  ELSE 'value3'
END = 'value3'

Answer 2

There is no need any [if] or [case] statement. Just write filter conditions with logical operators.