Problem with Cast. - 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

General SQL Server Forums

New to SQL Server Programming

Problem with Cast.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-07-28 : 14:45:50

To start with, this works:

SELECT sum((SORELS.forderqty - SORELS.finvqty
             - COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (
                     sorels.fsono + sorels.finumber + sorels.frelease),0))
           * SORELS.funetprice)
 as [BackOrder] FROM SORELS
 WHERE sorels.fsono = '281460' AND FMASTERREL = 0

The answer is 4147.5. what I want to return is 4147.50. I tried the following:

SELECT sum(cast((SORELS.forderqty - SORELS.finvqty
             - COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (
                     sorels.fsono + sorels.finumber + sorels.frelease),0))
           * SORELS.funetprice as decimal(5, 2) ))
 as [BackOrder] FROM SORELS
 WHERE sorels.fsono = '281460' AND FMASTERREL = 0

AND

 SELECT cast(sum((SORELS.forderqty - SORELS.finvqty
             - COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (
                     sorels.fsono + sorels.finumber + sorels.frelease),0))
           * SORELS.funetprice  ) as decimal(5, 2))
 as [BackOrder] FROM SORELS
 WHERE sorels.fsono = '281460' AND FMASTERREL = 0

and both return 0.

Help.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-07-28 : 14:52:48

Perhaps some of the problem is that the database value is actually:

4147.500000000

And I just noticed taht I get an error at the bottom with the second 2 queries: SQL Server Database Error: Arithmetic overflow error converting numeric to data type numeric.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-28 : 14:57:30

I thought you should use 0.0 rather than 0 in your coalesce in your first query.

Edit: Oh no that sh..

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-28 : 15:00:25

to express this value: 4147.50
You need decimal(6, 2)

Be One with the Optimizer
TG

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-28 : 15:01:35

I have seen that in plus and minus there is no effect by literals with decimal point - only in devide or multiply...

So what datatypes are involved?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-28 : 15:02:34

quote:
Originally posted by TG

to express this value: 4147.50
You need decimal(6, 2)

Be One with the Optimizer
TG

TG!!!!
Eagleeye!

No, you're never too old to Yak'n'Roll if you're too young to die.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-07-28 : 15:12:50

Ah! Thanks guys.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-28 : 15:38:24

quote:
Originally posted by webfred

quote:
Originally posted by TG

to express this value: 4147.50
You need decimal(6, 2)

Be One with the Optimizer
TG

TG!!!!
Eagleeye!

No, you're never too old to Yak'n'Roll if you're too young to die.

My father used to call me that - but only because I could find stuff on the floor after he dropped it...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources