Cognos Report Studio (case statement) - Syntax err

Question

I have a case statement in Cognos report studio in which if the date is 1st of the current month of previous year then it should fetch data for last whole month (1 to the last date) data of the previous year. I think it is a syntax error. Below is the code that I'm sharing.Thanks in advance! Please let me know in case of concerns.

case when 
[Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] = _first_of_month(_add_years(current_date,-1))
then 
[Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] 
  between
_first_of_month(_add_months(_add_years(current_date,-1),-1))
   and
_last_of_month (_add_months(_add_years(current_date,-1),-1))
end

Answer 1

Your logic is correct. However, it looks like you are using a CASE statement in a filter. Cognos is picky about CASE syntax in filters. It requires you to put both the condition and the result in parentheses. Try this:

case when 
([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] = _first_of_month(_add_years(current_date,-1)))
then 
([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD]
  between
_first_of_month(_add_months(_add_years(current_date,-1),-1))
   and
_last_of_month (_add_months(_add_years(current_date,-1),-1)))
end

Here's an alternate syntax that should also work:

extract(year,current_date) - 1 = extract(year, [Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD])
AND 
extract(month,current_date) = extract(month, [Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD])

==CORRECTION==

It's come to my attention that my answer is not quite correct even though it was accepted. Please see below for clarification.

The CASE WHEN I provided will generate an error because the condition references fluctuating values that vary by source row. When I constructed the answer I focused on the syntactical problems without evaulating the logic. In order for this type of logic to work, the condition right after CASE WHEN should reference a single value for the entire query. This can be a user-supplied parameter or a function or constant returning a single value for the entire query, such as:

(year(current_date) = 2018)

or

(?param? = 'foo')

The following is a very simple example that works in 10.2 and can be used as a template:

CASE 
WHEN (?param? = 'foo') 
THEN (extract(year,[Date]) = 2018)
ELSE (extract(year,[Date]) = 2017)
END

Similarly, my alternative logic is correct in form but not in logic because I again lifted the logic straight from the question without examination. The logic as constructed will never return true. A modified corrected version is as follows:

_first_of_month(current_date) = current_date /* Today is the first day of the month */
AND month(_add_months(current_date,-1)) = month([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD]) /* The month of the row matches the previous month */
AND year(_add_years(current_date,-1)) = year([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD]) /* The year of the row matches the previous year */

This will give you all of the previous month's data for last year if ran on the first day of any month. If you ran it in November 1, 2018, you'd get data for the entire month of October 2017. If you ran it January 1, 2018, you'd get data for the entire month of December 2016 etc.

Answer 2

For Db2-LUW v10.5, these predicates may help you build the SQL you need:

-- Date of the first-day of the current month last year:

date(year(current date - 1 year)||'-'||month(current date)||'-01')

-- Date of the last-day of the current month last year:

date(year(current date - 1 year)||'-'||month(current date+ 1 month)||'-01') - 1 day

Answer 3

Other than the possibility of erroring because it returns a NULL, your (Johnsonium's) code is equivalent to this:

[Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] = _first_of_month(_add_years(current_date,-1))
and (
    [Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] between
        _first_of_month(_add_months(_add_years(current_date,-1),-1)) and
        _last_of_month (_add_months(_add_years(current_date,-1),-1))
    )

With no ELSE clause, your original code should return TRUE or NULL. Unfortunately, it will never return TRUE. I would not assume that NULL will be handled as FALSE. If you must use the CASE statement, I would add an ELSE clause. Perhaps...

ELSE (1=2)

But since it will never return TRUE, I'm not sure what you're going for here. If you are trying to ensure your query never returns results, a filter containing...

1=2

...is much simpler.