Skip to Content
0
Former Member
Apr 02, 2019 at 06:18 PM

Syntax to get correct string as result of case statement

73 Views Last edit Nov 30, 2020 at 09:54 AM 2 rev

I have a query filter which works and looks like this:

"Fiscal Quarter Description" (dimension) "in list" (from drop down) "2019-FQ1;2018-FQ4;2019-FQ2;2019-FQ3"

--

Default query looks like this:

----

SELECT OSA_START_DATES.FISCALQUARTERDESC FROM BODATAMART.DATE_DIM OSA_START_DATES WHERE OSA_START_DATES.FISCALQUARTERDESC IN ( '2019-FQ1','2018-FQ4','2019-FQ2','2019-FQ3' )

---

Every new quarter this report has to be modified by dropping the oldest 4th quarter in the list and adding a new one, essentially it changes the quarters passed to IN statement. Trying to automate it instead.

My several attempts have failed. Here they are:

1. Placed the following query in set of parenthesis after IN:

SELECT CASE WHEN extract(MONTH from sysdate) BETWEEN 1 AND 3 THEN extract(year from sysdate) || '-FQ2; '|| extract(year from sysdate) || '-FQ1; '|| (extract(year from sysdate) - 1)||'-FQ4; '|| (extract(year from sysdate) - 1)||'-FQ3' WHEN extract(MONTH from sysdate) BETWEEN 4 AND 6 THEN extract(year from sysdate) || '-FQ3; '|| extract(year from sysdate) || '-FQ2; '|| extract(year from sysdate)||'-FQ1; '|| (extract(year from sysdate) - 1)||'-FQ4' WHEN extract(MONTH from sysdate) BETWEEN 7 AND 9 THEN extract(year from sysdate) || '-FQ4; '|| extract(year from sysdate) || '-FQ3; '|| extract(year from sysdate) ||'-FQ2; '|| extract(year from sysdate)||'-FQ1' WHEN extract(MONTH from sysdate) BETWEEN 10 AND 12 THEN (extract(year from sysdate) + 1) || '-FQ1; '|| extract(year from sysdate) || '-FQ4; '|| extract(year from sysdate)||'-FQ3; '|| extract(year from sysdate)||'-FQ2' END from dual

TOAD results of above query is: 2019-FQ3; 2019-FQ2; 2019-FQ1; 2018-FQ4

Query in BO (Business Objects) validates fine but gives no results!

2. Rewrote query to generate same string as in default query:

SELECT CASE WHEN extract(MONTH from sysdate) BETWEEN 1 AND 3 THEN ''''||extract(year from sysdate) || '-FQ2'','''|| extract(year from sysdate) || '-FQ1'','''|| (extract(year from sysdate) - 1)||'-FQ4'','''|| (extract(year from sysdate) - 1)||'-FQ3''' WHEN extract(MONTH from sysdate) BETWEEN 4 AND 6 THEN ''''||extract(year from sysdate) || '-FQ3'','''|| extract(year from sysdate) || '-FQ2'','''|| extract(year from sysdate)||'-FQ1'','''|| (extract(year from sysdate) - 1)||'-FQ4''' WHEN extract(MONTH from sysdate) BETWEEN 7 AND 9 THEN ''''||extract(year from sysdate) || '-FQ4'','''|| extract(year from sysdate) || '-FQ3'','''|| extract(year from sysdate) ||'-FQ2'','''|| extract(year from sysdate)||'-FQ1''' WHEN extract(MONTH from sysdate) BETWEEN 10 AND 12 THEN ''''||(extract(year from sysdate) + 1) || '-FQ1'','''|| extract(year from sysdate) || '-FQ4'','''|| extract(year from sysdate)||'-FQ3'','''|| extract(year from sysdate)||'-FQ2''' END AS Last_4Quarters from dual

Again, no error while validating but gives no result.

TOAD Result of this query is: '2019-FQ3','2019-FQ2','2019-FQ1','2018-FQ4'

3. If I do the following simple query without CASE statement, the report works fine (but it will not work for other dates than the current quarter:

select extract(year from sysdate) || '-FQ3' from dual union select extract(year from sysdate) || '-FQ2' from dual union select extract(year from sysdate)||'-FQ1' from dual union select (extract(year from sysdate) - 1)||'-FQ4' from dual

4. But I do it with case as follows, it gives me error at validation:

SELECT CASE WHEN extract(MONTH from sysdate) BETWEEN 1 AND 3 THEN (select extract(year from sysdate) || '-FQ2' from dual union select extract(year from sysdate) || '-FQ1' from dual union select (extract(year from sysdate) - 1)||'-FQ4' from dual union select (extract(year from sysdate) - 1)||'-FQ3' from dual) WHEN extract(MONTH from sysdate) BETWEEN 4 AND 6 THEN (select extract(year from sysdate) || '-FQ3' from dual union select extract(year from sysdate) || '-FQ2' from dual union select extract(year from sysdate)||'-FQ1' from dual union select (extract(year from sysdate) - 1)||'-FQ4' from dual) WHEN extract(MONTH from sysdate) BETWEEN 7 AND 9 THEN (select extract(year from sysdate) || '-FQ1' from dual union select extract(year from sysdate) || '-FQ2' from dual union select extract(year from sysdate) || '-FQ3' from dual union select extract(year from sysdate) || '-FQ4' from dual ) WHEN extract(MONTH from sysdate) BETWEEN 10 AND 12 THEN (select (extract(year from sysdate) + 1) || '-FQ1' from dual union select extract(year from sysdate) || '-FQ4' from dual union select extract(year from sysdate) || '-FQ3' from dual union select extract(year from sysdate) || '-FQ2' from dual) END from dual

--------

Nothing works