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.
| Author |
Topic |
|
crugerenator
Posting Yak Master
126 Posts |
 Posted - 2008-09-15 : 12:44:40
|
I'm having trouble with a case statement. Basically I'm looking to return 0 if a field's value is 0, and do some math if the field contains a value > 0. Here is the line I'm trying to use one on...(Coalesce (overPeakTotal, 0) - (sum (OrderItemType.dollarValue*OrderItem.quantity))) as amtOverPeakTotal Here is what I'm trying to do in pseudo code... if overPeakTotal = 0 then return 0, else (overPeakTotal - (sum (OrderItemType.dollarValue*OrderItem.quantity))) as amtOverThis is what I've been trying with the case statement...case when Coalesce (overPeakTotal, 0)=0 then 0 else (overPeakTotal - (sum (OrderItemType.dollarValue*OrderItem.quantity))) as amtOverPeakTotal |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-15 : 12:47:56
|
| you cant use sum() and individual columns together unless you group by individual columns. can you post full query used, without seeing which its very hard to give accurate soln. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-15 : 12:49:45
|
| Not sure what your "trouble" is but it looks like you're just missing the "END" of your CASE.Be One with the OptimizerTG |
|
|
|
crugerenator
Posting Yak Master
126 Posts |
Posted - 2008-09-15 : 12:56:36
|
| Perfect, I was forgetting the end. But, after the else I was hoping to return any Nulls as 0. Below is what I'm trying, but I keep getting errors. I don't think coalesce can work here, is there another solution?(case when Coalesce (overPeakTotal, 0)=0 then 0 else (overPeakTotal - (Coalesce ((sum (OrderItemType.dollarValue*OrderItem.quantity)),0))end) as amtOverPeakTotal |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-15 : 13:00:47
|
| please post some data and explain what you're trying to achieve. that will make it much clearer than the query you post. |
|
|
|
crugerenator
Posting Yak Master
126 Posts |
Posted - 2008-09-15 : 13:41:16
|
| case when Coalesce (overPeakTotal, 0)=0 then 0 else (Coalesce (overPeakTotal - (sum (OrderItemType.dollarValue*OrderItem.quantity)), 0))end as amtOverPeakTotalCame up with that and it works. |
|
|
|
|
|
|
|
|
case...when...else...end