Warning: #1292 Truncated incorrect DECIMAL valu

Question

I have a problem with the following SQL script, where I received the warning "#1292 Truncated incorrect DECIMAL value".

All the fields are VARCHAR. I tried with the cast in CHAR but nothing. I Tried by removing the AND cast(substring_index(substring_index(nomefilepdf, '_', 12), '_', -1) as int) = 0 but nothing. I noticed that if i remove the AND operator from the WHERE clauses the warning dissapear, so I think is not a problem with decimal's values.

  INSERT INTO `BOP_APPENDICI`(RAMO, RISCHIO, PREMIO)            
SELECT 
substring_index(substring_index(nomefilepdf, '_', 10), '_', -1) as ramo_view,
substring_index(substring_index(nomefilepdf, '_', 11), '_', -1) as rischio_view,
substring_index(substring_index(nomefilepdf, '_', 12), '_', -1) as premio_view
FROM `BOP_importazioneAppendici
WHERE 
substring_index(substring_index(nomefilepdf, '_', 4), '_', -1) IN 
    (SELECT TIPO_DOC FROM BOP_DOMINIO_DOCUMENTI)
AND NOT EXISTS (
    SELECT 1 
    FROM BOP_DOMINIO_RAMO_RISCHIO d
    WHERE 
        d.ramo = substring_index(substring_index(nomefilepdf, '_', 10), '_', -1)
        AND d.rischio = substring_index(substring_index(nomefilepdf, '_', 11), '_', -1)
)
AND cast(substring_index(substring_index(nomefilepdf, '_', 12), '_', -1) as int) = 0

Answer 1

In the last line you're casting it as integer:

AND cast(substring_index(substring_index(nomefilepdf, '_', 12), '_', -1) as int) = 0

If I understood correctly, by removing this AND-clause you don't get the error because it's not trying to cast a char as int anymore.

Are you sure the column you're casting as integer is composed only of numbers?

Answer 2

My answer it's only to try help you to debug your problem:

SELECT DISTINCT substring_index(substring_index(nomefilepdf, '_', 12), '_', -1)
FROM BOP_importazioneAppendici
WHERE
    cast(substring_index(substring_index(nomefilepdf, '_', 12), '_', -1) as int) = 0
    AND nomefilepdf NOT REGEXP '^[0-9]+$';

Running this query should find the records that give you problems, so you can better understand the problem. Let me know if this help you