0

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'
Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
seth
  • 35
  • 4
  • 1
    Can you read that? I can't. Please learn how to ***format*** your code. I can do it for you, but then you won't learn anything from it. Please do your own little research for a couple of minutes - I promise it won't take more than that. Then edit your post to format the code. –  Feb 27 '21 at 15:17
  • 2
    In any case, if you have three expressions of the same data type, and you want to return the first one that is not `null`, you can use `coalesce`. Again I won't say more; Google for "Oracle coalesce" and learn about this function. –  Feb 27 '21 at 15:18
  • 1
    Another thing - you will reap extreme benefits from learning to use the `date` data type for dates (instead of simply handling them as strings, as you do in your code). –  Feb 27 '21 at 16:04
  • sorry for lack of format, will do next time. Appreciate all the answers! – seth Feb 27 '21 at 23:44

1 Answers1

1

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

COALESCE(a_date, b_date, c_date)
Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
  • Another option (not as good as COALESCE, IMO, but perhaps there'll be a PL/SQL obfuscated code contest :-) is a series of nested NVL calls, e.g. `NVL(a_date, NVL(b_date, c_date))` – Bob Jarvis - Слава Україні Feb 27 '21 at 15:43
  • 2
    @BobJarvis-ReinstateMonica - If you say that, perhaps you should also briefly mention why your option is not as good as `coalesce`. Besides the cleaner code, and the fact that `coalesce` is in the SQL Standard while `nvl` is not, `coalesce` uses shortcut evaluation; `nvl` does not. This may be especially relevant if the "later" expressions are expensive to evaluate. –  Feb 27 '21 at 16:01
  • I have always been puzzled why Oracle stopped at two arguments for `nvl`, and why they never added more. It's hard to imagine a scenario where more would have broken any existing code. – William Robertson Feb 27 '21 at 20:23
  • It's simply because it's not necessary as coalesce is a standard SQL function and nvl was just to get a default value when a column value is null – Simon Martinelli Feb 28 '21 at 10:55