SQL，在关键字" FROM "附近的CASE语句语法错误

I am trying to get the below code to work, individually the two pieces of code (in the WHEN part and the ELSE part) work but when used in this CASE statement I get an error

我正在尝试让下面的代码工作起来，这两段代码(在WHEN部分和ELSE部分)单独工作，但是在本例语句中使用时，我得到了一个错误

"Incorrect syntax near 'CAST', expected 'AS'." error.

“在'CAST'附近的不正确语法，预期'AS'.”错误。

Basically if the WHEN statements code is equals to or greater than 24 then use the THEN statement if its is under 24 then use the ELSE statement.

基本上，如果WHEN语句代码等于或大于24，那么使用then语句，如果它小于24，那么使用ELSE语句。

I cannot seem to get this to work after trying for several hours any indication as to where I am going wrong would be greatly appreciated.

在尝试了几个小时之后，我似乎无法让它发挥作用。如果有任何迹象表明我哪里做错了，我将非常感激。

SELECT CASE 
        WHEN 
            (convert(float,datediff(mi, start_work, end_work))/60) >= '24'
        THEN
            (convert(float,datediff(mi, start_work, end_work))/60)
        ELSE
            (CAST(convert(varchar(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114) 
            * 60 + RIGHT (convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114),
            CASE WHEN CHARINDEX(':',convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) > 0
            THEN LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))-3
            ELSE LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))
            END) AS decimal) / 60
FROM  NDB.dbo.statusa 
INNER JOIN NDB.dbo.details ON statusa.vkey = details.vkey
INNER JOIN NDB.dbo.chegu ON statusa.ckey = NDB.dbo.chegu.gkey
WHERE start_time!= end_time AND string1 = Visit_Id and NDB.dbo.chegu.name = 'loft'
     AS [Working]

9 个解决方案

#1

you need to close your case statement

您需要关闭您的case语句。

 case when ... then ... else ... end

#2

There should be an END before the FROM clause and you should also remove( before CAST.

在FROM子句之前应该有一个结束，您也应该删除(在CAST之前)。

#3

There arent closing parenthesis.

没有关闭括号。

SELECT CASE 
    WHEN 
        (convert(float,datediff(mi, start_work, end_work))/60) >= '24'
    THEN
        (convert(float,datediff(mi, start_work, end_work))/60)
    ELSE
        (CAST(convert(varchar(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114) 
        * 60 + RIGHT (convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114),
        CASE WHEN CHARINDEX(':',convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) > 0
        THEN LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))-3
        ELSE LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))
        END) AS decimal) / 60)

#4

Unclosed parentheses and a case statement needed an END.

未闭圆括号和case语句需要结束。

SELECT CASE 
            WHEN (convert(FLOAT, datediff(mi, start_work, end_work)) / 60) >= '24'
                THEN (convert(FLOAT, datediff(mi, start_work, end_work)) / 60)
            ELSE CAST(convert(VARCHAR(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114) * 60 
                 + RIGHT(
                        convert(VARCHAR(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114), 
                        CASE 
                            WHEN CHARINDEX(':', convert(VARCHAR(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) > 0
                                THEN LEN(convert(VARCHAR(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) - 3
                            ELSE LEN(convert(VARCHAR(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))
                        END
                        ) AS DECIMAL) / 60
        END

FROM NDB.dbo.statusa
INNER JOIN NDB.dbo.details
    ON statusa.vkey = details.vkey
INNER JOIN NDB.dbo.chegu
    ON statusa.ckey = NDB.dbo.chegu.gkey
WHERE start_time != end_time
    AND string1 = Visit_Id
    AND NDB.dbo.chegu.NAME = 'loft' AS [Working]

#5

I realized this answer is too late for the reward. But your script is WAY too long. This will do exactly the same as you are trying to achieve, fixing the error in your code, is just patching bad code:

我意识到这个答案对奖励来说太晚了。但是你的剧本太长了。这与您试图实现的完全相同，修复代码中的错误，只是修补错误代码:

SELECT 
  datediff(mi, 0, end_time - start_time)/60.0%24
FROM  
 ....

#6

Two basic formulations for CASE expression 1) Simple CASE expressions A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalence. If these expressions are equivalent, the expression in the THEN clause will be returned.

1)简单的CASE表达式一个简单的CASE表达式针对多个值检查一个表达式。在SELECT语句中，一个简单的CASE表达式只允许进行相等检查;没有进行其他比较。一个简单的CASE表达式通过将第一个表达式与每个WHEN子句中的表达式进行比较来获得等价性。如果这些表达式是等价的，则将返回THEN子句中的表达式。

2) Searched CASE expressions A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expression is shown below:

2)搜索的CASE表达式一个搜索的CASE表达式允许比较运算符，以及在每个布尔表达式之间的使用和/或。简单的CASE表达式只检查等价的值，不能包含布尔表达式。搜索情况表达式的基本语法如下:

阅读更多:http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/

#7

There are 2 errors in the code.
First, you have an extra parenthesis in front of CAST which is not needed (and isn't closed ever). Change

代码中有两个错误。首先，在CAST之前有一个额外的括号，这是不需要的(而且从来没有关闭过)。改变

(CAST(convert(varchar(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)

来

CAST(convert(varchar(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)

Second: Add END at the close of the CASE statement before the FROM keyword (as @gefei points out above as well) . Your final code should look like the following:

第二:在CASE语句的结尾添加END，然后是FROM关键字(正如@gefei在上面指出的那样)。您的最终代码应该如下所示:

SELECT CASE 
        WHEN 
            (convert(float,datediff(mi, start_work, end_work))/60) >= '24'
        THEN
            (convert(float,datediff(mi, start_work, end_work))/60)
        ELSE
            CAST(convert(varchar(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114) 
            * 60 + RIGHT (convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114),
            CASE WHEN CHARINDEX(':',convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) > 0
            THEN LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))-3
            ELSE LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))
            END) AS decimal) / 60
        END
FROM  NDB.dbo.statusa 
INNER JOIN NDB.dbo.details ON statusa.vkey = details.vkey
INNER JOIN NDB.dbo.chegu ON statusa.ckey = NDB.dbo.chegu.gkey
WHERE start_time!= end_time AND string1 = Visit_Id and NDB.dbo.chegu.name = 'loft'
     AS [Working]

#8

The RIGHT() function does not have a second parameter

右()函数没有第二个参数

RIGHT (convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114),

or: you missed the ending parenthesis behind AS decimal

或者:你漏掉了后面的小数点

END ) AS decimal ) ) / 60

#9

Here's the corrected version of your code:

这是你的代码的更正版本:

SELECT CASE 
        WHEN 
            (convert(float,datediff(mi, start_work, end_work))/60) >= '24'
        THEN
            (convert(float,datediff(mi, start_work, end_work))/60)
        ELSE
            (CAST(convert(varchar(2), dateadd(minute, datediff(minute, start_time, end_time), 0), 114) 
            * 60 + RIGHT (convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114),
            CASE WHEN CHARINDEX(':',convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114)) > 0
            THEN LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))-3
            ELSE LEN(convert(varchar(5), dateadd(minute, datediff(minute, start_time, end_time), 0), 114))
            END) AS decimal) / 60 
        END
AS [Working]
FROM  NDB.dbo.statusa  INNER JOIN NDB.dbo.details ON statusa.vkey = details.vkey INNER JOIN NDB.dbo.chegu ON statusa.ckey = NDB.dbo.chegu.gkey WHERE start_time!= end_time AND string1 = Visit_Id and NDB.dbo.chegu.name = 'loft'

Basically, you had a missing END in the outer SELECT..CASE statement and the selected column alias [Working] was mistakenly put at the end of the query rather than in the select clause of the query.

基本上，您在外部选择中缺少了一个端点。CASE语句和选择的列别名[Working]被错误地放在查询的末尾，而不是在查询的select子句中。

#1