CODEWITHSUNDEEP
sqloracle-databaseoracle12cunion-all
Byron Ferguson
Byron Ferguson

Reputation: 21

Oracle UNION ALL not returning full set

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.

Upvotes: 2

Views: 2351

Answers (3)

Suresh
Suresh

Reputation: 1

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)
;
}

Upvotes: 0

korabelnik
korabelnik

Reputation: 11

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.

Upvotes: 1

Roger Cornejo
Roger Cornejo

Reputation: 1547

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)
;
}

Upvotes: 0

Related Questions