空场上的标准(Where Criteria on empty field)

空场上的标准(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.