sql - if date A is missing use date B, if date B is missing use date C Oracle

Question

I need to use date in my filter - calc_date > '2001-01-01', but the this calc_date should first check a_date and if its null then use b_date and if its null use c_date.

I wanted to use NVL() but I think thats only limited to 2 dates. I am using CASE statement but not sure if this is the best way. Can you please let me know if other ways to do this? So from the below query I need to see ID' 3, 5, 6 and 8 since the calc_date > '2001-01-01'. This works but looking for something more efficient. On another note, not sure why I get back ID of '1' when that date is '2001-01-01' and my filter has calc_date > '2001-01-01'. Shouldn't this not show up? Thanks SQL BELOW:

with t1 as (select 1 id, '1997-01-01' as a_date, null as b_date, null as c_date from dual union all
            select 2 id, null as a_date, '2001-01-01' as b_date, null as c_date from dual union all
            select 3 id, null as a_date, null as b_date, '2001-03-01' as c_date from dual union all
            select 4 id, '1999-02-03' as a_date, null as b_date, null as c_date from dual union all
            select 5 id, '2003-01-03' as a_date, '2001-03-06' as b_date, '1998-01-01' as c_date from dual union all
            select 6 id, null as a_date, '2001-03-06' as b_date, '1998-01-01' as c_date from dual union all
            select 7 id, '1999-02-03' as a_date, null as b_date, '2001-07-01' as c_date from dual union all
            select 8 id, '2001-02-03' as a_date, '2002-07-01' as b_date, null as c_date from dual)  
select id, a_date, b_date, c_date,
case
when a_date is not null and b_date is not null and c_date is not null then a_date
when a_date is not null and b_date is not null and c_date is null then a_date
when a_date is not null and b_date is null and c_date is not null then a_date
when a_date is null and b_date is not null and c_date is not null then b_date
when a_date is null and b_date is null then c_date
when b_date is null and c_date is null then a_date
when a_date is null and c_date is null then b_date
else null end as calc_date
from   t1
where case
when a_date is not null and b_date is not null and c_date is not null then a_date
when a_date is not null and b_date is not null and c_date is null then a_date
when a_date is not null and b_date is null and c_date is not null then a_date
when a_date is null and b_date is not null and c_date is not null then b_date
when a_date is null and b_date is null then c_date
when b_date is null and c_date is null then a_date
when a_date is null and c_date is null then b_date
else null end  >= '2000-01-01'

标签： sqloracle

Answer 1

You can use COALESCE this function returns the first non null value

COALESCE(a_date, b_date, c_date)