Filter Period depending on CurrentMonth

Question

I am trying to create an Analysis. I am trying to use SQL Expression and using Below Query but this does not work. My requirement is to filter Period.So if Current Month is Jul then Filter should be on Jul if its Aug then filter should be on Jul,Aug and If its Sep then Filter on Jul,Aug,Sep and so on. Can someone help to see why my below query does not work.

SELECT CASE CAST(VALUEOF("QFBI203:CurrentMonthAlias") AS CHAR(3)) WHEN 'Jul' THEN 'Jul' WHEN 'Aug' THEN 'Jul';'Aug'

WHEN 'Sep' THEN 'Jul';'Aug';'Sep'

WHEN 'Oct' THEN 'Jul';'Aug';'Sep';'Oct'

WHEN 'Nov' THEN 'Jul';'Aug';'Sep';'Oct';'Nov'

END From "Prompts"

Appriciate if someone can help.

Gianni Ceresa · Answer

Ok, so ... think SQL

In a IN condition (https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm ) you must have an expression list (a "static" list of values separated by " , " ) or a subquery.

In your case you mixed them.

The subquery return values in rows, so if you want your month to be "Jul", "Aug", "Sep" it must be 3 rows containing each one of the 3 values.

You can't really use a CASE WHEN to return the expression list (not covered by the documentation and doesn't seem to work in any possible way).

So this must give you already the answer on how to make your code working.

But if we think at what @asim cholas first posted and the idea I was trying to express, using a proper time dimension with the various columns to allow you to make these operations. If your repository variable would be a number representing the number instead of the name (or even a date) you could use BETWEEN ... AND ... or >= AND <= to easily get what you try to do.

And there you can easily use a CASE WHEN as one of the values of the BETWEEN or <=.

User_KJI1O · Answer

Sorry if I was not clear. The select query I am using is actually on Filter of type SQL Expression. Also my Months are coming from Period dimension I am not using text values. But the selection of the Period.Month Filter I want to use is dynamic and hence I am using SQL Expression. Screenshot attached below. I need a way to select months depending on the value of currentmonthAlias Repositiory variable. Hope this clarifies.

cesar.advincula.o · Answer

Not sure what you want,  Gianni is right, you should use the time dimension column and not deal with textual months. Also the syntax its not right you are using ";" to give two values. I am not sure if you could give more than two values for CASE statement. If you describe in more detail, could be there a better path to solve your question.

However, if you REALLY BUT REALLY, want to do that:

-Make a SQL query with the logic of your CASE STATEMENT,  this is a sample of the query is in Oracle Database:

select MONTH_DES month

from

(SELECT

ADD_MONTHS(TRUNC( sd, 'MONTH'  ), rn) time_id

,TO_CHAR(ADD_MONTHS(TRUNC( sd, 'MONTH'  ), rn),'Mon')  MONTH_DES

,TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC( sd, 'MONTH'  ), rn),'RRRRMM')) MONTH_ID

FROM

(

SELECT

TO_DATE( '12/31/2015', 'MM/DD/YYYY' ) sd,

rownum rn

FROM dual

CONNECT BY level <= 12

) )

where 1=1

and  MONTH_ID  <=  TO_NUMBER(to_char(SYSDATE,'RRRRMM') )

and  MONTH_ID  >=

(SELECT CASE

WHEN to_char(SYSDATE,'MON') = 'NOV'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -4

WHEN to_char(SYSDATE,'MON') = 'OCT'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -3

WHEN to_char(SYSDATE,'MON') = 'SEP'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -2

WHEN to_char(SYSDATE,'MON') = 'AUG'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -1

WHEN to_char(SYSDATE,'MON') = 'JUL'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -0

ELSE TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -1 END

FROM DUAL)

group by MONTH_DES

order by 1;

-Check the nls_language setting

alter session set nls_language ='ENGLISH'

- And use using row-wise repository variables,  OBIEE - (Row-wise|List of Values) server variable [Gerardnico]

WHERE TABLE.MY_COLUMN = valueof(NQ_SESSION.MY_VARIABLE_NAME)

use the variable for the filter , etc