What do these symbols do???

Question

What do these symbols do???

djustice 20821

Old Hand

Points: 326
More actions
February 8, 2012 at 7:10 am

#250444

I have worked with SQL for a few years now, but have never seen the + - symbols used like they are in the script below. I am trying to figure out what this piece of code means. It is part of a much larger view. Can anyone tell me what the +/- is doing here?
Thanks
[Code = "sql"]ReplenishmentPosition = dbo.fnIMRoundQtyPhysCount(
(SELECT COALESCE (SUM(m.QtyOnHand), 0)
FROM timWhseBinInvt m
JOIN timWhseBin n ON m.WhseBinKey = n.WhseBinKey
WHERE n.WhseKey = a.WhseKey AND m.ItemKey = a.ItemKey)
- a.QtyOnSO
- a.QtyReqForTrnsfr
- a.QtyReqForWO
+ a.QtyOnPO
+ a.QtyOnTrnsfr
+ a.QtyOnWO
+ COALESCE((SELECT SUM(n.PendQtyIncrease - n.PendQtyDecrease)
FROM timWhseBin m
INNER JOIN timWhseBinInvt n ON m.WhseBinKey = n.WhseBinKey
WHERE m.WhseKey = a.WhseKey AND n.ItemKey = a.ItemKey),0)
, b.ItemKey, b.StockUnitMeasKey)[/code]

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Adding and subtracting values. The various fields apparently return numeric types and are been added and subtracted to get a total.

Maybe it would be more understandable as such:

(SELECT COALESCE (SUM(m.QtyOnHand), 0)

FROM timWhseBinInvt m

JOIN timWhseBin n ON m.WhseBinKey = n.WhseBinKey

WHERE n.WhseKey = a.WhseKey AND m.ItemKey = a.ItemKey) - a.QtyOnSO - a.QtyReqForTrnsfr - a.QtyReqForWO + a.QtyOnPO + a.QtyOnTrnsfr + a.QtyOnWO + COALESCE((SELECT SUM(n.PendQtyIncrease - n.PendQtyDecrease)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

What they are meant for: substracting and summing values.

It's hard to see without the entire SQL statement present, but the select statements return numerical values.

edit: apparently Gail can type faster than I can 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

HowardW One Orange Chip Points: 29109 More actions · Answer 3

It's pretty bad looking code, but it's straight forward. It's adding and substracting numerical values.

E.g. the first select statement is returning a scalar value (a number), to which the other columns specified are being added and substracted and then a function is being applied to the result of this.

The most straight forward example would be:

SELECT 1+2-1

Edit: Apparently Koen and Gail can both type faster than I can

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

HowardW (2/8/2012)
The most straight forward example would be:
SELECT 1+2-1

That really is straight forward 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

djustice 20821 Old Hand Points: 326 More actions · Answer 5

Thanks everyone. I should have figured that out, I have just never seen it coded out in that way.

Thanks again!