If you are unable to create a new account, please email bspsoftware@techdata.com

Author Topic: Case statement with multiple values as filter  (Read 615 times)

Offline sepusa

  • Full Member
  • ***
  • Join Date: Aug 2020
  • Posts: 8
  • Forum Citizenship: +0/-0
Case statement with multiple values as filter
« on: 08 Sep 2022 02:54:05 pm »
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.

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 798
  • Forum Citizenship: +30/-1
Re: Case statement with multiple values as filter
« Reply #1 on: 08 Sep 2022 05:01:14 pm »
Please provide the error message.

Offline bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 528
  • Forum Citizenship: +47/-0
Re: Case statement with multiple values as filter
« Reply #2 on: 08 Sep 2022 06:53:05 pm »
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.

Offline sepusa

  • Full Member
  • ***
  • Join Date: Aug 2020
  • Posts: 8
  • Forum Citizenship: +0/-0
Re: Case statement with multiple values as filter
« Reply #3 on: 15 Sep 2022 10:33:39 am »
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'



Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,544
  • Forum Citizenship: +673/-10
  • Cognos Software Muppet
Re: Case statement with multiple values as filter
« Reply #4 on: 15 Sep 2022 11:18:49 am »
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.
Meep!

Offline sepusa

  • Full Member
  • ***
  • Join Date: Aug 2020
  • Posts: 8
  • Forum Citizenship: +0/-0
Re: Case statement with multiple values as filter
« Reply #5 on: 22 Sep 2022 11:56:34 am »
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.