In this post, I will explain why you encountered the error message [Error 3653] [SQLState 21S02] All select-lists do not contain the same number of expressions, also known as Failed [3653: 21S02] All select-lists do not contain the same number of expressions, I will present the cause of the problem and show how to avoid a error in the future.
The problem may occurs during using UNION, UNION ALL, MINUS or INTERSECT operations. The below is the easiest way to get an error message using the UNION ALL operation:
CREATE TABLE SUBJECT ( SUBJECT_ID INTEGER, SUBJECT_NAME VARCHAR(100), LOAD_DATE TIMESTAMP(0)); CREATE TABLE SUBJECT_HISTORY( SUBJECT_ID INTEGER, SUBJECT_NAME VARCHAR(100), LOAD_DATE TIMESTAMP(0), END_DATE TIMESTAMP(0));
We have created the two tables SUBJECT and SUBJECT_HISTORY, which is a snapshot of historical data and has an additional attribute – END_DATE that stores the record closing date. Let’s try to get information about all the subjects we have ever had:
SELECT * FROM SUBJECT UNION ALL SELECT * FROM SUBJECT_HISTORY;
It does not look good – we got an error message, but why?
I wrote not without reason that the SUBJECT_HISTORY table has an additional attribute – END_DATE. At the moment, the SUBJECT table has 3 columns, while the historical table has 4 attributes. During execute the SELECT * query , it is as if we wrote:
SELECT SUBJECT_ID, SUBJECT_NAME, LOAD_DATE FROM SUBJECT UNION ALL SELECT SUBJECT_ID, SUBJECT_NAME, LOAD_DATE, END_DATE FROM SUBJECT_HISTORY;
What should Teradata do with the END_DATE attribute? It is defined only in one SELECT query, so the lists of our attributes are different – and this is the reason for the 3653 error message.
Always specify the attribute list instead of using SELECT * during the UNION / UNION ALL / MINUS / INTERSECT operation
If the attribute lists in the tables on which you execute one of the above query are different, remember to instead of using the SELECT * statment provide the full list of attributes you need. The number of columns in the query must be the same. If you do not need an attribute that interferes with your list, you can simply skip it in the query:
SELECT SUBJECT_ID, SUBJECT_NAME, LOAD_DATE FROM SUBJECT UNION ALL SELECT SUBJECT_ID, SUBJECT_NAME, LOAD_DATE FROM SUBJECT_HISTORY;
Okay, but what if the attribute END_DATE is important to me? Especially if we combine records using UNION ALL we would like to know if this is a new or historical record. Let’s look at the workaround below:
If you need all of the attributes in the table where there are more, but you do not know how to use UNION / UNION ALL / MINUS or INTERSECT , replace the attribute gap with the default value or NULL. Let’s look below:
SELECT SUBJECT_ID, SUBJECT_NAME, LOAD_DATE, NULL AS END_DATE FROM SUBJECT UNION ALL SELECT SUBJECT_ID, SUBJECT_NAME, LOAD_DATE, END_DATE FROM SUBJECT_HISTORY;
The above query allows us to receive information about an additional attribute END_DATE without receiving a 3653 error message.