CASE statement not behaving as expected

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 2000 Forums

Transact-SQL (2000)

CASE statement not behaving as expected

Author

Topic

mmrtech
Starting Member

10 Posts

Posted - 2010-01-22 : 14:39:39

For consideration:

select accountnumber,
accountbalance
from accounts
ORDER BY
CASE WHEN ISNUMERIC(accountnumber)=1 THEN CAST(accountnumber AS bigint)
ELSE accountnumber
END ASC,
accountbalance DESC

This statement returns "Error converting data type varchar to numeric" when I run it. If I comment out the line containing ELSE, it works, but sorts all the records containing alpha characters by accountbalance desc, and the rest (pure numeric string accountnumbers) numerically by accountnumber.

Acct# Balance
a636489 97.18
z704646 51.21
m715030 48.07
z722531 35.82
82909 0.00
220021 0.00
327723 0.00
1200339 11.59
1211590 0.00

mmrtech
Starting Member

10 Posts

Posted - 2010-01-22 : 14:59:43

Looking back on my post, I guess I should have asked a specific question!

Can anyone explain why the query appears to be attempting to CAST accountnumbers that are not numeric? Why isn't the CASE logic passing those records to the ELSE?

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-22 : 15:30:46

Thre Case is really only 1 column...and the expressions can't be of different data types

I'm guessing that accountnumber is varchar

OK, can you tell us what you are actually trying to do?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-22 : 15:36:02

ahhh, another lesson in using proper data types. would be better not to mix numeric and character data. anyway...this ought to work.

SET NOCOUNT ON

select	accountnumber, accountbalance, identity(int) sort
into	#result
from	accounts
where	ISNUMERIC(accountnumber) = 1
ORDER BY 
	Cast(accountnumber as bigint)

insert	#result (accountnumber, accountbalance)
select	accountnumber, accountbalance
from	accounts
where	ISNUMERIC(accountnumber) = 0

select	accountnumber, accountbalance
from	#result 
order by
	sort asc,
	accountnumber asc,
	accountbalance desc;

drop table #result

mmrtech
Starting Member

10 Posts

Posted - 2010-01-22 : 17:57:04

quote:
Originally posted by X002548

Thre Case is really only 1 column...and the expressions can't be of different data types

I'm guessing that accountnumber is varchar

OK, can you tell us what you are actually trying to do?
Brett
8-)

Thank you for the quick replies.

Yes, accountnumber is varchar. Here's the additional background: this is part of a stored procedure that returns a list of accounts to a web page. On the web page, the users can click column headings (including accountnumber) to sort upon. Users complained that the account number sort does not return the numerical sort they are expecting (1000 precedes 999), so I am trying to create logic that will sort numerically when possible, or revert to an ASCII sort if the procedure finds alpha characters in accountnumbers.

Brett, am I understanding you correctly: that within a case statement, the WHEN lines all have to return the same datatypes?

mmrtech
Starting Member

10 Posts

Posted - 2010-01-22 : 17:59:14

quote:
Originally posted by russell

ahhh, another lesson in using proper data types. would be better not to mix numeric and character data.

I agree, and that was my first thought, but unfortunately was told that the data structure is "out of scope" when coming up with a fix for this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-22 : 18:03:34

[code]SELECT AccountNumber,
AccountBalance
FROM dbo.Accounts
ORDER BY CASE ISNUMERIC(AccountNumber)
WHEN 1 THEN STR(AccountNumber, 25)
ELSE AccountNumber
END[/code]

N 56°04'39.26"
E 12°55'05.63"

mmrtech
Starting Member

10 Posts

Posted - 2010-01-22 : 18:28:16

quote:

Originally posted by Peso

SELECT		AccountNumber,
		AccountBalance
FROM		dbo.Accounts
ORDER BY	CASE ISNUMERIC(AccountNumber)
			WHEN 1 THEN STR(AccountNumber, 25)
			ELSE AccountNumber
		END

N 56°04'39.26"
E 12°55'05.63"

Peso, thank you SO much. I put this in place and the sort is working well now. The list returns with numbers first (in integer order) and then the accounts with preceding alpha characters appear at the end.

If you don't mind sparing a little more of your time, can you educate me on why the numeric sort works even though str() returns char?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-22 : 18:48:39

STR left-pads spaces to that the "numeric" account numbers start with space, which string-wise are sorted before any other character.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-22 : 18:50:06

Another method (probable faster) would be this

SELECT		AccountNumber,
		AccountBalance
FROM		dbo.Accounts
ORDER BY	ISNUMERIC(AccountNumber) DESC,
		AccountNumber

N 56°04'39.26"
E 12°55'05.63"

mmrtech
Starting Member

10 Posts

Posted - 2010-01-22 : 19:20:46

quote:
Originally posted by Peso

STR left-pads spaces to that the "numeric" account numbers start with space, which string-wise are sorted before any other character.

Ah, thank you, that makes sense.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-22 : 20:46:19

quote:
Originally posted by Peso

Another method (probable faster) would be this
SELECT AccountNumber,
AccountBalance
FROM dbo.Accounts
ORDER BY ISNUMERIC(AccountNumber) DESC,
AccountNumber

N 56°04'39.26"
E 12°55'05.63"

this was my 1st thought, but it doesn't sort the numeric values properly. for example, '1000' would come before '22' in this method

Kristen
Test

22859 Posts

Posted - 2010-01-23 : 01:34:19

"This statement returns "Error converting data type varchar to numeric" when I run it."

Beware that IsNumeric will consider, for example, '1E1' as being a valid number, but that won't cast to INT.

It will also consider "1.234" as numeric, and that is probably not a valid Account Number

STR() will correctly handle the floating point values (the ones where CAST to INT will fail), I like that solution

However, if you are looking for a numeric account number, that does not allow "1E1" nor "-" sign, then I think you need to use a RegEx to check for only digits:


ORDER BY	CASE WHEN AccountNumber NOT LIKE '%[^0-9]%'
			THEN STR(AccountNumber, 25)
			ELSE AccountNumber
		END

and once you know that AccountNumber only contains digits you could just left-pad it (may be faster than STR() ?)

mmrtech
Starting Member

10 Posts

Posted - 2010-01-25 : 11:11:31

quote:
Originally posted by Kristen

However, if you are looking for a numeric account number, that does not allow "1E1" nor "-" sign, then I think you need to use a RegEx to check for only digits:

ORDER BY CASE WHEN AccountNumber NOT LIKE '%[^0-9]%'
THEN STR(AccountNumber, 25)
ELSE AccountNumber
END

and once you know that AccountNumber only contains digits you could just left-pad it (may be faster than STR() ?)

Kristen: I originally started with a similar expression, but tried ISNUMERIC when I couldn't get it to work. Probably a good idea; I doubt we'll see any scientific notation come through, but I could definitely imagine someone hyphenating an account number at some point. Thank you for contributing. I'm going to put these suggestions into place.

Thanks all!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-25 : 11:37:23

lol

that's not sceintific notation or a hyphen. look up LIKE in Book OnLine

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 14:26:42

I think he was referring to IsNumeric() returning True for "1E1"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-25 : 14:40:36