Case statement with multiple values as filter

[sepusa]

Hello,
I am trying to use this below filter to make the report work for both quarterly data and monthly data.

When I validate it shows no errors but when I run the report with this logic it failing with parsing error message. Can any one please help me to get this fixed.

[Year Month]  IN (CASE WHEN

to_char(current_date, 'MM') IN ('01', '04', '07', '10') then
(
to_char(_add_months(current_date, -3), 'YYYYMM'),
to_char(_add_months(current_date, -2), 'YYYYMM'),
to_char(_add_months(current_date, -1), 'YYYYMM')
)
else
to_char(_add_months(current_date, -1), 'YYYYMM') end)

Thank you in advance.

[dougp]

Please provide the error message.

[bus_pass_man]

It's probably pointing to that comma after this:

to_char(_add_months(current_date, -3), 'YYYYMM'),

This is because this isn't good syntax
to_char(_add_months(current_date, -3), 'YYYYMM'), to_char(_add_months(current_date, -2), 'YYYYMM'), to_char(_add_months(current_date, -1), 'YYYYMM')

Could you specify in English what you are trying to do? "...make the report work for both quarterly data and monthly data" doesn't say very much.  Say what you want the expression to do.  It looks like you want to determine if the month of the current date is either 1, 4, 7 or 10 and if so, return the previous 3 months, such as  202207, 202208,202209 but you might want to put brackets about that.

You might need to end up using '''+to_char(_add_months(current_date, -3), 'YYYYMM')+'''  or something similar.   

After that you might run into problems complaining about mismatched data types etc.   What's the data type of [Year Month]?  Depending on the data type you might not need to do so much mucking about with strings.  Knowing what you are trying to do could provide clues to suggest a simpler expression.

I don't know what will happen for when the current_date's month is 1, which requires the 10, 11 and 12 months of the previous year.   

Have you thought about the between function?  Something along these lines:

[Year Month]  IN (CASE WHEN

to_char(current_date, 'MM') IN ('01', '04', '07', '10') then
(
between to_char(_add_months(current_date, -3), 'YYYYMM')
and
to_char(_add_months(current_date, -1), 'YYYYMM')
)
else
to_char(_add_months(current_date, -1), 'YYYYMM') end)

There is the _month function and extract.  They might be helpful too.

[sepusa]

Hi thank you for your time for looking into the issue...
Here is the explanation of what i am trying to achieve
When the month of Current date is (01,04,07,10) I want the report to bring back data for the last three months (Prior 3 completed months)
or else I wan the report to bring data only for last completed month.
Example:
If we the run the report in
January 2022 it should return (202112, 202111, 202110)
February 2022 it should return 202201
March 2022 it should return 202202
April 2022 it should return (202201, 202202, 202203)
May 2022 it should return 202204
June 2022 it should return 202205
July 2022 it should return (202204, 202205, 202206)
August 2022 it should return 202207
September 2022 it should return 202208
October 2022 it should return (202207, 202208,202209)
November 2022 it should return 202210
December 2022 it should return 202211.

above is the logic which I am trying to find out...
The data type of YearMonth field is defined as 'String'

[MFGF]

Hi thank you for your time for looking into the issue...
Here is the explanation of what i am trying to achieve
When the month of Current date is (01,04,07,10) I want the report to bring back data for the last three months (Prior 3 completed months)
or else I wan the report to bring data only for last completed month.
Example:
If we the run the report in
January 2022 it should return (202112, 202111, 202110)
February 2022 it should return 202201
March 2022 it should return 202202
April 2022 it should return (202201, 202202, 202203)
May 2022 it should return 202204
June 2022 it should return 202205
July 2022 it should return (202204, 202205, 202206)
August 2022 it should return 202207
September 2022 it should return 202208
October 2022 it should return (202207, 202208,202209)
November 2022 it should return 202210
December 2022 it should return 202211.

above is the logic which I am trying to find out...
The data type of YearMonth field is defined as 'String'

I would take the following approach

(extract(month, current_date) in (1,4,7,10) and cast([Year Month], integer) between extract(year, _add_months(current_date,-4)) * 100 + extract(month, _add_months(current_date,-4)) and extract(year, _add_months(current_date,-1)) * 100 + extract(month, _add_months(current_date,-1)))
or
(extract(month,current_date) in (2,3,5,6,8,9,11,12) and cast([Year Month], integer) = extract(year, _add_months(current_date, -1)) * 100 + extract(month, _add_months(current_date, -1)))

Does this work for you?

Cheers!

MF.

[sepusa]

Hello,
Thank you so much for helping. The syntax you provided did worked for me with a small tweak.
Some how our system did not support cast function so need to replace that function in the syntax.
Thanks again.