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))"