帮我处理下面这段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 19:38:01 浏览: 178
这个错误通常是因为在整数类型的列中插入了带有小数点的值。在这个查询中,错误可能是在使用DECODE函数时,由于在某些条件下结果为小数而不是整数。您可以尝试将DECODE函数中的INT强制转换移动到ROUND函数之后,例如:
```
SELECT prod_dis_type,
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)::INT
ELSE 0
END AS FIRST_BIZ,
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)::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
```
这个查询也对第二个case语句做了同样的修改。
阅读全文
相关推荐
















