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

Question

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.

if it´s allways the same than you could check out the [with clause](https://stackoverflow.com/questions/12552288/sql-with-clause-example) — SomeJavaGuy, Jun 01 '17 at 08:56
Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Aug 18 '22 at 05:53

score 2 · Accepted Answer · answered Jun 01 '17 at 09:01

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;

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

1 Answers1