Oracle SQL Unions error “query block has incorrect number of results columns”

I’m trying to write a query that pulls data from a lot of tables, and has about 20 unions. It’s pulling the same information repeatedly, but with more layers each time, to show a sort of tree.

I want to compare the final two columns. I’m using a case to do this, and if I add a case to this query then I get the error “query block has incorrect number of results columns”. This seems to be because the final select in the union has an extra column (the compare case).

Is there any way to work around this? I don’t want to add the case to each select, as this would add about 15 more columns that I don’t want.

Answer

Use a sub-query:

SELECT col1,
       col2,
       CASE
         WHEN col1 = 'somevalue'
         THEN 'someresult'
         ELSE 'otherresult'
       END AS col3
FROM   (
  SELECT col1, col2 FROM table1 UNION ALL
  SELECT col1, col2 FROM table2 UNION ALL
  SELECT col1, col2 FROM table3
  -- ...
);

Or use a sub-query factoring clause:

WITH data ( col1, col2 ) AS (
  SELECT col1, col2 FROM table1 UNION ALL
  SELECT col1, col2 FROM table2 UNION ALL
  SELECT col1, col2 FROM table3
  -- ...
)
SELECT col1,
       col2,
       CASE
         WHEN col1 = 'somevalue'
         THEN 'someresult'
         ELSE 'otherresult'
       END AS col3
FROM   data;