Having conversion issues - String to Float

I am running into some issues with conversion and would appreciate someone who can help on this.

I need to roll up a column which contains numeric (float) data but it is stored in a varchar field.

I was trying to do the following (please note that Activity field is varchar(50) in MyTable):

SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable

and i get the following error.

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value '-39862.8' to data type int.

I even tried with case statement (as below) but still got same issue.

SELECT CASE ISNUMERIC(NULLIF(Activity,0))

WHEN 1 THEN

CONVERT(float, (NULLIF(Activity,0)))

ELSE 0

END

from MyTable

Thanks,

Ashish

Try using 0.0 rather than 0 in the NULLIF functions. Also, do you mean to use ISNULL, or NULLIF? NULLIF returns a NULL, if the expressions are equal, while ISNULL returns the second argument if the first is NULL.
|||Yes. I want to use ISNULL. I changed it but I still get the error. Even with '0.0'|||What happens when you run the following:

Code Snippet

select *

from MyTable

where isnumeric(activity) = 0

|||SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable where isnumeric(Activity)=1|||

Are you sure that you want to use NULLIF()? (I would think that ISNULL() would be a better option.)

Also note that the isnumeric() test will PASS because '-39862.8' will always test to be a number.

This works as you want.

Code Snippet

DECLARE @.Activity varchar(50)

SET @.Activity = '-39862.8'

SELECT cast( isnull( @.Activity, 0 ) AS float )

--
-39862.800000000003

|||Good point about isnull(), Arnie.

Handling Transaction

hi friends,

I like to put a set of sql statements under a transaction and wish the sql server to take care of commit / roll back the entire set depending upon the success/failure of the statements in the set. I want the whole set is either to success or to failure.

When I go through the docs, i find that SQL Server 2000 operates three transaction modes:
Autocommit transactions : Each individual statement is a transaction.

Explicit transactions : Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

As one can see, it seems that it is not possible to define "atomic compound sql statements". Even if i used explicit transaction, it is not possible to achieve this, since i couldn't find a mechanism to handle errors for a group of statements.

I wonder how to write atomic compound sql statement in the sql server.
can anybody please help me on this...

JakeThe only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.|||hi Kaiowas,

thanks for the suggestion. instead of checking for success & failure for each line, i found another way of doing this.
If we put the set of statements in a BEGIN TRANSACTION ... COMMIT TRANSACTION block, the commit transaction will get execute iff all the statements succeed. Otherwise all the statements get rolled back. This solves my problem.

But still there is no way to handle exception for a block of statements.
Does Yukon release have support for this? any idea?

Jake

Originally posted by Kaiowas
The only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.