Getting a result in a Case Oracle-CodePudding

I have 4 tables from which I take date. these tables go in priority from top to bottom. The goal is if in the first there is an existing result to take it from the first table and skip all others. If the first will not take from the second and skip the next tables and so on.

SELECT 
DATE 
FROM TABLE1
IF  date NULL  then go to the other query if result !=  NULL then 
Skip other select  below

  UNION ALL
  SELECT 
  DATE 
  FROM TABLE2
  UNUAN ALL

 SELECT 
 DATE 
 FROM TABLE3
 UNION ALL
SELECT 
DATE 
FROM TABLE4

CodePudding user response：

You said:

I have 4 tables from which I take date

"Date", not "dates" (singular, not plural). If that means that each query you use returns just one row, then you could cross join them and use the coalesce function which returns the first non-null value.

Sample data:

SQL> with
  2  table1 as (select cast(null as date) datum from dual),
  3  table2 as (select date '2022-02-18'  datum from dual),   --> this is the 1st non-null value
  4  table3 as (select date '2022-03-15'  datum from dual),
  5  table4 as (select cast(null as date) datum from dual),
  6  --

Query begins here; these are your queries. Once again, they return a single row:

  7  t1 as (select datum from table1),
  8  t2 as (select datum from table2),
  9  t3 as (select datum from table3),
 10  t4 as (select datum from table4)

Finally, use coalesce:

 11  select coalesce(t1.datum, t2.datum, t3.datum, t4.datum)
 12  from t1 cross join t2 cross join t2 cross join t3 cross join t4;

COALESCE(T
----------
2022-02-18

SQL>

CodePudding user response：

You can filter out the NULL rows and include a priority column that you order by and then fetch only the first priority rows:

SELECT "DATE", 1 AS priority FROM TABLE1 WHERE "DATE" IS NOT NULL
UNION ALL
SELECT "DATE", 2 AS priority FROM TABLE2 WHERE "DATE" IS NOT NULL
UNION ALL
SELECT "DATE", 3 AS priority FROM TABLE3 WHERE "DATE" IS NOT NULL
UNION ALL
SELECT "DATE", 4 AS priority FROM TABLE4 WHERE "DATE" IS NOT NULL
ORDER BY priority ASC
FETCH FIRST ROW WITH TIES;