Icon View Incident Report

Serious Serious
Reported By: Ole Willy Tuv
Reported On: 12/12/2002
For: Version 3.20 Build 1
# 1291 UNIONing Two SQL Statements Containing Sub-Selects Causes an Error

When running the script below my signature, I get an exception with the following message:

# 11949 SQL error - The number of columns in the UNION ALL query must match the number of columns in the master query.

DROP TABLE IF EXISTS MEMORY Table1;
CREATE TABLE MEMORY Table1 (
  c1 CHAR(1),
  c2 CHAR(1),
  c3 CHAR(1),
  PRIMARY KEY (c1)
);
INSERT INTO MEMORY Table1 VALUES('1', 'a', 'a');
INSERT INTO MEMORY Table1 VALUES('2', 'b', 'b');
INSERT INTO MEMORY Table1 VALUES('3', 'c', 'c');
INSERT INTO MEMORY Table1 VALUES('4', 'd', 'd');

DROP TABLE IF EXISTS MEMORY Table2;
CREATE TABLE MEMORY Table2 (
  c1 CHAR(1),
  c2 CHAR(1),
  c3 CHAR(1),
  PRIMARY KEY (c1)
);
INSERT INTO MEMORY Table2 VALUES('1', 'a', 'a');
INSERT INTO MEMORY Table2 VALUES('2', 'b', 'b');
INSERT INTO MEMORY Table2 VALUES('3', 'e', 'e');

SELECT c1, c2, c3
FROM MEMORY Table1
WHERE c2||c3 NOT IN (SELECT c2||c3 FROM MEMORY Table2)
UNION
SELECT c1, c2, c3
FROM MEMORY Table2
WHERE c2||c3 NOT IN (SELECT c2||c3 FROM MEMORY Table1)



Resolution Resolution
Fixed Problem on 12/18/2002 in version 3.21 build 1
Image