此 SP 中的“SUM"附近的“语法不正确"在哪里?

2022-01-09 00:00:00 sum sql-server stored-procedures

当我尝试在此 MS SQL Server 存储过程中执行它时，我得到消息 102，级别 15，状态 1，过程 duckbilledPlatypi，第 21 行，'SUM' 附近的语法不正确."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

我得到以下手指:

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

推荐答案

就像我在评论中说的，你应该添加一个 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

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

相关文章