0

There are two subqueries select a set according to certain criteria, such as

select
    id, dt_beg, dt_end
  from ...................
  where ..............

it is necessary to select data from the second if there is no record zero, if it is zero, then from the first, it was originally thought (erroneously), thinking that the first will never return the record zero, therefore the second one

more or less like this:

SELECT
    ao.id,
    DECODE(NVL(an.id,0), 0,ao.dt_beg, an.dt_beg) AS dt_beg,
    DECODE(NVL(an.id,0), 0,ao.dt_end, an.dt_end) AS dt_end
  FROM
  (.....) ao, (.....) an
  WHERE ao.id = an.id(+)
    AND ao.id = _NUMBER_
;

But, it turned out, there are situations when the first returns NULL, while the second is not NULL and just the data from the second priority, i.e. if they are not NULL, then they must be substituted.

tried to do FULL OUTER JOIN but nothing is fetched, although there is definitely data in the second query.

SELECT
    ao.id,
    DECODE(NVL(an.id,0), 0,ao.dt_beg, an.dt_beg) AS dt_beg,
    DECODE(NVL(an.id,0), 0,ao.dt_end, an.dt_end) AS dt_end
  FROM
  (.....) ao
  FULL OUTER JOIN (.....) an ON ao.id = an.id
  WHERE ao.id = _NUMBER_ OR an.id = _NUMBER_
;

How to combine and select data?

  • You use DECODE() function which is not applicable for MySQL. Does you use Oracle? Check your question tags list... – Akina Nov 7 '19 at 8:15
1

You may use

WITH 
cte1 AS ( 1st query ),
cte2 AS ( 2nd query )
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2 WHERE ( SELECT COUNT(*) FROM cte1 ) = 0

Of course, record structure of both subqueries must be the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.