SQL:转换失败
SQL:转换失败
提问于 2018-06-06 05:09:22
SELECT
*
,CASE
WHEN emp_type='a' then '30'
when emp_type='b' then '20'
else '25' end as s
, case when tbl_cut.dir_date='0' or tbl_cut.nameOFmonthe_cut='2018/05' then (coalesce(work_days_cut,0)-coalesce(count(addCut_id),0))
else (coalesce(s,0)-coalesce(count(addCut_id),0) end as 'days'
, case when tbl_cut.dir_date='0' or tbl_cut.nameOFmonthe_cut='2018/05' then (((coalesce(work_days_cut,0)-coalesce(count(addCut_id),0)))*(coalesce([emp_price],0)))
else (((coalesce((cast('s'as int)),0)-coalesce(count(addCut_id),0)))*(coalesce([emp_price],0))) end as 'salary'
FROM
(
select distinct (emp_id)
from
(
select emp_id from tbl_employee
union select emp_id from tbl_add_cut
union select emp_id from tbl_cut
) tmp
) emp_ids
left join tbl_add_cut
on tbl_add_cut.emp_id=emp_ids.emp_id
and nameOFmonthe='2018/05'
left join tbl_cut
on tbl_cut.emp_id=emp_ids.emp_id
left join tbl_employee
on tbl_employee.emp_id=emp_ids.emp_id
inner join tbl_department
on tbl_employee.depart_id=tbl_department.depart_id
--where emp_name like '%'+ @emp_name + '%' and [emp_type]=@emp_type
group by
tbl_employee.emp_id,tbl_employee.emp_name,tbl_department.depart_Des,[emp_degree],
tbl_employee.[emp_price],[emp_type],tbl_add_cut.nameOFmonthe, work_days_cut,
tbl_cut.nameOFmonthe_cut,tbl_cut.dir_date,emp_work_days我得到了这个错误
将varchar值“%s”转换为数据类型int时，
转换失败。
谢谢
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-06 06:45:20
好的，我看到了。您正在创建一个名为“%s”的薪资字段，但您还不能引用它，因为它在同一查询中。只需对引用它的第二个CASE语句中的's‘使用相同的CASE语句即可。这里我在第二次使用时删除了强制转换，因为你希望它是一个整数，而不是一个字符串(删除了强制转换，删除了数字两边的单引号)：
, CASE
WHEN emp_type='a' then '30'
when emp_type='b' then '20'
else '25' end as s
, case when tbl_cut.dir_date='0' or tbl_cut.nameOFmonthe_cut='2018/05' then (coalesce(work_days_cut,0)-coalesce(count(addCut_id),0))
else (coalesce(s,0)-coalesce(count(addCut_id),0) END as 'days'
, case when tbl_cut.dir_date='0' or tbl_cut.nameOFmonthe_cut='2018/05' then (((coalesce(work_days_cut,0)-coalesce(count(addCut_id),0)))*(coalesce([emp_price],0)))
else (((coalesce((CASE
WHEN emp_type='a' then 30
when emp_type='b' then 20
else 25 end),0)-coalesce(count(addCut_id),0)))*(coalesce([emp_price],0))) end as 'salary'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50708967
复制相关文章
相似问题