调整我的新脚本使列无效，并且不返回正确的表达式

我以前有一个脚本，在代码中用“for”对数据库进行多次调用，我更改了逻辑，将所有内容存储在临时表中，并将数据与 "#cellInfoToDelete" ，我在新脚本上有以下错误：
消息8133，16级，状态1，第14行
case规范中至少有一个结果表达式必须是null常量以外的表达式
消息207，16级，状态1，第47行
列名“schedid”无效
旧脚本：

UPDATE
    _PS_SCHEDULES_Details_CellInfo2
SET
    EmpNumDay1 = CASE WHEN @WeekDay = 1 THEN NULL ELSE EmpNumDay1 END,
    EmpNumDay2 = CASE WHEN @WeekDay = 2 THEN NULL ELSE EmpNumDay2 END,
    EmpNumDay3 = CASE WHEN @WeekDay = 3 THEN NULL ELSE EmpNumDay3 END,
    EmpNumDay4 = CASE WHEN @WeekDay = 4 THEN NULL ELSE EmpNumDay4 END,
    EmpNumDay5 = CASE WHEN @WeekDay = 5 THEN NULL ELSE EmpNumDay5 END,
    EmpNumDay6 = CASE WHEN @WeekDay = 6 THEN NULL ELSE EmpNumDay6 END,
    EmpNumDay7 = CASE WHEN @WeekDay = 7 THEN NULL ELSE EmpNumDay7 END,
    NotUsed1 = CASE WHEN @WeekDay = 1 THEN 1 ELSE NotUsed1 END,
    NotUsed2 = CASE WHEN @WeekDay = 2 THEN 1 ELSE NotUsed2 END,
    NotUsed3 = CASE WHEN @WeekDay = 3 THEN 1 ELSE NotUsed3 END,
    NotUsed4 = CASE WHEN @WeekDay = 4 THEN 1 ELSE NotUsed4 END,
    NotUsed5 = CASE WHEN @WeekDay = 5 THEN 1 ELSE NotUsed5 END,
    NotUsed6 = CASE WHEN @WeekDay = 6 THEN 1 ELSE NotUsed6 END,
    NotUsed7 = CASE WHEN @WeekDay = 7 THEN 1 ELSE NotUsed7 END
WHERE
    ID = @SchedID

新脚本：

UPDATE
    PSDetails
SET
    EmpNumDay1 = COALESCE(PSDetails.EmpNumDay1, cellToDel.EmpNumDay1),
    EmpNumDay2 = COALESCE(PSDetails.EmpNumDay2, cellToDel.EmpNumDay2),
    EmpNumDay3 = COALESCE(PSDetails.EmpNumDay3, cellToDel.EmpNumDay3),
    EmpNumDay4 = COALESCE(PSDetails.EmpNumDay4, cellToDel.EmpNumDay4),
    EmpNumDay5 = COALESCE(PSDetails.EmpNumDay5, cellToDel.EmpNumDay5),
    EmpNumDay6 = COALESCE(PSDetails.EmpNumDay6, cellToDel.EmpNumDay6),
    EmpNumDay7 = COALESCE(PSDetails.EmpNumDay7, cellToDel.EmpNumDay7),
    NotUsed1 = COALESCE(PSDetails.NotUsed1, cellToDel.NotUsed1),
    NotUsed2 = COALESCE(PSDetails.NotUsed2, cellToDel.NotUsed2),
    NotUsed3 = COALESCE(PSDetails.NotUsed3, cellToDel.NotUsed3),
    NotUsed4 = COALESCE(PSDetails.NotUsed4, cellToDel.NotUsed4),
    NotUsed5 = COALESCE(PSDetails.NotUsed5, cellToDel.NotUsed5),
    NotUsed6 = COALESCE(PSDetails.NotUsed6, cellToDel.NotUsed6),
    NotUsed7 = COALESCE(PSDetails.NotUsed7, cellToDel.NotUsed7)
FROM 
    _PS_SCHEDULES_Details_CellInfo2 AS PSDetails
INNER JOIN 
    (SELECT
         CASE WHEN cellToDel.WeekDay = 1 THEN NULL END as EmpNumDay1,
         CASE WHEN cellToDel.WeekDay = 2 THEN NULL END as EmpNumDay2,
         CASE WHEN cellToDel.WeekDay = 3 THEN NULL END as EmpNumDay3,
         CASE WHEN cellToDel.WeekDay = 4 THEN NULL END as EmpNumDay4,
         CASE WHEN cellToDel.WeekDay = 5 THEN NULL END as EmpNumDay5,
         CASE WHEN cellToDel.WeekDay = 6 THEN NULL END as EmpNumDay6,
         CASE WHEN cellToDel.WeekDay = 7 THEN NULL END as EmpNumDay7,
         MAX(CASE WHEN cellToDel.WeekDay = 1 THEN 1 END) as NotUsed1,
         MAX(CASE WHEN cellToDel.WeekDay = 2 THEN 1 END) as NotUsed2,
         MAX(CASE WHEN cellToDel.WeekDay = 3 THEN 1 END) as NotUsed3,
         MAX(CASE WHEN cellToDel.WeekDay = 4 THEN 1 END) as NotUsed4,
         MAX(CASE WHEN cellToDel.WeekDay = 5 THEN 1 END) as NotUsed5,
         MAX(CASE WHEN cellToDel.WeekDay = 6 THEN 1 END) as NotUsed6,
         MAX(CASE WHEN cellToDel.WeekDay = 7 THEN 1 END) as NotUsed7 
     FROM 
         #cellInfoToDelete cellToDel) cellToDel ON PSDetails.ID = cellToDel.SchedID

有许多行代码如下所示：

CASE WHEN cellToDel.WeekDay = 1 THEN NULL END

但错误指出，“case规范中至少有一个结果表达式必须是null常量以外的表达式”。对于所有这样的行，您提供的唯一结果表达式是 NULL ，这是“空常量”。所以你还没有达到一个有效的 case 表情。
我想你想要这个：

UPDATE   ps        
SET      EmpNumDay1 = CASE WHEN c.WeekDay = 1 THEN NULL ELSE EmpNumDay1 END,
         EmpNumDay2 = CASE WHEN c.WeekDay = 2 THEN NULL ELSE EmpNumDay2 END,
         EmpNumDay3 = CASE WHEN c.WeekDay = 3 THEN NULL ELSE EmpNumDay3 END,
         EmpNumDay4 = CASE WHEN c.WeekDay = 4 THEN NULL ELSE EmpNumDay4 END,
         EmpNumDay5 = CASE WHEN c.WeekDay = 5 THEN NULL ELSE EmpNumDay5 END,
         EmpNumDay6 = CASE WHEN c.WeekDay = 6 THEN NULL ELSE EmpNumDay6 END,
         EmpNumDay7 = CASE WHEN c.WeekDay = 7 THEN NULL ELSE EmpNumDay7 END,
         NotUsed1   = CASE WHEN c.WeekDay = 1 THEN 1 ELSE NotUsed1 END,
         NotUsed2   = CASE WHEN c.WeekDay = 2 THEN 1 ELSE NotUsed2 END,
         NotUsed3   = CASE WHEN c.WeekDay = 3 THEN 1 ELSE NotUsed3 END,
         NotUsed4   = CASE WHEN c.WeekDay = 4 THEN 1 ELSE NotUsed4 END,
         NotUsed5   = CASE WHEN c.WeekDay = 5 THEN 1 ELSE NotUsed5 END,
         NotUsed6   = CASE WHEN c.WeekDay = 6 THEN 1 ELSE NotUsed6 END,
         NotUsed7   = CASE WHEN c.WeekDay = 7 THEN 1 ELSE NotUsed7 END
from     _PS_SCHEDULES_Details_CellInfo2 ps
join     #cellInfoToDelete               c  on  ps.ID = c.SchedID

但我不知道你在说什么 max() 函数正在尝试执行。

调整我的新脚本使列无效，并且不返回正确的表达式

1条答案

相关问题

热门标签

最新问答