Error 1366 - Incorrect decimal value: '' for column '' at row -1

Question

I've got a rather large query that is trying to get a list of carriers and compare the amount of insurance they have on record to identify carriers that do not meet a minimum threshold. If I run the select query it works just fine with no errors. But when I try to use it for an insert into a table it returns this error message

[Err] 1366 - Incorrect decimal value: '' for column '' at row -1

I have to use the cast as decimal at the bottom of this query because the value that is being stored in the database is a varchar and I cannot change that.

Anyone have any ideas?

set @cw_days = 15;
INSERT INTO carrier_dnl (carrier_id, dnl_reason_id, status_id)
SELECT work_cw_carrier_status_update.carrier_id, company_dnl_schema.dnl_reason_id,
  CASE
    WHEN work_cw_carrier_status_update.comparison_date > @cw_days THEN 1
  ELSE 4
  END as status
  FROM work_cw_carrier_status_update
  JOIN company_dnl_schema
    ON company_dnl_schema.dnl_reason_id = 51
  LEFT OUTER JOIN carrier_insurance
    ON carrier_insurance.carrier_id = work_cw_carrier_status_update.carrier_id
  WHERE ifnull(carrier_insurance.insurance_type_id,4) = 4
    AND date(now()) BETWEEN IFNULL(carrier_insurance.insurance_effective_date,DATE_SUB(now(),INTERVAL 1 day)) AND IFNULL(carrier_insurance.insurance_expiration_date,DATE_ADD(now(),INTERVAL 1 day))
    AND CASE WHEN NULLIF(carrier_insurance.insurance_bipdto_amount,'') is null THEN 0 < company_dnl_schema.value
    ELSE
      ifnull(cast(replace(carrier_insurance.insurance_bipdto_amount, '*','') as decimal),0) < company_dnl_schema.value
    END
    AND ( work_cw_carrier_status_update.b_bulk = 0 OR work_cw_carrier_status_update.b_bulk = 1 )
    AND ( work_cw_carrier_status_update.b_otr = 1 OR work_cw_carrier_status_update.b_ltl = 1
            OR work_cw_carrier_status_update.b_dray = 1 OR work_cw_carrier_status_update.b_rail = 1
            OR work_cw_carrier_status_update.b_intermodal = 1 OR work_cw_carrier_status_update.b_forwarder = 1
            OR work_cw_carrier_status_update.b_broker = 1 )
group by work_cw_carrier_status_update.carrier_id;`

Answer 1

If the select seems to work, then there are two possible problems. The first is that the select doesn't really work and the problem appears further down in the data. Returning one or a handful of rows is not always the same as "working".

The second is an incompatibility with the types for the insert. You can try to use silent conversion to convert the values in the select to numbers:

SELECT work_cw_carrier_status_update.carrier_id + 0, company_dnl_schema.dnl_reason_id + 0,
       (CASE WHEN work_cw_carrier_status_update.comparison_date > @cw_days THEN 1
             ELSE 4
        END) as status

This may look ugly, but it is not nearly as ugly as storing ids as strings in one table and as numbers in another.