oracle 如何使用日期范围作为参数来解码一行数据

lskq00tm 于 2023-02-18 发布在 Oracle

关注(0)|答案(3)|浏览(111)

我正在尝试对Oracle服务器进行解码，如果日期范围介于下面列出的日期范围之间，则返回会计年度。我一直收到'ORA-00907：缺少右括号'，我敢肯定这是一些简单的东西，或者也许我没有使用正确的语法为日期范围，但任何帮助将不胜感激。谢谢！

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

更新
这是我当前的SQL语句，它返回两列，一列是年份，另一列是季度和总额。我最终需要将季度和财政年度放在两个不同的位置：

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

oracle

来源：https://stackoverflow.com/questions/22797365/how-do-i-decode-a-row-of-data-using-a-date-range-as-my-parameter

3条答案

按热度按时间

6ie5vjzr1#

不能使用DECODE测试除相等性以外的任何东西，但是可以使用更健壮、更标准的CASE函数

CASE WHEN issue_dt >= date '2011-07-01' AND issue_dt <= date '2012-06-30'
     THEN '2012'
     WHEN issue_dt >= date '2012-07-01' AND issue_dt <= date '2013-06-30'
     THEN '2013'
     WHEN issue_dt >= date '2013-07-01' AND issue_dt <= date '2014-06-30'
     THEN '2014'
     ELSE 'NONE'
 END

不过，通过计算而不是硬编码每年的范围来实现这一点可能是有意义的

to_char( issue_dt + interval '6' month, 'yyyy' )

赞(0）回复(0）举报 2023-02-18

vwhgwdsa2#

我找到了一个有效的查询。不确定这是否是最佳实践，但这里是我的代码：

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)

赞(0）回复(0）举报 2023-02-18

busg9geu3#

有点晚了，但你可以试试这个：

SELECT 'FY'||to_char(add_months(t.issue_dt,6), 'YY')||' Q'||DECODE(FLOOR(EXTRACT(month from t.issue_dt)/4), 0, '3', 1, '4', 2, '1', 3, '2', 'E') 
FROM t

赞(0）回复(0）举报 2023-02-18

我来回答

oracle 如何使用日期范围作为参数来解码一行数据

3条答案

相关问题

热门标签

最新问答