Search code examples
sqldb2truncate

String data right truncation DB2 error

I am receiving the error "String data right truncation" on db2 when I use this query 

SELECT BILL_NUMBER, 'PAPERWORK BUT NOT COMPLETE', 'NONE', NULL, '00000',NULL,NULL,TOTAL_CHARGES, NULL FROM TLORDER WHERE 
CURRENT_STATUS NOT IN ('COMPLETE','APPRVD','PAPERWISE','BILLD','EDIBILLED','CANCL')   AND BILL_TO_CODE NOT LIKE CASE WHEN :INCLUDE_DED = 'No' THEN 'ROCD%' ELSE '1234kkh5656' END
AND EXISTS (SELECT 1 FROM LIST_CHECKIN_AUDIT A WHERE A.BILL_NUMBER = TLORDER.BILL_NUMBER FETCH FIRST 1 ROW ONLY)
AND SITE_ID = :SITE AND DELIVER_BY_END >= CURRENT TIMESTAMP - 3 MONTHS AND COALESCE(PICK_UP_DRIVER,'') = '' AND '00000' =:DRIVER_ID

However when I suppress this line I do not get the error. 

AND BILL_TO_CODE NOT LIKE CASE WHEN :INCLUDE_DED = 'No' THEN 'ROCD%' ELSE '1234kkh5656' END

Thanks in advance!

Solution

  • I'd venture to guess that this happens when the value of the :INCLUDE_DED host variable exceeds 2 bytes in length. You do not supply the variable data type, so the query compiler derives it from the right side of the comparison, where the literal 'No' has the length of 2 bytes. If you then assign a value like 'Yes' to the host variable it has to be truncated.

    Consider adding an explicit type information to the host variable reference, e.g.:

    ...WHEN CAST(:INCLUDE_DED AS VARCHAR(10)) = 'No'...

    Use the data type appropriate for the range of possible values.