Nested Functions in CASE are killing me!

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

General SQL Server Forums

New to SQL Server Programming

Nested Functions in CASE are killing me!

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-10-01 : 17:14:36

[code]
SELECT
CASE
WHEN ISNUMERIC(SUM(CONVERT(REAL,REPLACE(ISNULL(OPS_047_Projection,'0'),'',0))))=1 AND
ISNUMERIC(SUM(CONVERT(REAL,REPLACE(ISNULL(OPS_047_Actual,'0'),'',0))))=1 THEN
SUM(CONVERT(REAL, OPS_047_Actual)) / SUM(CONVERT(REAL, OPS_047_Projection))
ELSE 0
END,
from mytable
[/code]

I keep getting "Error converting data type varchar to real". The bad data I have coming in is NULLS and blanks, which I am trying to account for in my functions. (I know, the ideal is to validate the data coming into the system. Life is not ideal. I gotta deal with bunk data for now!)

What am I doing wrong?

Craig Greenwood

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-01 : 21:34:28

looks like you are handling the nulls, now need to use the replace function on the blanks

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-02 : 01:55:31

Yes if the data inside is not the number but charater or string values, it will not be able to convert charater into Number.
Thats why the error comes..

To Avoid errors...First check wheather data is ISNUMERIC or NOT then start with the process...

quote:
Originally posted by craigwg

SELECT
CASE
WHEN ISNUMERIC(SUM(CONVERT(REAL,REPLACE(ISNULL(OPS_047_Projection,'0'),'',0))))=1 AND
ISNUMERIC(SUM(CONVERT(REAL,REPLACE(ISNULL(OPS_047_Actual,'0'),'',0))))=1 THEN
SUM(CONVERT(REAL, OPS_047_Actual)) / SUM(CONVERT(REAL, OPS_047_Projection))
ELSE 0
END,
from mytable

I keep getting "Error converting data type varchar to real". The bad data I have coming in is NULLS and blanks, which I am trying to account for in my functions. (I know, the ideal is to validate the data coming into the system. Life is not ideal. I gotta deal with bunk data for now!)

What am I doing wrong?

Craig Greenwood

Deepak Arora

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-02 : 02:13:46

SELECT
CASE
WHEN ISNUMERIC(SUM(CONVERT(REAL,REPLACE(ISNULL(OPS_047_Projection,'0'),'',0))))=1 AND
ISNUMERIC(SUM(CONVERT(REAL,REPLACE(ISNULL(OPS_047_Actual,'0'),'',0))))=1 THEN
SUM(CONVERT(REAL, OPS_047_Actual)) / SUM(CONVERT(REAL, OPS_047_Projection))
ELSE 0
END,
from mytable
WHERE IsNUMERIC(OPS_047_Projection) = 1 AND ISNUMERIC(OPS_047_Actual) =1

I think apply this where clause, this will solve your issue.

quote:
Originally posted by craigwg

SELECT
CASE
WHEN ISNUMERIC(SUM(CONVERT(REAL,REPLACE(ISNULL(OPS_047_Projection,'0'),'',0))))=1 AND
ISNUMERIC(SUM(CONVERT(REAL,REPLACE(ISNULL(OPS_047_Actual,'0'),'',0))))=1 THEN
SUM(CONVERT(REAL, OPS_047_Actual)) / SUM(CONVERT(REAL, OPS_047_Projection))
ELSE 0
END,
from mytable

I keep getting "Error converting data type varchar to real". The bad data I have coming in is NULLS and blanks, which I am trying to account for in my functions. (I know, the ideal is to validate the data coming into the system. Life is not ideal. I gotta deal with bunk data for now!)

What am I doing wrong?

Craig Greenwood

Deepak Arora

craigwg
Posting Yak Master

154 Posts

Posted - 2010-10-04 : 14:19:21

My largest issue I found this morning. The Germans had used commas for decimal points (didn't we win that war?...TWICE?). I just used another REPLACE function and changes the commas to periods and that worked. I also made use of the ISNUMERIC function, which I have tried in the past, but got it working.

Thanks team!

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources