Oracle UNION ALL not returning full set

Question

I have two tables that are populated with the same structure into 2 different tables: MST3_CURR and MST4_CURR. Accounts are populated into one of the two tables; each table holds accounts that are in a different 'state'. In order to generate a complete list of accounts the tables need to be fully joined and have the most current data for an account pulled.

There are several other tables which follow the exact same approach where I am using a UNION ALL operator without issue. However, for some reason with these two tables when I perform the UNION ALL I receive the record for account 4700121500023998 which is found in MST3_CURR, but the other accounts are in MST4_CURR and are not present in the final dataset. When I reverse the UNION ALL order and have MST4_CURR first followed by MST3_CURR the reverse is true.

WITH cchm_d_curr AS (
  SELECT * FROM hcus_raw.cchm_d_mst3_curr
    UNION ALL
  SELECT * FROM hcus_raw.cchm_d_mst4_curr
)
SELECT chd_current_balance FROM cchm_d_curr
WHERE
  chd_account_number IN (4700121500023998, 4700121500090430, 4700121500044101, 4700121500250492, 4700121500250013)
;

I have am at a loss to finding any kind of answer to this peculiar behaviour that Oracle 12c is exhibiting. Please let me know if there is information missing that would help answer my question.

Thank you.

Are these normal tables, or they they perhaps views? Is there anything else unusual about them? Is the query running in parallel? — Alex Poole, Feb 01 '17 at 15:35
'UNION ALL' is not a join! If you are confused about that, how can we trust that everything else you say is in fact accurate? — , Feb 01 '17 at 15:36
@Alex: There is nothing unusual about the tables. These are not views or materialized views. — Byron Ferguson, Feb 01 '17 at 18:59
@mathguy: you are correct that it is not a _JOIN_, but rather an operator. — Byron Ferguson, Feb 01 '17 at 19:01
Are you logged in with the user who owns the schema containing these tables? are there database links you're going over? are there synonyms you're using to access these tables? Do you have copies of these tables in the schema you're logging in with? Is there a public synonym or view for cchm_d_curr? — xQbert, Nov 17 '17 at 22:00

score 1 · Answer 1 · answered May 27 '20 at 06:44

It might be that order of columns in the queried tables is different, so reversing tables in the union leads to different columns to be filtered by "where", i.e.

select a, b 
from (select A, b from t1
      union all
      select b, A from t2)
where a=1

returns something different from what expected from this query

select a, b 
from (select A, b from t1
      union all
      select A, b from t2)
where a=1

I would check if columns order in tables hcus_raw.cchm_d_mst3_curr and hcus_raw.cchm_d_mst4_curr in the original question is same.

score 0 · Answer 2 · edited Feb 01 '17 at 18:35

0

what does the following return [m3 first, then m4 first]??

{
WITH cchm_d_curr AS (
  SELECT 'm3' src, m3.* FROM hcus_raw.cchm_d_mst3_curr m3
    UNION ALL
  SELECT 'm4' src, m4.* FROM hcus_raw.cchm_d_mst4_curr m4
)
SELECT src, chd_account_number, chd_current_balance FROM cchm_d_curr
WHERE
  chd_account_number IN (4700121500023998, 4700121500090430, 4700121500044101, 4700121500250492, 4700121500250013)
;
}

edited Feb 01 '17 at 18:35

Mike

3,186
3
26
32

answered Feb 01 '17 at 18:32

Roger Cornejo

1,507
1
8
7

The result of this query statement results in **0** records from _m4_ being present in the result. If I reverse the order within the _WITH_ clause then the resultset only contains records from _m4_ – Byron Ferguson Feb 01 '17 at 19:02

score 0 · Answer 3 · answered Jul 30 '23 at 05:37

0

Noticed the same issue recently. For some reason, WITH clause don't go well with "UNION ALL" clause. Change the query to UNION as shown below and it should go through.

{
WITH cchm_d_curr AS (
  SELECT 'm3' src, m3.* FROM hcus_raw.cchm_d_mst3_curr m3
    UNION
  SELECT 'm4' src, m4.* FROM hcus_raw.cchm_d_mst4_curr m4
)
SELECT src, chd_account_number, chd_current_balance FROM cchm_d_curr
WHERE
  chd_account_number IN (4700121500023998, 4700121500090430, 4700121500044101, 4700121500250492, 4700121500250013)
;
}

answered Jul 30 '23 at 05:37

Suresh

1

This answer should be a comment on the answer to which it is a copy of. And it's an unproven answer too, so feedback on that answer via the comments. Unless you have solved the problem in the answer and can post a better answer. – Wayne Birch Aug 04 '23 at 09:15
This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/34766683) – wp78de Aug 04 '23 at 19:22

Oracle UNION ALL not returning full set

3 Answers3