Skip to content
Advertisement

How does UNION ALL return blanks in one column and data in another?

I’ve a View that has two SELECT statements returning data using UNION ALL.

SELECT column1, column2, column3, column4
FROM table1
UNION ALL
SELECT column1, column2, column3, column4
FROM table2

So, all the columns in table1 are supposed to display data because column4=’A’. However, in table2, column2 should return blanks/null because column4=’B’. Keep in mind, column2 in table2 DOES contain data, but in the View, we only want to return blank/nulls.

Advertisement

Answer

You want something like this.? When Column4 is ‘B’ mask column2 as NULL or blanks. Using DECODE or even CASE WHEN can be used.

SELECT column1, column2, column3, column4
 FROM table1
 UNION ALL
 SELECT column1, DECODE(column4,'B',NULL,column2), column3, column4
 FROM table2

Multiple Values:

SELECT column1, column2, column3, column4
 FROM table1
UNION ALL
SELECT column1,
       CASE WHEN column4 IN ('B','D','O','R'..) THEN
          NULL
       ELSE
         column2
       END,
       column3,
       column4
FROM  table2
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement