空场上的标准(Where Criteria on empty field)
考虑我有一个表格计划,其中包含以下值。
planName(VARCHAR2) | validFrom(timestamp) | validTo(timestamp) --------------------------------------------------------------- planA | 20-10-2013 |我应该在'validTo'字段上使用where条件写入什么选择查询,以便即使在'validTo'中值为空也可以获取该行
例如,如果我写的查询如下: -
select * from plan where validFrom > to_Date('20-10-2013', 'DD-MM,YYYY') and validTo < to_Date('31-12-2029', 'DD-MM,YYYY')并获得结果
planName(VARCHAR2) | validFrom(timestamp) | validTo(timestamp) --------------------------------------------------------------- | |但是我也需要获取那一行。
Consider I have a table Plan with following values.
planName(VARCHAR2) | validFrom(timestamp) | validTo(timestamp) --------------------------------------------------------------- planA | 20-10-2013 |What select query should I write with a where criteria on 'validTo' field so that to obtain that row even if the value is empty in 'validTo'
for Example, if I write the query like :-
select * from plan where validFrom > to_Date('20-10-2013', 'DD-MM,YYYY') and validTo < to_Date('31-12-2029', 'DD-MM,YYYY')and obtaining result like
planName(VARCHAR2) | validFrom(timestamp) | validTo(timestamp) --------------------------------------------------------------- | |but I need to fetch that row also.
最满意答案
谢谢我得到的每一个方法,希望这也会支持其他人。
select * from plan where COALESCE(validFrom , to_date('12/10/2799', 'DD/MM/YYYY')) > to_Date('20/10/2013', 'DD/MM/YYYY') AND COALESCE(validFrom , to_date('1/01/1700', 'DD/MM/YYYY'))< to_Date('31/12/2029', 'DD/MM/YYYY');如果值为null,则COALESCE()将与下一个非空值进行比较。
COALESCE是NVL的替代方法。
Thanks every one I got the approach, hope this will support others too.
select * from plan where COALESCE(validFrom , to_date('12/10/2799', 'DD/MM/YYYY')) > to_Date('20/10/2013', 'DD/MM/YYYY') AND COALESCE(validFrom , to_date('1/01/1700', 'DD/MM/YYYY'))< to_Date('31/12/2029', 'DD/MM/YYYY');If the value is null COALESCE() will compare with the next not null value.
COALESCE is an alternate approach to NVL.
更多推荐
发布评论