Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    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

    Z601_REC_KEY_3 is datatype Character while Z103_LKR_DOC_NUMBER is datatype NUMBER.

    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)


    Category: System Management (500)

    Subject: Oracle (500)

    • Article last edited: 12/11/2013