I am trying to do a decode against an Oracle server and return a fiscal year if date ranges fall in between what is listed below. I keep getting 'ORA-00907: missing right parenthesis' and I am sure it is something simple or maybe I am not using correct syntax for the date range but any help would be appreciated. Thanks!
SELECT ISSUE_DT,
MAX(DECODE(ISSUE_DT >= '01-JUL-11' AND ISSUE_DT <= '30-JUN-12','2012',ISSUE_DT >= '01-JUL-12' AND ISSUE_DT <= '30-JUN-13','2013',ISSUE_DT >= '01-JUL-13' AND ISSUE_DT <= '30-JUN-14','2014','NONE'))FISCAL_YEAR
FROM PS_GSU_AWD_INFO_VW
UPDATE
This is my current SQL statement that returns two columns, the year with quarter and total amount. I ultimately need the quarter and fiscal year in two separate places:
select CASE
WHEN ISSUE_DT >= '01-JUL-11' AND ISSUE_DT <= '30-SEP-11' THEN 'FY12 Q1'
WHEN ISSUE_DT >= '01-OCT-11' AND ISSUE_DT <= '31-DEC-11' THEN 'FY12 Q2'
WHEN ISSUE_DT >= '01-JAN-12' AND ISSUE_DT <= '31-MAR-12' THEN 'FY12 Q3'
WHEN ISSUE_DT >= '01-APR-12' AND ISSUE_DT <= '30-JUN-12' THEN 'FY12 Q4'
WHEN ISSUE_DT >= '01-JUL-12' AND ISSUE_DT <= '30-SEP-12' THEN 'FY13 Q1'
WHEN ISSUE_DT >= '01-OCT-12' AND ISSUE_DT <= '31-DEC-12' THEN 'FY13 Q2'
WHEN ISSUE_DT >= '01-JAN-13' AND ISSUE_DT <= '31-MAR-13' THEN 'FY13 Q3'
WHEN ISSUE_DT >= '01-APR-13' AND ISSUE_DT <= '30-JUN-13' THEN 'FY13 Q4'
WHEN ISSUE_DT >= '01-JUL-13' AND ISSUE_DT <= '30-SEP-13' THEN 'FY14 Q1'
WHEN ISSUE_DT >= '01-OCT-13' AND ISSUE_DT <= '31-DEC-13' THEN 'FY14 Q2'
WHEN ISSUE_DT >= '01-JAN-14' AND ISSUE_DT <= '31-MAR-14' THEN 'FY14 Q3'
WHEN ISSUE_DT >= '01-APR-14' AND ISSUE_DT <= '30-JUN-14' THEN 'FY14 Q4'
ELSE 'NO DATA' END AS FISCAL_QUARTER, SUM(AMOUNT) AS TOTAL_AWARDED FROM PS_GSU_AWD_INFO_VW WHERE ISSUE_DT >= '01-JUL-11'
GROUP BY CASE WHEN ISSUE_DT >= '01-JUL-11' AND ISSUE_DT <= '30-SEP-11' THEN 'FY12 Q1'
WHEN ISSUE_DT >= '01-OCT-11' AND ISSUE_DT <= '31-DEC-11' THEN 'FY12 Q2'
WHEN ISSUE_DT >= '01-JAN-12' AND ISSUE_DT <= '31-MAR-12' THEN 'FY12 Q3'
WHEN ISSUE_DT >= '01-APR-12' AND ISSUE_DT <= '30-JUN-12' THEN 'FY12 Q4'
WHEN ISSUE_DT >= '01-JUL-12' AND ISSUE_DT <= '30-SEP-12' THEN 'FY13 Q1'
WHEN ISSUE_DT >= '01-OCT-12' AND ISSUE_DT <= '31-DEC-12' THEN 'FY13 Q2'
WHEN ISSUE_DT >= '01-JAN-13' AND ISSUE_DT <= '31-MAR-13' THEN 'FY13 Q3'
WHEN ISSUE_DT >= '01-APR-13' AND ISSUE_DT <= '30-JUN-13' THEN 'FY13 Q4'
WHEN ISSUE_DT >= '01-JUL-13' AND ISSUE_DT <= '30-SEP-13' THEN 'FY14 Q1'
WHEN ISSUE_DT >= '01-OCT-13' AND ISSUE_DT <= '31-DEC-13' THEN 'FY14 Q2'
WHEN ISSUE_DT >= '01-JAN-14' AND ISSUE_DT <= '31-MAR-14' THEN 'FY14 Q3'
WHEN ISSUE_DT >= '01-APR-14' AND ISSUE_DT <= '30-JUN-14' THEN 'FY14 Q4'
ELSE 'NO DATA' END ORDER BY FISCAL_QUARTER
I figured out a query that works. Not sure if this is best practices or not but here my is code:
SELECT DECODE(FISCAL_QUARTER,'FY12 Q1',2012,'FY12 Q2',2012,'FY12 Q3',2012,
'FY12 Q4',2012,'FY13 Q1',2013,'FY13 Q2',2013,'FY13 Q3',2013,'FY13 Q4',2013,'FY14
Q1',2014,'FY14 Q2',2014,'FY14 Q3',2014,'FY14 Q4',2014,2015)as "FISCAL YEAR",
DECODE(FISCAL_QUARTER,'FY12 Q1','Q1','FY12 Q2','Q2','FY12 Q3','Q3','FY12 Q4'
,'Q4','FY13 Q1','Q1','FY13 Q2','Q2','FY13 Q3','Q3','FY13 Q4','Q4','FY14 Q1','Q1',
'FY14 Q2','Q2','FY14 Q3','Q3','FY14 Q4','Q4',2015)as "FISCAL QUARTER",
DECODE(FISCAL_QUARTER,'FY12 Q1',TO_CHAR(TOTAL_AWARDED),'FY12 Q2',TO_CHAR(TOTAL_AWARDED),'FY12 Q3',TO_CHAR(TOTAL_AWARDED),'FY12 Q4',TO_CHAR(TOTAL_AWARDED),'FY13 Q1',TO_CHAR(TOTAL_AWARDED),'FY13 Q2',TO_CHAR(TOTAL_AWARDED),'FY13 Q3',TO_CHAR(TOTAL_AWARDED),'FY13 Q4',TO_CHAR(TOTAL_AWARDED),'FY14 Q1',TO_CHAR(TOTAL_AWARDED),'FY14 Q2',TO_CHAR(TOTAL_AWARDED),'FY14 Q3',TO_CHAR(TOTAL_AWARDED),'FY14 Q4',TO_CHAR(TOTAL_AWARDED),'NONE')as "TOTAL AWARDED"
FROM(
select CASE
WHEN ISSUE_DT >= '01-JUL-11' AND ISSUE_DT <= '30-SEP-11' THEN 'FY12 Q1'
WHEN ISSUE_DT >= '01-OCT-11' AND ISSUE_DT <= '31-DEC-11' THEN 'FY12 Q2'
WHEN ISSUE_DT >= '01-JAN-12' AND ISSUE_DT <= '31-MAR-12' THEN 'FY12 Q3'
WHEN ISSUE_DT >= '01-APR-12' AND ISSUE_DT <= '30-JUN-12' THEN 'FY12 Q4'
WHEN ISSUE_DT >= '01-JUL-12' AND ISSUE_DT <= '30-SEP-12' THEN 'FY13 Q1'
WHEN ISSUE_DT >= '01-OCT-12' AND ISSUE_DT <= '31-DEC-12' THEN 'FY13 Q2'
WHEN ISSUE_DT >= '01-JAN-13' AND ISSUE_DT <= '31-MAR-13' THEN 'FY13 Q3'
WHEN ISSUE_DT >= '01-APR-13' AND ISSUE_DT <= '30-JUN-13' THEN 'FY13 Q4'
WHEN ISSUE_DT >= '01-JUL-13' AND ISSUE_DT <= '30-SEP-13' THEN 'FY14 Q1'
WHEN ISSUE_DT >= '01-OCT-13' AND ISSUE_DT <= '31-DEC-13' THEN 'FY14 Q2'
WHEN ISSUE_DT >= '01-JAN-14' AND ISSUE_DT <= '31-MAR-14' THEN 'FY14 Q3'
WHEN ISSUE_DT >= '01-APR-14' AND ISSUE_DT <= '30-JUN-14' THEN 'FY14 Q4'
ELSE 'NO DATA' END AS FISCAL_QUARTER, SUM(AMOUNT) AS TOTAL_AWARDED FROM PS_GSU_AWD_INFO_VW WHERE ISSUE_DT >= '01-JUL-11'
GROUP BY CASE WHEN ISSUE_DT >= '01-JUL-11' AND ISSUE_DT <= '30-SEP-11' THEN 'FY12 Q1'
WHEN ISSUE_DT >= '01-OCT-11' AND ISSUE_DT <= '31-DEC-11' THEN 'FY12 Q2'
WHEN ISSUE_DT >= '01-JAN-12' AND ISSUE_DT <= '31-MAR-12' THEN 'FY12 Q3'
WHEN ISSUE_DT >= '01-APR-12' AND ISSUE_DT <= '30-JUN-12' THEN 'FY12 Q4'
WHEN ISSUE_DT >= '01-JUL-12' AND ISSUE_DT <= '30-SEP-12' THEN 'FY13 Q1'
WHEN ISSUE_DT >= '01-OCT-12' AND ISSUE_DT <= '31-DEC-12' THEN 'FY13 Q2'
WHEN ISSUE_DT >= '01-JAN-13' AND ISSUE_DT <= '31-MAR-13' THEN 'FY13 Q3'
WHEN ISSUE_DT >= '01-APR-13' AND ISSUE_DT <= '30-JUN-13' THEN 'FY13 Q4'
WHEN ISSUE_DT >= '01-JUL-13' AND ISSUE_DT <= '30-SEP-13' THEN 'FY14 Q1'
WHEN ISSUE_DT >= '01-OCT-13' AND ISSUE_DT <= '31-DEC-13' THEN 'FY14 Q2'
WHEN ISSUE_DT >= '01-JAN-14' AND ISSUE_DT <= '31-MAR-14' THEN 'FY14 Q3'
WHEN ISSUE_DT >= '01-APR-14' AND ISSUE_DT <= '30-JUN-14' THEN 'FY14 Q4'
ELSE 'NO DATA' END ORDER BY FISCAL_QUARTER)