Casting Help - 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)

Casting Help

Author

Topic

dog2bert
Starting Member

13 Posts

Posted - 2012-07-03 : 13:42:25

I am generating an error report that compares two columns, preload and postload, to make sure they match. Some values are returned in a different data type so when compared they appear different to SQL. 0 compared to blank, 0 compared to 0.0000, etc.

What should I cast these to to make sure they compare properly?

dog2bert
Starting Member

13 Posts

Posted - 2012-07-03 : 13:58:34

000 compared to 0

dog2bert
Starting Member

13 Posts

Posted - 2012-07-03 : 13:59:25

Blank to 0.000

dog2bert
Starting Member

13 Posts

Posted - 2012-07-03 : 13:59:54

045 to 45

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-03 : 14:51:13

You should define your columns so that they are typed appropriately for the data. If they are in fact all character data then why should 045 equate to 45? or 0 to 000? They are different.

Assuming you can't/won't do that then one thing you could do based on your examples is compare the values converted to money

Be One with the Optimizer
TG

dog2bert
Starting Member

13 Posts

Posted - 2012-07-03 : 15:00:47

Here is the error I receive:

Cannot convert a char value to money. The char value has incorrect syntax.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-03 : 15:11:18

That is why I specified "based on your examples".
If you have all kinds of non-numeric data in your columns combined with numeric data then you will need to specify a bunch of rules to follow. Then possibly take several passes at it to clean out false positive differences that result from a straight string comparison. If you want help then post the structure of your table and enough sample data to represent values that should not show in your report as well as things that should show up as legitimate differences.

Be One with the Optimizer
TG

dog2bert
Starting Member

13 Posts

Posted - 2012-07-03 : 15:24:38

Material Plant ttMARC MARC Field
MATNR WERKS ttMARC MARC Field
000000000000077924 A968 0004 LADGR correct to Show up
000000000000078040 A968 0004 LADGR correct to Show up
000000000000070479 A956 X KAUTB correct to Show up
000000000000070479 A998 X KAUTB correct to Show up
000000000000025192 A995 000 0 WEBAZ Should not show up
000000000000025247 C098 000 0 WEBAZ Should not show up
000000000000070479 A956 ND DISMM This probably can't be converted to Money
000000000000070479 A998 ND DISMM This probably can't be converted to Money
000000000000075059 A999 ND DISMM This probably can't be converted to Money
000000000000075059 C006 ND DISMM This probably can't be converted to Money

dog2bert
Starting Member

13 Posts

Posted - 2012-07-03 : 15:25:09

MARC and ttMARC are both nvarchar

dog2bert
Starting Member

13 Posts

Posted - 2012-07-03 : 15:28:59

https://plus.google.com/photos/111299300543748275094/albums/5761027150891756177?authkey=CJjkpZWCxo_3cw

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-03 : 16:06:00

This should work most of the time though it is not infallible:


where case
      when isNumeric(ttMARC) = 1 then convert(varchar(30), convert(numeric(28,9),ttMARC))
      else ttMARC
      end 
      != 
      case
      when isNumeric(MARC) = 1 then convert(varchar(30), convert(numeric(28,9),MARC))
      else MARC
      end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources