当将varchar值“AJH 123”转换为数据类型int时，SQL转换失败

I have a select statement what i need to run a case statement on it to check for a value first and then enter decide which column to get.

我有一个select语句，我需要在它上面运行case语句，首先检查值，然后输入decide要获取哪个列。

the start of the call is this

电话的开头是这样的

select 
case when req.ReportStatusCode in ('draft','submitted') then 
req.ID else 
req.reportNumber
end as [Request Id \ Report Number],

however when I call this i get an error

但是当我调用它时，会得到一个错误

Conversion failed when converting the varchar value 'AJH 123' to data type int.

当将varchar值'AJH 123'转换为数据类型int时，转换失败。

this is the report number field and ive no idea how i could stop this.

这是报告编号字段，我不知道该如何阻止它。

any and all help much appreciated

非常感谢您的帮助

2 个解决方案

#1

case when req.ReportStatusCode in ('draft','submitted')
then cast(req.ID as varchar(255)) else  cast(req.reportNumber as varchar(255)) end

Make sure case returns the same data type.

确保case返回相同的数据类型。

#2

This is because case statement should evaluate to one type so it converts varchar to int here. Explicitly convert int to varchar:

这是因为case语句应该计算为一种类型，所以它在这里将varchar转换为int。显式地将int转换为varchar:

select 
case when req.ReportStatusCode in ('draft','submitted') then 
     cast(req.ID as varchar(100)) else 
     req.reportNumber
end as [Request Id \ Report Number],

#1

case when req.ReportStatusCode in ('draft','submitted')
then cast(req.ID as varchar(255)) else  cast(req.reportNumber as varchar(255)) end