if statement in sql server

Question

i am getting the following error:

Incorrect syntax near 'cast', expected 'AS'.

on this line:

  use SalesDWH
go
if (select isnumeric(result))=1
begin
select  max(cast(result) as decimal(10,2)) from testresults 

end

testresults table contains about 21 million rows of data.

what am i doing wrong? thanks so much.

thank you everyone for you rhelp.

i've changed the code to:

 use SalesDWH
    go
    if (select isnumeric(result))=1
    begin
    select  max(cast(result as decimal(10,2))) from testresults

    end

and now i am getting error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'result'.

it's definitely a valid column

i took kris' suggestion and did this:

 use SalesDWH
    go
    SELECT MAX( CAST( [Result] AS DECIMAL(9,2) ) )
FROM [testresults]
WHERE ISNUMERIC( [Result] ) = 1

    and dbo.isReallyNumeric([Result]) = 1 
    and dbo.isReallyInteger([Result]) = 1 

the functions are here http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

and the result that i got was NULL!!!!!

i need a numeric result. what am i doing wrong?

here is a sample of the data:

625857  AMPH-AMPHETAMINES   357.1   EBB74CF9-D12D-4FBC-917F-91D9DAC169F3
625858  AMP_C-Amphetamine   NEGATIVE    EBB74CF9-D12D-4FBC-917F-91D9DAC169F3
625859  BARB-BARBITURATES   7.1 EBB74CF9-D12D-4FBC-917F-91D9DAC169F3
625860  BENZ-BENZODIAZEPINES    1.2 EBB74CF9-D12D-4FBC-917F-91D9DAC169F3
625861  COCN-COCAINE METABOLITES    -105.5  EBB74CF9-D12D-4FBC-917F-91D9DAC169F3
625862  CR-CREATININE (CHEMICAL)    57.8    EBB74CF9-D12D-4FBC-917F-91D9DAC169F3
625863  ETOH-ETHANOL    134.5   EBB74CF9-D12D-4FBC-917F-91D9DAC169F3
625864  METAMP_C-Methamphetamine    NEGATIVE    EBB74CF9-D12D-4FBC-917F-91D9DAC169F3
625865  METD-METHADONE  -32.3   EBB74CF9-D12D-4FBC-917F-91D9DAC169F3

thank you for all your help. i think i am getting closer:

 select MAX(cast(char_value as decimal(10,2))) from
        (SELECT 
    Char_Number = CASE 
        WHEN id <= 255 THEN RTRIM(id) 
        ELSE '-' END, 
    Char_Value = RTRIM(CASE 
        WHEN id <= 255 THEN CHAR(id) 
        ELSE result END), 
    is_numeric = ISNUMERIC(result), 
    is_really_numeric = dbo.isReallyNumeric(result), 
    is_really_integer = dbo.isReallyInteger(result) 
FROM 
    testresults 
WHERE 
    ISNUMERIC(result) = 1 
    OR dbo.isReallyNumeric(result) = 1 
    OR dbo.isReallyInteger(result) = 1 
 )

    where is_really_numeric=1

but i am getting this error:

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'where'.

Answer 1

If you are still getting the error, just give an alias to your inner table.

select MAX(cast(char_value as decimal(10,2))) from
        (SELECT 
    Char_Number = CASE 
        WHEN id <= 255 THEN RTRIM(id) 
        ELSE '-' END, 
    Char_Value = RTRIM(CASE 
        WHEN id <= 255 THEN CHAR(id) 
        ELSE result END), 
    is_numeric = ISNUMERIC(result), 
    is_really_numeric = dbo.isReallyNumeric(result), 
    is_really_integer = dbo.isReallyInteger(result) 
FROM 
    testresults 
WHERE 
    ISNUMERIC(result) = 1 
    OR dbo.isReallyNumeric(result) = 1 
    OR dbo.isReallyInteger(result) = 1 
 ) myInnerTable

    where is_really_numeric=1

Answer 2

Try this one

use SalesDWH
go
SELECT MAX(CAST(Result AS DECIMAL(10,2))) FROM testresults WHERE isnumeric(result)=1

But if you get NULL as a result even with this:

and dbo.isReallyNumeric([Result]) = 1 
and dbo.isReallyInteger([Result]) = 1 

then this may be the actual result - there is no any numberic values in the column

OR

the really numberic values of column are left- or right-padded with spaces etc...

OR

you stripe all the floats with this dbo.isReallyInteger([Result]) = 1 and you have no pure integers in the table

Answer 3

updated based on guess of what you were looking for

SELECT MAX( CAST( [Result] AS DECIMAL(10,2) ) )
FROM [testresults]
WHERE ISNUMERIC( [Result] ) = 1;