Search code examples
pythondb2ibm-midrangepyodbc

DB2 Matching on different data types

I have to tables that have a join field but they aren't matching data types. One is a String and one is a Decimal. When I select them using pyodbc sql query, I can use the following to get them to return identical results:

int(trim(imus15))

int(substr(cmcsno, 1, 6))

However I get the following error when I run this join:

[DB2 for i5/OS]SQL0802 - Data conversion or data mapping error. (-802)

I have tried:

CAST(TRIM(IMUS15 AS INTEGER)) = int(substr(cmcsno, 1, 6))

I cannot determine how to join on these two fields.

IMUS15 would = '112303 '

CMCSNO would = 112303

Solution

  • There is a non-numeric value in the IMUS15 field somewhere in the table.

    The quickest solution is an explicit character comparison:

    JOIN TABLE2 ON IMUS15 = CHAR(CMCSNO)

    For example:

    WITH TABLE1 AS (SELECT '112303      ' IMUS15 FROM SYSIBM.SYSDUMMY1),
TABLE2 AS (SELECT 112303 CMCSNO FROM SYSIBM.SYSDUMMY1)
SELECT * FROM TABLE1
JOIN TABLE2 ON IMUS15 = CHAR(CMCSNO)