SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Division String Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

simplymidori
Starting Member

9 Posts

Posted - 02/22/2008 :  14:07:51  Show Profile  Send simplymidori an AOL message  Reply with Quote
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
6062 Posts

Posted - 02/22/2008 :  15:16:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/23/2008 :  04:24:35  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/23/2008 :  08:14:31  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/23/2008 :  13:37:35  Show Profile  Reply with Quote
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.
Go to Top of Page

simplymidori
Starting Member

9 Posts

Posted - 02/25/2008 :  07:15:51  Show Profile  Send simplymidori an AOL message  Reply with Quote
Thanks all for your time - I will give this a try this morning and give you feedback shortly. These strings make total sense.
Go to Top of Page

simplymidori
Starting Member

9 Posts

Posted - 02/25/2008 :  07:28:58  Show Profile  Send simplymidori an AOL message  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/25/2008 :  07:30:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000