| Author |
 Topic  |
|
|
simplymidori
Starting Member
9 Posts |
 Posted - 02/22/2008 : 14:07:51
|
Hello,
I probably have this all wrong but I'm giving it a shot and have searched this afternoon for a solution.
CASE WHEN (SOLD='0','0' ELSE CONVERT (Numeric, PolAvailsbyRetail.Sold) / CONVERT (Numeric, PolAvailsbyRetail.TheAmount) * 100 END
The red is probably wrong.
So what I am trying to do is my data columns are vachar and converting them to numeric.
I would like to divide SOLD/TheAmount and making sure I'm not dividing by 0 in SOLD.
Any Suggestions.
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 02/22/2008 : 15:16:54
|
each WHEN should result in the same datatype
to avoid devide by zero error then you need to check for PolAvailsbyRetail.TheAmount = '0'
so try this:
select CASE
WHEN PolAvailsbyRetail.TheAmount='0' THEN CONVERT (Numeric, 0)
ELSE CONVERT (Numeric, PolAvailsbyRetail.Sold) / CONVERT (Numeric, PolAvailsbyRetail.TheAmount) * 100
END
from ...
Be One with the Optimizer
TG |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 02/23/2008 : 04:24:35
|
to avoid division by zero error, change denominator like this:-
select CASE
WHEN PolAvailsbyRetail.TheAmount='0' THEN CONVERT (Numeric, 0)
ELSE CONVERT (Numeric, PolAvailsbyRetail.Sold) / NULLIF(CONVERT (Numeric, PolAvailsbyRetail.TheAmount),0) * 100
END
from ... |
Edited by - visakh16 on 02/23/2008 04:25:25 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 02/23/2008 : 08:14:31
|
visakh16, is there a reason you left the CASE in there (which checks for 0 and results to 0)?
If it is there, won't your NULLIF never be used?
And if you remove the CASE in favor of the NULLIF then the result will be NULL rather than the user's original intention of 0.
is suppose you could do this:
select
isNULL(CONVERT (Numeric, PolAvailsbyRetail.Sold) / NULLIF(CONVERT (Numeric, PolAvailsbyRetail.TheAmount),0) * 100,0)
from ...
Be One with the Optimizer
TG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 02/23/2008 : 13:37:35
|
quote:
Originally posted by TG
visakh16, is there a reason you left the CASE in there (which checks for 0 and results to 0)?
If it is there, won't your NULLIF never be used?
And if you remove the CASE in favor of the NULLIF then the result will be NULL rather than the user's original intention of 0.
is suppose you could do this:
select
isNULL(CONVERT (Numeric, PolAvailsbyRetail.Sold) / NULLIF(CONVERT (Numeric, PolAvailsbyRetail.TheAmount),0) * 100,0)
from ...
Be One with the Optimizer
TG
OOps i had missed that. i was just giving another way of doing that.I also meant the same thing . Make denominator NULL using NULLIF if its 0 and then use ISNULL() function to return result as 0. Sorry didnt went through your soln fully.  |
|
|
|
simplymidori
Starting Member
9 Posts |
Posted - 02/25/2008 : 07:15:51
|
| Thanks all for your time - I will give this a try this morning and give you feedback shortly. These strings make total sense. |
|
|
|
simplymidori
Starting Member
9 Posts |
Posted - 02/25/2008 : 07:28:58
|
to avoid division by zero error, change denominator like this:-
select CASE
WHEN PolAvailsbyRetail.TheAmount='0' THEN CONVERT (Numeric, 0)
ELSE CONVERT (Numeric, PolAvailsbyRetail.Sold) / NULLIF(CONVERT (Numeric, PolAvailsbyRetail.TheAmount),0) * 100
END
from ...
--------------------------------------------------------------------------------
Edited by - visakh16 on 02/23/2008 04:25:25
Thank you this worked.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/25/2008 : 07:30:35
|
Why all these converts?SELECT CASE
WHEN PolAvailsbyRetail.TheAmount = 0 THEN 0.0
ELSE 100.0 * PolAvailsbyRetail.Sold / PolAvailsbyRetail.TheAmount
END
E 12°55'05.25"
N 56°04'39.16" |
|
|
| |
Topic |
|
Division String Question
