Math statement does not return the correct values

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)

Math statement does not return the correct values

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-24 : 11:05:43

Hello

I was hoping someone can assist me with this and perhaps point out what I am doing wrong. I have a simple math statement (at least I thought it was simple). But it's not working properly.

UPDATE dbo.[TransNet Corporation$Open Order Summary]
SET Product = ([Qty] - [Qty Invoiced]) * [Unit Price]
WHERE [Gen_Prod_Posting_Group] = 'HARDWARE'

In this case i'm saying the Product field value should equal,
[Qty] subtracted from [Qty Invoiced] times the [Unit price].

In many cases the [Qty] & [Qty Invoiced] values are the same. (i.e. 5 subtracted from 5) which would = 0, Now 0 times anything should equal 0. But it's not returning the correct math result.

Please help..

I'm using SQL 2000 with Service Pack 4a

Thanks

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 11:08:52

What are some of the results you are getting back? Are either of those two fields NULL for some records?

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-24 : 11:37:10

Yes some are nulls and/or blank. (Should I be using a different math statement concerning the null fields, if so would you be so kind as to post an example of the syntax)

But for instance I have one example in which the [QTY] = 5 & the [QTY INVOICED] = 5 & the [UNIT PRICE] = 10.
The Math statement says Qty - Qty Invoiced * Unit Price

The result I'm getting back is 10. (But should be = 0) as 5 - 5 = 0 * 10 (should = 0) again

Help.....

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 11:43:51

Not sure why you'd be getting that result. I do know that if you have nulls, it will affect your answer and you might want to look into using ISNULL to deal with nulls. ISNULL(qty,0) for example.

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-24 : 11:49:50

Would you be kind enough to post a syntak example and I will try that (never used ISNULL(qty,0).
Where would I place it and what is the syntax

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 11:54:16

I think if you are not familiar with isNull() it would be better you got into the habit of using COALESCE() instead - its Standards Compliant, takes a variable number of arguments, has an awkward name but at least it isn't stupid (all other IsXXX() functions return True/False, and IsNull in all other languages, except T-SQL, does exact that too

), and doesn't have the parameter-messing foibles that IsNull has)

Hmmm ... I wonder if I feel rather too strongly about that?


DECLARE	@Param1 int,
	@Param2 int

SELECT	@Param1 = NULL,
	@Param2 = 1

SELECT @Param1, @Param2

SELECT COALESCE(@Param1, 0), COALESCE(@Param2, 0)

Kristen

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 11:57:56

Sure. What will most likely happen if you have NULLs is that your answer will be NULL and you may want it to be 0.

If say QtyInvoiced was NULL for a record, you probably want the UPDATE to put a 0 in instead of NULL.

This statement will replace all NULLs with 0 for your UPDATE to use:

SET Product = (isnull([Qty],0) - isnull([Qty Invoiced],0)) * isnull([Unit Price],0)

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 12:03:17

Although I have to say, re-reading this, that I would be very worried about an application that allowed a NULL to get into any of those columns because I'm not comfortable that can just be taken to means the same as "zero" - it suggest to me "something went wrong" instead

Kristen

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-24 : 12:04:27

Hi Kristen

Please bear with me and my questions as I am not familiar with this syntax at all (And thanks so much for all your help)

1 - Is @Param1 supposed to be the Field name(s)in the DECLARE section? (i.e. Param1 = [Qty] & Param2 = [Qty Invoiced])
2 - Should this syntax be placed before the initial Math statement?

Thanks
Eric

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 12:06:42

Yep. I thought maybe that's why their math wasn't working out...because of NULLs but from his example it doesn't sound like NULLs are the problem. But they did mention that there are NULLs sometimes so I was just showing a way to deal with them. I would however, want to go back and figure out why they were NULL to begin with and fix that issue...maybe have those fields not allow NULLs.

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 12:10:34

"Is @Param1 supposed to be the Field name(s)in the DECLARE section? (i.e. Param1 = [Qty] & Param2 = [Qty Invoiced])"

Sorry, no not really. That was just a little example that you could run to see how the two different values (NULL and 1) behaved when processed with COALESCE.

Following on from what Van said above I think you ought to have a look at records that NULL in those columns and examine why that may be the case:


SELECT *
FROM dbo.[TransNet Corporation$Open Order Summary]
WHERE (
            [Qty] IS NULL
         OR [Qty Invoiced] IS NULL
         OR [Unit Price]  IS NULL
      )
      AND [Gen_Prod_Posting_Group] = 'HARDWARE'

Kristen

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-24 : 12:44:30

Hello again

I just checked and found 2 fields which were null and/or blank. I removed/deleted those line items.
However when I run the math statement, I still get the wrong values (same as noted above)

Thank you guys in advance so much for your help and any additional help you may be able to provide

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 12:52:42

What are the datatypes for the [Qty] and [Qty Invoiced] columns?

If INT then the maths should be OK, but if they are FLOAT or some other in-precise numeric datatype I suppose that could be causing a rounding error

(Although I would expect SELF - SELF to be zero in all instances

)

Kristen

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 12:54:11

How many rows of data are in the HARDWARE group?

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-24 : 12:56:29

952

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 13:04:15

Can you run this and post a few of the result rows that are in error?

SELECT [Qty], [Qty Invoiced], [Unit Price], [Product], ([Qty] - [Qty Invoiced]) * [Unit Price] as Calc
FROM dbo.[TransNet Corporation$Open Order Summary]
WHERE [Gen_Prod_Posting_Group] = 'HARDWARE'

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-24 : 14:06:23

Qty Qty Invoiced Unit Price Product Calc
1 0 31625 31625 31625
1 0 27031.08 27031.08 27031.08
4 0 0 0 0
1 1 521.42 0 0
3 3 87 0 0
112 112 0.8 0 0
1 0 594 594 594
4 0 0 0 0
1 1 80.76 0 0
8 8 1526.66 0 0
13 0 0 0 0
13 0 0 0 0
13 0 0 0 0
24 0 0 0 0
30 0 0 0 0
2 0 1218.41 2436.82 2436.82

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 14:22:01

All of those are the correct answer. I'd want to see an example where it's not calculating correctly.

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-24 : 14:34:28

Opps my mistake.. Here you are (Again thanks for your help)

Qty Qty Invoiced Unit Price Product Calc
2 0 9482 18964 18964
2 0 -2071.2 -4142.4 -4142.4
2 0 0 0 0
2 0 325 650 650
2 0 915 1830 1830
14 0 1958 27412 27412
6 0 1224 7344 7344
2 0 0 0 0
2 0 0 0 0

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-24 : 14:38:12

These are all correct too. I need to see one that's not calculating right.

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-24 : 14:51:23

Hi Van

Yes these all certainly seem correct with your SELECT statement of:

SELECT [Qty], [Qty Invoiced], [Unit Price], [Product], ([Qty] - [Qty Invoiced]) * [Unit Price] as Calc
FROM dbo.[TransNet Corporation$Open Order Summary]
WHERE ([Gen_Prod_Posting_Group] = 'HARDWARE')

Given this, Could there be something wron with my UODATE statment of (perhaps this is where the problem lies. Only I can't seem to locate it)

UPDATE dbo.[TransNet Corporation$Open Order Summary]
SET Product = ([Qty] - [Qty Invoiced]) * [Unit Price]
WHERE [Gen_Prod_Posting_Group] = 'HARDWARE'

Eric

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources