CTE语句中关键字选项附近的语法不正确 [英] Incorrect Syntax Near Keyword 'OPTION' in CTE Statement

问题描述

在尝试在SQL Server2008中保存视图时，我收到一条错误的关键字‘选项’附近的语法错误。我正在尝试将MAXRECURSION选项添加到我的公用表表达式的末尾。我见过的所有例子和我以前创建的CTE都不介意CTE结尾处的"Option(MAXRECURSION0)"。

有人知道我为什么会收到这个错误吗？CTE在没有OPTION子句的情况下工作，尽管它达到了最大递归数(100)。

WITH CTE AS
(
  SELECT
    CDay,
    InvAcct,
    BuyerCode,
    PartNumber,
    ROP,
    ROP_ROQ,
    DailyDemand,
    StartingInvQty,
    SchedDeliveryQty,
    CAST(StartingInvQty - DailyDemand/2.0 AS decimal(18,4)) AS ProjInvQty
  FROM
    qryInventorySimulation
  WHERE
    MBC = 'B' AND
    CDay = CAST(CAST(GETDATE()AS date) as datetime)

  UNION ALL

  SELECT
    qryInventorySimulation.CDay,
    qryInventorySimulation.InvAcct,
    qryInventorySimulation.BuyerCode,
    qryInventorySimulation.PartNumber,
    qryInventorySimulation.ROP,
    qryInventorySimulation.ROP_ROQ,
    qryInventorySimulation.DailyDemand,
    qryInventorySimulation.StartingInvQty,
    qryInventorySimulation.SchedDeliveryQty,
    CAST(CTE.ProjInvQty + qryInventorySimulation.SchedDeliveryQty - qryInventorySimulation.DailyDemand AS decimal(18,4)) AS ProjInvQty
  FROM
    qryInventorySimulation INNER JOIN CTE ON qryInventorySimulation.InvAcct = CTE.InvAcct AND qryInventorySimulation.PartNumber = CTE.PartNumber AND qryInventorySimulation.CDay = DATEADD(d,1,CTE.CDay)
  WHERE
    qryInventorySimulation.CDay <= DATEADD(d,120,GETDATE())
)

SELECT * FROM CTE
OPTION (MAXRECURSION 0);

推荐答案

您不能在视图中应用此选项。您需要将其应用于调用该视图的查询。例如

CREATE VIEW dbo.V
AS
    WITH CTE AS
    (   SELECT 1 AS A 
        UNION ALL
        SELECT A + 1
        FROM    CTE
        WHERE   A < 50
    )
    SELECT  *
    FROM    CTE;
GO
SELECT  *
FROM    dbo.V
OPTION (MAXRECURSION 0);

如果您认为一个视图更像是一个存储的子查询而不是一个存储的查询(是的，它可以自己调用，但不是必须的)，并且记住它的定义被扩展到主查询中(除非您正在使用NOEXPAND--无论如何您不能在包含递归CTE的视图上使用它)，那么在本质上您正在尝试这样做：

WITH RecursiveCTE AS (...)
SELECT *
FROM T
    INNER JOIN 
    (   SELECT  *
        FROM    RecursiveCTE
        OPTION (MAXRECURSION 0)
    ) c
        ON c.SomeField = T.SomeField;

而正确的语法应该是：

WITH RecursiveCTE AS (...)
SELECT *
FROM T
    INNER JOIN 
    (   SELECT  *
        FROM    RecursiveCTE
    ) c
        ON c.SomeField = T.SomeField;
OPTION (MAXRECURSION 0)

这篇关于CTE语句中关键字选项附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！