当我试图执行下面的查询时,我无法找出缺少的关键字,我想要的是如果startDate和endDate在其他返回sysdate-7数据之间存在使用。
select * from LARGE_RELATION LR where
(CASE WHEN (STARTDATE IS NOT NULL AND ENDDATE IS NOT NULL)
THEN LR.END_DT BETWEEN to_date(STARTDATE, 'yyyymmdd') and to_date(ENDDATE, 'yyyymmdd')
ELSE (LR.END_DT IS NULL OR LR.END_DT > SYSDATE - 7)
END);
ORA- 00905 :缺少关键字00905。00000 -“缺少关键字”*原因:
*操作:行错误:3列: 31
发布于 2019-10-10 18:01:36
当两个日期都为非空时,第一个条件处理
当其中一个日期为null时,第二个验证
select *
from LARGE_RELATION LR
where
( STARTDATE IS NOT NULL
AND ENDDATE IS NOT NULL
AND LR.END_DT BETWEEN to_date(STARTDATE, 'yyyymmdd')
AND to_date(ENDDATE, 'yyyymmdd')
) OR
( (STARTDATE IS NULL OR ENDDATE IS NULL)
AND (LR.END_DT IS NULL OR LR.END_DT > SYSDATE - 7)
)
发布于 2019-10-10 18:01:42
在示例中,需要在case
之后指定要检查的参数- CASE STARTDATE WHEN IS NOT NULL AND (ENDDATE IS NOT NULL)
https://stackoverflow.com/questions/58328605
复制相似问题