Subtract = null - 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 2005 Forums

Transact-SQL (2005)

Subtract = null

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-21 : 11:45:21

Have a table that has several decimal columns (18,2). I am doing the following:

select top 10 abs(netcash) as netcash, abs(comm) as comm,abs(nullif(netcash,0)) - abs(nullif(comm,0))as math from transactions

and i get the following results


netcash	        comm	math
85928.70	130.00	85798.70
85928.70	0.00	NULL
84752.38	110.00	84642.38
84752.38	0.00	NULL
7405.00	        0.00	NULL
7405.00	        0.00	NULL
27890.18	55.00	27835.18
27890.18	0.00	NULL
14925.20	10.00	14915.20
14925.20	0.00	NULL

I dont understand why I get nulls instead of just 85298.70 when it does the 85928.70 - 0.00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-21 : 11:49:44

because you use nullif(). nullif () returns null as a value if the value of the 1st parameter is same as 2nd parameter.

for row 2 it will be nullif(85928.70 , 0) - nullif(0, 0)
which will be equal to 85928.70 - NULL
and result in NULL

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-21 : 11:51:16

Do look up BOL on nullif() function

http://msdn2.microsoft.com/en-us/library/ms177562.aspx

KH
[spoiler]Time is always against us[/spoiler]

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-21 : 11:52:11

PERFECT, THANKS

Happy Holidays

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 11:52:23

You need to use ISNULL which replaces null by value passed use:-

abs(isnull(netcash,0)) - abs(isnull(comm,0))

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-21 : 11:54:31

I was trying isnull but was getting the divide by zero message when doing some other math, and read something that said to use nullif

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 12:02:49

thats when you perform division. There inorder to prevent division by zero error you need nullif as

NR/NULLIF(DR,0) which returns the result as NULL rather than throwing a divide by zero error. Then you can use ISNULL to return result as 0.

ISNULL((NR/NULLIF(DR,0)),0)

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-21 : 12:10:03

Thanks for the explanation, I appreciate it. Would you be able to explain why I have the following than:


select top 10 abs(netcash) as netcash, abs(comm) as comm, abs(units) as units,abs(netcash) - abs(comm) / abs(nullif(units,0)) as total from transactions


Results:
netcash	        comm	units	        total
85928.70	130.00	2600.000	85928.6500000000000000000
85928.70	0.00	85928.700	85928.7000000000000000000
84752.38	110.00	2200.000	84752.3300000000000000000

When I would expect for example the frist result to be for the top line: 32.9995

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 12:20:08

use like this:-

select top 10 abs(netcash) as netcash, abs(comm) as comm, abs(units) as units,(abs(netcash) - abs(comm))/ abs(nullif(units,0)) as total from transactions

as per what you gave, it does abs(comm)/ abs(nullif(units,0)) first as / has greater precedence over -. so you need to enclose first operation in () to give greater precedence.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-21 : 12:26:34

YOU DA FREAKING MAN, THANKS FOR YOUR EXPERTISE. ONCE AGAIN HAPPY HOLIDAYS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-24 : 01:46:49

quote:
Originally posted by duhaas

Have a table that has several decimal columns (18,2). I am doing the following:

select top 10 abs(netcash) as netcash, abs(comm) as comm,abs(nullif(netcash,0)) - abs(nullif(comm,0))as math from transactions

and i get the following results

netcash comm math
85928.70 130.00 85798.70
85928.70 0.00 NULL
84752.38 110.00 84642.38
84752.38 0.00 NULL
7405.00 0.00 NULL
7405.00 0.00 NULL
27890.18 55.00 27835.18
27890.18 0.00 NULL
14925.20 10.00 14915.20
14925.20 0.00 NULL

I dont understand why I get nulls instead of just 85298.70 when it does the 85928.70 - 0.00