i have this sql query for mysql that works well. the query finds in 2 tables wich is the max numeric value from both and returns the value good in mysql, i have to do the same query in sql server but tells me an error syntax error near cast, expects as
the query is the next :
SELECT MAX(CAST(RIGHT(nrt, 5) AS UNSIGNED))
FROM
(
SELECT nrt from asp where nrt != ' ' and nrt is not null
UNION ALL
SELECT nrt from asp_historic where nrt != ' ' and nrt is not null
) as subQuery
there is a way to do that the this query runs?
I would start with no conversion at all:
SELECT MAX(RIGHT(nrt, 5)) FROM asp WHERE nrt <> ' ' and nrt is not null
UNION ALL
SELECT nrt FROM asp_historic WHERE nrt M< ' ' and nrt is not null
This assumes that nrt
has at least 5 characters.
If not, convert to an integer:
SELECT CAST(MAX(RIGHT(nrt, 5)) as int) FROM asp WHERE nrt <> ' ' and nrt is not null
UNION ALL
SELECT nrt FROM asp_historic WHERE nrt <> ' ' and nrt is not null
EDIT:
This question appears to be about MySQLs implicit conversions. I might suggestion:
SELECT MAX(CAST(LEFT(nrt + ' ', PATINDEX(nrt, '%[^0-9]%') - 1) as int))
. . .
Below is the same code for SQL Server ...
SELECT MAX(RIGHT(ABS(nrt), 5))
FROM
(
SELECT nrt from asp where nrt <> ' ' and nrt is not null
UNION ALL
SELECT nrt from asp_historic where nrt <> ' ' and nrt is not null
) as subQuery
Are you looking for nrt != ' ' and nrt is not null
?
I think it should be OR
condition instead of AND
.