String data right truncation DB2 error

Question

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!

Answer 1

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.

Answer 2

I would first check the datatype of the bill_to_code. You are returning '1234kkh5656' that may exceed the length of the datatype.

Answer 3

In my case, I was selecting a service code (int) but passing the service value (string) via the exposed API in our system instead.

It is important to ensure that one is passing the right data.