CODEWITHSUNDEEP
sqloracle-databaseunion
Jokke Heikkilä
Jokke Heikkilä

Reputation: 928

Oracle advanced union

Is there any advanced Oracle SQL methods to solve this kind of situation?

Simplified: Two queries returns primary_key_value and other_value. Both queries always return primary_key_value but other_value might be null.

So how I can union those two queries so that it returns always those rows which has other_value, but if both queries are having other_value = null with same primary key, then only one row should be returned.

I know this is so stupid case. But specifications were like this :)

Example: First query:

A  |  B
=======
1  | X
2  | 
3  | 
4  | Z

Second query:

A  |  B
=======
1  | Y
2  | 
3  | Z
4  |

So result need to be like this:

A  |  B
=======
1  | X
1  | Y
2  | 
3  | Z
4  | Z

Upvotes: 0

Views: 580

Answers (3)

Morbo
Morbo

Reputation: 536

Another way to look at is that you want all possible values from the union of column A then left outer outer join these with the non-null values from column B, thus only showing null in B when there is no non-null value to display.
roughly: 

WITH q1 as (whatever),
     q2 as (whatever)
SELECT All_A.A, BVals.B
FROM (SELECT DISTINCT A FROM (SELECT A FROM q1 UNION SELECT A FROM q2)) All_A,
     (SELECT A,B FROM q1 WHERE B IS NOT NULL
      UNION
      SELECT A,B FROM q2 WHERE B IS NOT NULL) BVals
WHERE All_A.A = BVals.A (+)

Also pruning the unwanted nulls explicitly could do the same job:

WITH q3 AS (q1_SELECT UNION q2_SELECT)
SELECT A,B
FROM q3 main
WHERE NOT ( B IS NULL AND
            EXISTS (SELECT 1 FROM q3 x WHERE main.A = x.A and x.B IS NOT NULL) )

Upvotes: 0

Maep
Maep

Reputation: 843

If you want use something really advanced, use model clause http://rwijk.blogspot.com/2007/10/sql-model-clause-tutorial-part-one.html But, in real life, using such things usually means bad-designed data model

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

You could use analytics:

SQL> WITH q1 AS (
  2     SELECT 1 a, 'X' b FROM DUAL UNION ALL
  3     SELECT 2 a, ''  b FROM DUAL UNION ALL
  4     SELECT 3 a, ''  b FROM DUAL UNION ALL
  5     SELECT 4 a, 'Z' b FROM DUAL
  6  ), q2 AS (
  7     SELECT 1 a, 'Y' b FROM DUAL UNION ALL
  8     SELECT 2 a, ''  b FROM DUAL UNION ALL
  9     SELECT 3 a, 'Z' b FROM DUAL UNION ALL
 10     SELECT 4 a, ''  b FROM DUAL
 11  )
 12  SELECT a, b
 13    FROM (SELECT a, b,
 14                 rank() over(PARTITION BY a 
 15                             ORDER BY decode(b, NULL, 2, 1)) rnk
 16             FROM (SELECT * FROM q1
 17                    UNION
 18                   SELECT * FROM q2))
 19   WHERE rnk = 1;

         A B
---------- -
         1 X
         1 Y
         2 
         3 Z
         4 Z

Upvotes: 4

Related Questions