Strange case behavior - SQL Server Forums

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

Transact-SQL (2008)

Strange case behavior

Author

Topic

midgard
Starting Member

2 Posts

Posted - 2012-07-10 : 02:10:25

Hi Folks.

I would like to trade this code for a simple use case ...

SELECT TOP 5
ID,
CASE WHEN ISNUMERIC(FUT_UMLAGERUNG.LZL_STAT_EKDM) = 0 THEN
'IS NOT NUMERIC'
-- CAST(FUT_UMLAGERUNG.LZL_MENGE AS numeric(15,2))
ELSE
'IS NUMERIC'
-- CAST(FUT_UMLAGERUNG.LZL_MENGE AS numeric(15,2))
END AS SUMME_EK,
ISNUMERIC(FUT_UMLAGERUNG.LZL_MENGE) AS LZL_MENGE_NUMERIC,
CAST(FUT_UMLAGERUNG.LZL_MENGE AS numeric(15,2)) AS LZL_MENGE_VALUE,
ISNUMERIC(FUT_UMLAGERUNG.LZL_STAT_EKDM) AS LZL_MENGE_NUMERIC
FROM FUT_UMLAGERUNG

Result:
ID SUMME_EK LZL_MENGE_NUMERIC LZL_MENGE_VALUE LZL_MENGE_NUMERIC
1 IS NOT NUMERIC 1 1.00 0
2 IS NUMERIC 1 2.00 1
3 IS NUMERIC 1 2.00 1
4 IS NUMERIC 1 2.00 1
5 IS NUMERIC 1 120.00 1

So far, so good. But if I cast the field 'FUT_UMLAGERUNG.LZL_MENGE' in the case, I receive the error:

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.

Any idea?

Thanxxx
Patrick

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-10 : 02:21:27

It's most likely this bit outside of the case statement:
CAST(FUT_UMLAGERUNG.LZL_MENGE AS numeric(15,2))
you can't just cast it - what if it's not numeric?
You'll need to conditionally cast it in a case statement as you've done with SUMME_EK and decide what you want to do with non-numerics (null or zero maybe?)

Also, your TOP 5 without order by does not make any sense. You need to order by (and carefully consider if you really mean top 5 rows or rows that have the top 5 value).

midgard
Starting Member

2 Posts

Posted - 2012-07-10 : 02:27:49

Hi Folks.

I resolve my Problem with http://www.fmsinc.com/free/newtips/sql/sqltip10.asp

Thanxxx
Patrick

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-10 : 02:39:20

Good for you, but your query still has holes in it.
1) You are casting values without testing for IS_NUMERIC outside the cast so it can still fail.
2) without ORDER BY your TOP makes no sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources