sql - 为什么我会收到错误消息 "TOP or FETCH clause contains an invalid value"？

Question

我正在使用 SQL Server 2012 并且我有以下 T-SQL 查询。但是，它在执行时给我一条错误消息。

错误信息是:

Msg 1014, Level 15, State 1, Line 2
A TOP or FETCH clause contains an invalid value.

我的 T-SQL 查询如下:

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT 1 FROM N,N a,N b,N c,N d)

INSERT INTO Allot4

SELECT
       b.Date as [TDate],
       Season,
       RN,
       TAProfileID,
       TOName,
       Market,
       RoomType,
       Property,     
       TOType

    FROM Allot3 a
    CROSS APPLY
    ( 
      SELECT top(datediff(d,Datefrom,case when DateTo >= DateFrom
                 then dateadd(d, 1, DateTo) else DateFrom end))
        DATEADD(d,row_number()over(order by 1/0)-1, DateFrom) Date
      FROM tally
    ) b

这里有什么问题，我该如何解决？

Answer 1

当我们在 TOP 子句中指定表达式时，我们必须确保表达式在计算时不应返回 NULL 。我在您的声明中添加了 ISNULL -

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT 1 FROM N,N a,N b,N c,N d)

INSERT INTO Allot4

SELECT
       b.Date as [TDate],
       Season,
       RN,
       TAProfileID,
       TOName,
       Market,
       RoomType,
       Property,     
       TOType

    FROM Allot3 a
    CROSS APPLY
    ( 
      SELECT top(isnull(datediff(d,Datefrom,case when DateTo >= DateFrom
                 then dateadd(d, 1, DateTo) else DateFrom end)),0)
        DATEADD(d,row_number()over(order by 1/0)-1, DateFrom) Date
      FROM tally
    ) b

我们可以从以下位置阅读有关 TOP 子句的更多信息 -
https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql