错误1366 - 十进制值不正确：''表示第-1行的列''

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

[错误] 1366 - 十进制值不正确:''为第-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.

我必须在此查询的底部使用强制转换为十进制,因为存储在数据库中的值是varchar,我无法更改它。

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;`

1 个解决方案

#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中的值转换为数字:

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.

这可能看起来很难看,但它并不像在一个表中将字符存储为字符串而在另一个表中存储数字一样难看。

#1