将字符串的varchar值转换为数据类型int时，转换失败

Question

我得到了问题与我的SQL如下语句。执行后，出现错误:

将varchar值'向前看'转换为数据时转换失败int类型。

SELECT description, moreThanDay, 
CONCAT(moreThanDay+1,' - ' , 
IIF(LEAD(moreThanDay) OVER (ORDER BY moreThanDay) > 0, 
LEAD(moreThanDay) OVER (ORDER BY moreThanDay),'ONWARDS')) AS rangeDay 
FROM EquipmentOwnerDetention WHERE equipmentOwnerCode=@equipmentOwnerCode 

是否有人知道如何cast，使结果将变成字符串?提前感谢。

Answer 1

IIF中的第二个参数为int， '向前看'为string。IIF希望两者具有相同的数据类型。尝试将第一个参数转换为字符串，它可能会工作。

像这样

SELECT description, moreThanDay, 
CONCAT(moreThanDay+1,' - ' , 
IIF(LEAD(moreThanDay) OVER (ORDER BY moreThanDay) > 0, 
STR(LEAD(moreThanDay) OVER (ORDER BY moreThanDay), 10),'ONWARDS')) AS rangeDay 
FROM EquipmentOwnerDetention WHERE equipmentOwnerCode=@equipmentOwnerCode 

Answer 2

发生错误是因为moreThanDay似乎是数字，而您不能直接将数字值连接到字符串。为简洁起见，我可能在这里包含一个CTE:

WITH cte AS (
SELECT *, LEAD(moreThanDay) OVER (ORDER BY moreThanDay) moreThanDayLead
FROM EquipmentOwnerDetention
WHERE equipmentOwnerCode = @equipmentOwnerCode
)
SELECT description, moreThanDay,
CONCAT(CAST(moreThanDay, VARCHAR(max)), ' - ',
IIF(moreThanDayLead > 0, CAST(moreThanDayLead, VARCHAR(max)), 'ONWARDS')) AS rangeDay
FROM cte;