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]
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.
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 :-/).
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.
you need to close your case statement
case when ... then ... else ... end
There should be an END before the FROM clause and you should also remove( before CAST.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With