SQL error: "expression must have same datatype as corresponding expression"
- Article Type: General
- Product: Aleph
- Product Version: 21
Problem Symptoms:
This SQL:
SQL> select substr(Z601_REC_KEY_3,1,9) from z601 where substr(Z601_REC_KEY_3,1,9) ^= '000000000' minus select Z103_LKR_DOC_NUMBER from z103 where Z103_LKR_LIBRARY='XXX50'
gets the following ERROR message:
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
Cause:
Z601_REC_KEY_3 is datatype Character while Z103_LKR_DOC_NUMBER is datatype NUMBER.
Resolution:
In the SQL change "substr (Z601_REC_KEY_3,1,9)" to "substr (to_number (Z601_REC_KEY_3,1,9))"
Additional Information
The SQL command "desc znnn" <where "znnn" is the table name> can be used to see the datatype of each column in a table:
Name Type
Z601_REC_KEY_3 CHAR(14)
Z103_LKR_DOC_NUMBER NUMBER(9)
Category: System Management (500)
Subject: Oracle (500)
- Article last edited: 12/11/2013