-isnull

Question

Post reply

-isnull

cathy.baker

Hall of Fame

Points: 3412
More actions
January 26, 2010 at 9:56 am

#138440

I've been doing SQL for a very long time. I have a vendor report that uses the -isnull(sum(somefield), 0). I know what the isnull() does, but I've never seen it preceded with a minus sign before. The statement does not return an error. Has anyone ever seen this before?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Can you post the rest of the statement?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

cathy.baker Hall of Fame Points: 3412 More actions · Answer 2

select isnull(sum(u.quantity),0) as q, -isnull(sum(u.linecost),0) as d

from matusetrans u

where u.issuetype in ('ISSUE','RETURN')

and u.transdate< 200912

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

It looks like -isnull(sum(u.linecost),0) means -1 * isnull(sum(u.linecost),0)

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

cathy.baker Hall of Fame Points: 3412 More actions · Answer 4

At first, I thought maybe that would be what it was, but the results of the query return a positive value for column d

Nabha SSCrazy Eights Points: 8534 More actions · Answer 5

May be the sum is giving a negetive value and they wanted to show absolute(positive value). Have a look at this,

SELECT -isnull(1, 0)

SELECT -isnull(-1, 0)

---------------------------------------------------------------------------------

cathy.baker Hall of Fame Points: 3412 More actions · Answer 6

You could be correct. But why not use the ABS() function. It would be clear and sort of self-documenting. We are going to ask the vendor to document the report and I guess we'll find out then why they did this.

Thanks for all of the answers.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

cathy.baker (1/26/2010)
You could be correct. But why not use the ABS() function. It would be clear and sort of self-documenting. We are going to ask the vendor to document the report and I guess we'll find out then why they did this.
Thanks for all of the answers.

Hang on here. -1 * sum is NOT the same as ABS(). I'm guessing whoever wrote that statement wanted -1 * the sum, and was NOT trying to remove the - sign.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

cathy.baker Hall of Fame Points: 3412 More actions · Answer 8

If I remove the - sign, I get a positive value, with it I get a negative value. But further down there is another similar query that does the exact opposite. So, I am assuming that it is doing -1 * value. Thanks again to everyone. This has definitely been interesting.