Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL, Incorrect syntax on CASE statement near the keyword 'FROM'

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

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

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.

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]
like image 716
Matt Avatar asked Aug 01 '13 10:08

Matt


People also ask

What is incorrect syntax near in SQL?

When executing a query in SQL and the editor throws back this error: Incorrect syntax near …'' That typically means you have used the wrong syntax for the query. This happens mostly when someone switched from one relational database to another relational database, from MySQL to MS SQL Server for example.

Can we use CASE IN FROM clause?

No, you can't pick a table to query using a CASE statement. CASE statements only go within expressions, such as for a column's value or as part of your WHERE expression. As "long handed" as this is, it's the approach I'd take (but no votes left :-/).

Can we use CASE statement in WHERE clause in SQL?

Another way to use the Case Statement is within the WHERE clause. There, it may be utilized to alter the data fetched by a query based on a condition. Within that context, the Case Statement is ideally suited to both static queries, as well as dynamic ones, such as those that you would find inside a stored procedure.


2 Answers

you need to close your case statement

 case when ... then ... else ... end
like image 160
gefei Avatar answered Nov 08 '22 04:11

gefei


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

like image 37
sickUnit Avatar answered Nov 08 '22 06:11

sickUnit