Why am I getting the error message "TOP or FETCH clause contains an invalid value"?

Question

I am using SQL Server 2012 and I have the following T-SQL query. However, it is giving me an error message when executed.

The error message is :

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

My T-SQL query stands as follows:

;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

What's wrong here and how do I solve it?

Answer 1

When we specify expression inside TOP clause, we have to make sure that expression when evaluated should not return NULL. I have added ISNULL inside your statement -

;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

We can read more about TOP clause from - https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql