Division String Question - 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 2000 Forums

Transact-SQL (2000)

Division String Question

Author

Topic

simplymidori
Starting Member

9 Posts

Posted - 2008-02-22 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-22 : 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

52326 Posts

Posted - 2008-02-23 : 04:24:35

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-23 : 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

52326 Posts

Posted - 2008-02-23 : 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 - 2008-02-25 : 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 - 2008-02-25 : 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

30421 Posts

Posted - 2008-02-25 : 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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources