SQL Server 滚动范围的条件中关键字“or.”附近的语法不正确

yqkkidmi 于 2023-01-20 发布在其他

关注(0)|答案(2)|浏览(192)

我展示了两个嵌套的select语句，它们在一个更大的select语句中。实际上有10个嵌套的select语句，它们对产品的销售/发出/使用进行求和，条件是匹配仓库和项目，然后是今年、去年和两年前的日期值范围。没有datetime列。确定日期的列是[Year]和[Month]列。我必须从提到的日期条件中得到总和。
我用于静态听力和YTD的所有语句都工作正常，但是当我使用'or'语法来表示"获取从2年前月份〉=到我当前月份的所有内容"时，它工作正常，但是当我添加或来包括从今年以及月份〈=当前月份的所有内容时，我得到了OR错误。此外，由于某种原因，AS现在会导致shame语法错误，但使用关键字AS（关键字"as"附近的语法不正确）时，别名中的下划线是否会导致：
关键字"as"附近的语法不正确
错误？

(SELECT SUM(sold) + sum(Issued)AS Expr1
     FROM dbo.M_NewtrnYM 
        WHERE (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber) AND (YEAR = YEAR(CURRENT_TIMESTAMP) - 1) 
            and (CAST([Month] as Integer) >=  Month(CURRENT_TIMESTAMP))) or (YEAR = YEAR(CURRENT_TIMESTAMP)) and (CAST([Month] as Integer) <=  Month(CURRENT_TIMESTAMP))) AS Twelvemth_usage, 

(SELECT SUM(sold) + sum(Issued)AS Expr1
     FROM dbo.M_NewtrnYM 
        WHERE (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber) AND (YEAR = year(dateadd(m, -6, getdate() - datepart(d, getdate()) + 1))) 
            and (CAST([Month] as Integer) >=   month(dateadd(m, -6, getdate() - datepart(d, getdate()) + 1)) or (YEAR = YEAR(CURRENT_TIMESTAMP)) and (CAST([Month] as Integer) <=  Month(CURRENT_TIMESTAMP))) AS sixmth_usage,

sql-server

来源：https://stackoverflow.com/questions/75155254/incorrect-syntax-near-the-keyword-or-in-criteria-for-a-rolling-range

2条答案

按热度按时间

olmpazwi1#

末尾缺少一个右括号：

(

    SELECT SUM(sold) + sum(Issued)AS Expr1
    FROM dbo.M_NewtrnYM 
    WHERE 
        (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) 
        AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber) 
        AND (YEAR = year(dateadd(m, -6, getdate() - datepart(d, getdate()) + 1))) 
        and (CAST([Month] as Integer) >=   month(dateadd(m, -6, getdate() - datepart(d, getdate()) + 1)) or (YEAR = YEAR(CURRENT_TIMESTAMP)) and (CAST([Month] as Integer) <=  Month(CURRENT_TIMESTAMP)))
)

另外，您可以删除AND条件上的一些括号，并查看最后一个条件，您确定这是您想要的吗？

(

    SELECT SUM(sold) + sum(Issued)AS Expr1
    FROM dbo.M_NewtrnYM 
    WHERE 
        WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse
        AND ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber
        AND YEAR = year(dateadd(m, -6, getdate() - datepart(d, getdate()) + 1))
        and 
        (
            CAST([Month] as Integer) >=   month(dateadd(m, -6, getdate() - datepart(d, getdate()) + 1)) 
            or 
            YEAR = YEAR(CURRENT_TIMESTAMP) 
            and 
            CAST([Month] as Integer) <=  Month(CURRENT_TIMESTAMP)
        )
)

您有X或Y和Z。如果X为真，则不会忽略Z。

赞(0）回复(0）举报 2023-01-20

e3bfsja22#

(SELECT SUM(sold) AS Expr1
 FROM dbo.M_NewtrnYM 
    WHERE (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) 
        AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber) 
        AND (Year = YEAR(CURRENT_TIMESTAMP) - 1) 
         AND (CAST(Month AS Integer) >= Month(CURRENT_TIMESTAMP)) 
 OR (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) 
      AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber)  
       and(Year = YEAR(CURRENT_TIMESTAMP)) 
          AND (CAST(Month AS Integer) <= Month(CURRENT_TIMESTAMP))) 
            AS RollingTwelvemth_sale

赞(0）回复(0）举报 2023-01-20

我来回答

SQL Server 滚动范围的条件中关键字“or.”附近的语法不正确

2条答案

相关问题

热门标签

最新问答