避免无效的一种功能

Question

我有这个疑问

select least(                
    (select min(full_date) from lopes.vehicle_data), 
    (select min(bithday) from lopes.ad_trf_day), 
    (select min(bithday) from lopes.ad_day_rec), 
    (select min(bithday) from lopes.ad_day_rec), 
    (select min(bithday) from lopes.ad_day), 
    (select min(bithday) from lopes.ad_poi_day)) 
from dual 

但它返回NULL。有没有办法避免空值？

Answer 1

一种选择是避免使用LEAST：

SELECT MIN(dt) 
FROM (
    SELECT full_date FROM lopes.vehicle_data UNION ALL 
    SELECT bithday FROM lopes.ad_trf_day UNION ALL 
    SELECT bithday FROM lopes.ad_day_rec UNION ALL 
    SELECT bithday FROM lopes.ad_day_rec UNION ALL 
    SELECT bithday FROM lopes.ad_day UNION ALL 
    SELECT bithday FROM lopes.ad_poi_day 
) 

这仍然会返回NULL，但只有当所有的表都是空的，或者只包含NULL值。

另一种是用COALESCE，NVL或CASE：

select least(                
     (select COALESCE(min(full_date), DATE '9999-12-31') from lopes.vehicle_data), 
     (select COALESCE(min(bithday), DATE '9999-12-31') from lopes.ad_trf_day), 
     (select COALESCE(min(bithday), DATE '9999-12-31') from lopes.ad_day_rec), 
     (select COALESCE(min(bithday), DATE '9999-12-31') from lopes.ad_day_rec), 
     (select COALESCE(min(bithday), DATE '9999-12-31') from lopes.ad_day), 
     (select COALESCE(min(bithday), DATE '9999-12-31') from lopes.ad_poi_day) 
     ) 
from dual 

这将永远不会返回NULL，但如果所有的表是空的，或者只包含NULL值将返回“神奇”的价值DATE '9999-12-31'。