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