Error converting varchar to numeric... again.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error converting varchar to numeric... again.

Author

Topic

glt101
Starting Member

28 Posts

Posted - 2009-03-31 : 17:08:38

Hi All,

I know this problem crops up a lot, but I didn't see a
way to solve my particular case. So I decided to post
after much banging of head against this wall.

The Data:
We have a simple table that maps sku_cd (varchar(20)) to
sku_xrev_no (char(10)). sku_xrev_no is actually a string
representation of a float value e.g. '7.5'.

What we want:
We want a simple way to select records where sku_xrev_no
is greater than a given value.

The problem:
The sku_xrev_no column contains entries other than numbers
e.g. 'any', 'none'. So attempts to implicitly convert that
column to numeric fail in a WHERE clause with:
Error converting data type varchar to numeric.
Here's the query:

SELECT DISTINCT orddet.ordd_comment,
orddet.ordd_release_dt,
orddet.ordd_ord_no,
sku.sku_cd
FROM orddet
INNER JOIN sxsn_sn ON orddet.sn_sn = sxsn_sn.sn_sn
INNER JOIN sku ON sxsn_sn.sku_cd = sku.sku_cd
WHERE sku.sku_xrev_no > 7.5

What is the best way around this?

Many thanks,
Geoff

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-31 : 17:10:39

Convert(decimal(10,2),sku.sku_xrev_no) > 7.5

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 17:13:34

Try this...

SELECT DISTINCT orddet.ordd_comment,
orddet.ordd_release_dt,
orddet.ordd_ord_no,
sku.sku_cd
FROM orddet
INNER JOIN sxsn_sn ON orddet.sn_sn = sxsn_sn.sn_sn
INNER JOIN sku ON sxsn_sn.sku_cd = sku.sku_cd
WHERE ISNUMERIC(sku.sku_xrev_no) = 1 AND CONVERT(decimal(10,2),sku.sku_xrev_no) > 7.5

glt101
Starting Member

28 Posts

Posted - 2009-03-31 : 17:54:59

Excellent Guys -Thanks very much.
Cheers,
Geoff

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 19:35:05

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources