此 SP 中“SUM"附近的“语法不正确"在哪里? [英] Where is the 'Incorrect syntax near 'SUM'' in this SP?

问题描述

当我尝试在此 MS SQL Server 存储过程中执行它时，我收到消息 102，级别 15，状态 1，过程duckbilledPlatypi，'SUM' 附近的第 21 行语法不正确."Visual Studio(在服务器资源管理器中创建之后):

I get, "Msg 102, Level 15, State 1, Procedure duckbilledPlatypi, Line 21 Incorrect syntax near 'SUM'." with this MS SQL Server Stored Procedure when I try to execute it in Visual Studio (after creating it from within Server Explorer):

CREATE PROCEDURE [dbo].[duckbilledPlatypi]
    @Unit varchar(25),
    @BegDate datetime,
    @EndDate datetime
AS

DECLARE 
@Week1End datetime,
@Week2begin datetime

    Select  Description,
    @BegDate  BegDate,
    @Week1End Week1End,
    @Week1End Week2Begin,
    @EndDate EndDate,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) Week1Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) Week2Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) Week1Price,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) -
   SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) UsageVariance
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance,
    (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
    / SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PercentageOfPriceVariance
    From    InvoiceDetail Ind
    Where   Ind.Unit = @Unit
    AND @Ind.InvoiceDate BETWEEN @BegDate AND @EndDate

问题所在:

SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)  -

在上下文中，整个语句部分是:

In context, the entire statement portion is:

SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance,

?

我想也许我需要将语句封装在另一对括号中，如下所示:

I thought maybe I needed to encase the statement in another pair of parens, like so:

*(SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)  - 
 SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END)) PriceVariance,*

...但我仍然得到完全相同的错误信息.

...but I still get the same exact err msg with that.

有了这个(HoneyBadger 的回答):

With this (HoneyBadger's answer):

CREATE PROCEDURE [dbo].[variancePriceByProductWithPriceChangePercentage]
    @Unit varchar(25),
    @BegDate datetime,
    @EndDate datetime
AS

DECLARE 
@Week1End datetime,
@Week2begin datetime

    Select  Description,
    @BegDate  BegDate,
    @Week1End Week1End,
    @Week1End Week2Begin,
    @EndDate EndDate,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) 
Week1Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) 
Week2Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
Week1Price,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) -
   SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) 
UsageVariance,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
PriceVariance,
    (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
    / SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
PercentageOfPriceVariance
    From    InvoiceDetail Ind
    Where   Ind.Unit = @Unit
    AND Ind.InvoiceDate BETWEEN @BegDate AND @EndDate
    Group By Description,
    @BegDate,
    @Week1End,
    @Week1End,
    @EndDate

我得到以下指尖:

消息 164，级别 15，状态 1，过程差异价格ByProductWithPriceChangePercentage，第 30 行每个 GROUP BY 表达式必须包含至少一列不是外部引用.消息 207，级别 16，状态 1，过程差异价格ByProductWithPriceChangePercentage，第 16 行无效的列名用法".消息 207，级别 16，状态 1，过程差异价格ByProductWithPriceChangePercentage，第 17 行无效的列名用法".消息 207，级别 16，状态 1，过程差异价格ByProductWithPriceChangePercentage，第 20 行无效的列名用法".消息 207，级别 16，状态 1，过程差异价格ByProductWithPriceChangePercentage，第 21 行无效的列名用法".消息 207，级别 16，状态 1，过程差异价格ByProductWithPriceChangePercentage，第 23 行列名用法"无效.

推荐答案

就像我在评论中所说的，你应该添加一个group by.其他人指出您缺少逗号，我也补充说:

Like I said in the comment, you should add a group by. Others have pointed out you had a missing comma, I added that as well:

    Select  Description,
    @BegDate  BegDate,
    @Week1End Week1End,
    @Week1End Week2Begin,
    @EndDate EndDate,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) Week1Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) Week2Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) Week1Price,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) -
   SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) UsageVariance,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance,
    (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
    / SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PercentageOfPriceVariance
    From    InvoiceDetail Ind
    Where   Ind.Unit = @Unit
    AND Ind.InvoiceDate BETWEEN @BegDate AND @EndDate
    Group By Description

显然group by中的变量是错误的

这篇关于此 SP 中“SUM"附近的“语法不正确"在哪里?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！