How to combine subqueries

oracle

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?

Best Answer

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.

Related Solutions

How to find out which data I just imported into Oracle

Using import with show=y won't actually do the import, but will show which tables the import will run against. There's no practical way, however, to show what rows import will write; it's assumed that you, running the import, would know that.

You may wish to look at some of Oracle's extensive documentation about these utilities to determine what options are available and what they do.

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Related Question