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'.

Try changing from the last parens forward to: ) A where A.is_really_numeric = 1 — SqlACID
– SqlACID, Commented Dec 25, 2011 at 16:51
What @SqlACID means is your subselect should have an alias which it doesn't at the moment. On a different note, you should really avoid asking so very different questions in one post. That is not how this site works. It's been designed to be helpful both to those immediately asking questions and to those who are browsing questions in search for an answer. Mixing different questions in one post like that makes things messy and thus not very helpful in the long run. — Andriy M
– Andriy M, Commented Dec 25, 2011 at 23:46

Kris Ivanov · Accepted Answer · 2011-12-25 05:11:20Z

2

updated based on guess of what you were looking for

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

edited Dec 25, 2011 at 5:11

answered Dec 25, 2011 at 5:03

Kris Ivanov

10.6k2 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alex Gordon Over a year ago

Error converting data type varchar to numeric.

Kris Ivanov Over a year ago

most likely you have some garbage data that is valid numeric but will not cast, for example symbols like ., -, ^, and +, you will have to exclude those

Kris Ivanov Over a year ago

here some ref to get you started classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Alex Gordon Over a year ago

thanks so much but this looks crazy! i have to do this to know whether it is numeric? there's no easier way out/?

Alex Gordon Over a year ago

kris please check again when yo uget an hour

|

Oleg Dok · Accepted Answer · 2011-12-25 07:02:35Z

1

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

edited Dec 25, 2011 at 7:02

answered Dec 25, 2011 at 5:15

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

7 Comments

Alex Gordon Over a year ago

Error converting data type varchar to numeric.

Oleg Dok Over a year ago

Now this depends on your data only - it is runtime error, not parse timedepends, may be you have numbers with more than 10 digits in both integer and decimal parts?

Alex Gordon Over a year ago

thank you oleg. i added to my question. please check it once more.

Alex Gordon Over a year ago

i just pasted a sample of the data please check i tout

Oleg Dok Over a year ago

In sample it is not clear - is there are any whitespace chars, try to produce sample with this: select top 10 cast(Result as varbinary(max)) from testresults

|

legendofawesomeness · Accepted Answer · 2011-12-25 07:36:25Z

-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

answered Dec 25, 2011 at 7:36

legendofawesomeness

2,9212 gold badges21 silver badges32 bronze badges

Collectives™ on Stack Overflow

if statement in sql server

3 Answers 3

6 Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related