locked
How to solve TOP clause contains an invalid value RRS feed

  • Question

  • I have a TOP clause in one of my SELECT statement. When I have data, I do not have error in my TOP clause. When I do not have data returned from my query, I received TOP clause contains an invalid value error.

    How do I resolve this error?  The error I believe is in SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1)

    My sample query is below:

    Thank you.

    DECLARE @BeginDate DATETIME
    DECLARE @EndDate DATETIME
    DECLARE @Status VARCHAR(MAX)
     
     
    SET @BeginDate = '1-4-2017'
    SET @EndDate = '11-4-2017'
    SET @Status = 'Pending' 
    
    ;WITH CTE_AmountRequested_VL AS
    (SELECT 
           vl.LoanID
           ,vl.AmountRequested 
                  FROM VehicleLoans vl 
    )
     
     
    ,CTE_FinalQuery AS
    (
    SELECT 
     t.ID, 
     t.LoanNumber,
     t.LoanStatus
    
    FROM CTE_PrepareQuery t
    CROSS APPLY (
    SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1) 
      [BusinessDate] = CAST(DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,CAST(StartDt AS DATE)) AS DATETIME)
    FROM (VALUES (0),(0),(0),(0),(0),(0),(0)) d (n)
    ) x
    CROSS APPLY (VALUES 
     (1, CAST('08:30' AS DATETIME), CAST('21:00' AS DATETIME)),
    (2, '08:30', '21:00'),
    (3, '08:30', '21:00'),
    (4, '08:30', '21:00'),
    (5, '08:30', '21:00'),
    (6, '08:30', '21:00'),
    (7, '11:00', '19:00') -- Sunday
    ) y ([DayOfWeek], BusinessDayStartHour, BusinessDayFinishHour)
    CROSS APPLY (
    SELECT 
      ActualDayStart = BusinessDate + CASE WHEN BusinessDate = CAST(StartDt AS DATE) THEN CAST(CAST(StartDt AS TIME(0)) AS DATETIME) ELSE BusinessDayStartHour END,
      ActualDayFinish = BusinessDate + CASE WHEN BusinessDate = CAST(EndDt AS DATE) THEN CAST(CAST(EndDt AS TIME(0)) AS DATETIME) ELSE BusinessDayFinishHour END
    ) z
     
    WHERE y.[DayOfWeek] = 1+DATEDIFF(DAY,0,BusinessDate)%7 -- settings-agnostic day of week matching
     
    GROUP BY 
    ID, 
    LoanNumber,
    
     
    )
     
     
    SELECT
    ID
    ,LoanNumber
    ,LoanStatus
    ,LoanType
    ,StartDt
    ,EndDt
    ,AmountRequested
    ,TotalDisbursedAmount
    ,loanofficer = ISNULL(loanofficer,'UNCLAIMED')
    ,MinutesWorked
    ,FinalHoursWorked = (MinutesWorked/60)
    ,FinalHoursWorked = REPLACE(STR((MinutesWorked/60),2),' ','0')
    ,FinalMinutesWorked = MinutesWorked%60 
    
           FROM CTE_FinalQuery
     
     
    ORDER BY 
    ID, 
    LoanNumber
    
    

    Tuesday, November 7, 2017 2:49 AM

Answers

  • Hi Garin,

    Since you are using DATEDIFF() function in TOP clause, the values in StartDt and EndDt should always be date type data. And as you said there will be null values. For sure, the TOP clause will return an error.

    I can reproduce your issue, please refer to following sample.

    create table #test
    (
    col int,
    StartDt date,
    EndDt date
    )
    
    insert into #test values
    (1,null,null)
    
    select *
    from #test
    
    cross apply ( select TOP(DATEDIFF(DAY,StartDt,EndDt)+1) col from #test) t
    
    drop table #test

    I think you should consider of the situation about Null values in StartDt and EndDt. Maybe when StartDt and EndDt are Null, you can give them a specific value. Like:

    select *
    from #test
    cross apply ( select TOP(DATEDIFF(DAY,Cast(case when StartDt is null then '1900-01-01' else StartDt end as date),Cast(case when EndDt is null then '1900-01-01' else EndDt end as date))+1) col from #test) t

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Xi Jin Tuesday, November 7, 2017 6:03 AM
    • Proposed as answer by Xi Jin Monday, November 13, 2017 9:40 AM
    • Marked as answer by GarinTiger Tuesday, January 23, 2018 1:27 AM
    Tuesday, November 7, 2017 5:57 AM
  • Maybe this?

    TOP(isnull(DATEDIFF(DAY,StartDt,EndDt), 0) +1)

    It depends on what you want to happen when any of StartDt and EndDt is null.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by GarinTiger Tuesday, January 23, 2018 1:27 AM
    Tuesday, November 7, 2017 9:57 PM

All replies

  • Is this the full query?

    I cant see the definition of CTE_PrepareQuery   in it


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 7, 2017 5:34 AM
  • Hi Garin,

    Since you are using DATEDIFF() function in TOP clause, the values in StartDt and EndDt should always be date type data. And as you said there will be null values. For sure, the TOP clause will return an error.

    I can reproduce your issue, please refer to following sample.

    create table #test
    (
    col int,
    StartDt date,
    EndDt date
    )
    
    insert into #test values
    (1,null,null)
    
    select *
    from #test
    
    cross apply ( select TOP(DATEDIFF(DAY,StartDt,EndDt)+1) col from #test) t
    
    drop table #test

    I think you should consider of the situation about Null values in StartDt and EndDt. Maybe when StartDt and EndDt are Null, you can give them a specific value. Like:

    select *
    from #test
    cross apply ( select TOP(DATEDIFF(DAY,Cast(case when StartDt is null then '1900-01-01' else StartDt end as date),Cast(case when EndDt is null then '1900-01-01' else EndDt end as date))+1) col from #test) t

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Xi Jin Tuesday, November 7, 2017 6:03 AM
    • Proposed as answer by Xi Jin Monday, November 13, 2017 9:40 AM
    • Marked as answer by GarinTiger Tuesday, January 23, 2018 1:27 AM
    Tuesday, November 7, 2017 5:57 AM
  • Maybe this?

    TOP(isnull(DATEDIFF(DAY,StartDt,EndDt), 0) +1)

    It depends on what you want to happen when any of StartDt and EndDt is null.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by GarinTiger Tuesday, January 23, 2018 1:27 AM
    Tuesday, November 7, 2017 9:57 PM