Error 1366 - Invalid decimal value: '' for column '' in row -1

Question

I have a rather large request that tries to get a list of carriers and compare the amount of insurance they have on the record to identify carriers that do not meet the minimum threshold. If I run a select query, it works fine, without errors. But when I try to use it to insert into the table, it returns this error message

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

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

Does anyone have any idea?

 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 select works, then there are two possible problems. Firstly, that select does not work, and the problem arises in the data below. The return of one or more rows does not always match the "worker" one.

The second is incompatibility with the types to be inserted. You can try using silent conversion to convert select values ​​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 

It may look ugly, but it is not as ugly as storing identifiers as rows in one table and numbers in another.