[SOLVED] Teradata Error 3653 SQLState 21S02 All select-lists do not contain the same number of expressions – easy solution!

Teradata Error" 3653 SQLState 21S02: 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.

Introduction

Error 3653 (SQLState 21S02) in Teradata" indicates that an error occurred while trying to execute a SELECT statement. The error message is “Column number out of range”.

This error can occur for a number of reasons, such as:

• The column name specified in the SELECT clause does not exist in the table.
• The column name is spelled incorrectly.
• The column name is qualified with the wrong table or view name.
• The column name is a reserved word that needs to be escaped with double quotes.

#1 Example – Teradata Error 3653 SQLState 21S02

Error 3653 with SQLState 21S02 indicates that you are trying to execute a SELECT statement that has multiple SELECT clauses, but the clauses do not have the same number of columns. This error can occur when you are using UNION, INTERSECT, or EXCEPT operators to combine the results of multiple SELECT statements.

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?

Cause of Teradata Error 3653

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.

Solution for Teradata Error 3653

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:

Work around for Teradata Error 3653

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 Teradata" Error 3653 SQLState 21S02 All select-lists do not contain the same number of expressions error message.

#2 Example

For example, you might see this error if you try to execute a query like the following:

SELECT col1, col2
FROM table1
UNION
SELECT col1
FROM table2;

To fix this error, you need to ensure that all of the SELECT clauses in your query have the same number of columns. You can do this by adding NULL values to the SELECT clauses that have fewer columns, or by modifying the SELECT clauses to include the same number of columns.

For example, the following query would be valid because all of the SELECT clauses have the same number of columns:

SELECT col1, col2
FROM table1
UNION
SELECT col1, NULL
FROM table2;

#3 Example

To resolve this error, you should check the SELECT clause of your query and ensure that all column" names are spelled correctly and properly qualified. If you are using a reserved word as a column name, you should escape it with double quotes.

For example, the following query will generate an error because the column “order” is a reserved word:

SELECT order FROM orders;

To fix the error, you can escape the column name with double quotes:

SELECT "order" FROM orders;

Summary

If you are not sure which column is causing the error, you can try running the query with only a few columns to narrow down the problem. You can also try using the DESCRIBE command to get a list of all columns in the table, which can help you identify any spelling or qualification errors.

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!