帮我处理下面这段sql 报 invalid input syntax for type integer: "1.00" 的问题 select prod_dis_type , case when PROD_DIS_TYPE = '1' then DECODE(sum(FIRST_BIZ), 0, 0, ROUND(sum(case when CONTRACT_CODE = 'NHT-2200462' then FIRST_BIZ else 0 end) / NULLIF(sum(FIRST_BIZ), 0), 2))::INT else 0 end as FIRST_BIZ , case when PROD_DIS_TYPE = '2' then DECODE(sum(APPR_DESIGN_PROD), 0, 0, ROUND(sum(case when CONTRACT_CODE = 'NHT-2200462' then APPR_DESIGN_PROD else 0 end) / NULLIF(sum(APPR_DESIGN_PROD), 0), 2))::INT else 0 end as APPR_DESIGN_PROD from T_SPDI_CONT_SUB_PROJ where UNITERM_CODE = 'ZT-DX-440000-2023-00028' and SUB_PROJ_CODE = '220004-01-301' and PROD_DIS_TYPE = '1' GROUP BY PROD_DIS_TYPE
时间: 2024-03-27 13:38:03 浏览: 28
这个错误通常是因为在整数类型的列中插入了带有小数点的值。在这个查询中,错误可能是在使用DECODE函数时,由于在某些条件下结果为小数而不是整数。您可以尝试将DECODE函数中的INT强制转换移动到ROUND函数之后,例如:
```
SELECT prod_dis_type,
CAST(CASE
WHEN PROD_DIS_TYPE = '1' THEN ROUND(DECODE(sum(FIRST_BIZ), 0, 0, sum(CASE WHEN CONTRACT_CODE = 'NHT-2200462' THEN FIRST_BIZ ELSE 0 END) / NULLIF(sum(FIRST_BIZ), 0)), 2)
ELSE 0
END AS INTEGER) AS FIRST_BIZ,
CAST(CASE
WHEN PROD_DIS_TYPE = '2' THEN ROUND(DECODE(sum(APPR_DESIGN_PROD), 0, 0, sum(CASE WHEN CONTRACT_CODE = 'NHT-2200462' THEN APPR_DESIGN_PROD ELSE 0 END) / NULLIF(sum(APPR_DESIGN_PROD), 0)), 2)
ELSE 0
END AS INTEGER) AS APPR_DESIGN_PROD
FROM T_SPDI_CONT_SUB_PROJ
WHERE UNITERM_CODE = 'ZT-DX-440000-2023-00028'
AND SUB_PROJ_CODE = '220004-01-301'
AND PROD_DIS_TYPE = '1'
GROUP BY PROD_DIS_TYPE
```
这个查询也对第二个case语句做了同样的修改。