SQL Query Error (Divide by zero)

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

General SQL Server Forums

New to SQL Server Programming

SQL Query Error (Divide by zero)

Author

Topic

SuiCidE
Starting Member

4 Posts

Posted - 2010-05-22 : 08:04:06

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-22 : 08:27:07

?

--
Gail Shaw
SQL Server MVP

SuiCidE
Starting Member

4 Posts

Posted - 2010-05-22 : 08:43:51

Hi, the trouble i'm having is that I keep getting the umeric value out of range (null) error.
I'm fairly new to SQL terminology (I've only done basic stuff with VBScript / Access, i'm a beginner) and what i'm wanting to do is....


DBConn.Execute("SELECT * FROM `Clans` ORDER BY `TimesJoined` / (`TimesRestricted` + `TimesJoined`) DESC")

Of course when I plus the 2 values together it is equal to 0 a lot of times, and of course I don't know how to go about fixing it.
Basically I want to end up with a list of "Clans" and their uptime (times joined divided by (`TimesJoined` + `TimesRestricted`)
Help is greatfully appreciated.

Thanks.

MRdNk
Starting Member

6 Posts

Posted - 2010-05-22 : 10:50:23

I don't know what the answer is to your actual question, but its a basic law of maths, that you can't divide by 0, try it in any computer calculation and you'll get an error - try it in your calculator.

Kristen
Test

22859 Posts

Posted - 2010-05-22 : 12:19:28

[code]DBConn.Execute("SELECT * FROM `Clans` ORDER BY `TimesJoined` / NullIf((`TimesRestricted` + `TimesJoined`), 0) DESC")

or

DBConn.Execute("SELECT *
FROM `Clans`
WHERE (`TimesRestricted` + `TimesJoined`) <> 0
ORDER BY `TimesJoined` / (`TimesRestricted` + `TimesJoined`) DESC")
[/code]
First will give you ALL rows, but will work around the divide-by-zero issue by converting it to NULL (which will sort either first or last). Second will specifically explude any rows where the result of the division would be zero - so pick which outcome you prevfer

but with those backticks it looks like MySQL and I don't know if that supports the NullIf() function.

(This is a Microsoft SQL Forum, so folk here may not be knowledgeable about MySQL)

" its a basic law of maths, that you can't divide by 0, try it in any computer calculation and you'll get an error"

Yeah, but the O/P is specifically asking how to work around when the divisor is zero ...

SuiCidE
Starting Member

4 Posts

Posted - 2010-05-22 : 19:23:07

Doesn't work..


	dim db
	dbconnect()
	set db = DBConn.Execute("SELECT * FROM `Clans` ORDER BY `TimesJoined` / NullIf((`TimesRestricted` + `TimesJoined`), 0) DESC")
	do until db.eof = true
		addchat vbwhite, db.fields(0)
		db.movenext
	loop
	dbconn.close

gives this error....
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'NullIf' in expression..

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2010-05-23 : 02:02:42

If nullif doesn't work, you can try this:

DBConn.Execute("SELECT * FROM `Clans` ORDER BY `TimesJoined` / (isnull(`TimesRestricted`,0) + isnull(`TimesJoined`,0)) DESC")

or

DBConn.Execute("SELECT * FROM `Clans` ORDER BY `TimesJoined` / (coalesce(`TimesRestricted`,0) + coalesce(`TimesJoined`,0)) DESC")

I think coalescing or "isnull" each individual expression might be the issue since the total of timesrestricted+timesjoined is not necessarily null even if 1 of the expressions is null.

SuiCidE
Starting Member

4 Posts

Posted - 2010-05-23 : 03:22:46

Neither work, both give errors.
[Microsoft][ODBC Microsoft Access Driver] Wrong number of arguments used with function in query expression '`TimesJoined` / (ISNULL(`TimesRestricted`, 0) + ISNULL(`TimesJoined`, 0))'..

and

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'coalesce' in expression.

Kristen
Test

22859 Posts

Posted - 2010-05-23 : 03:44:53

quote:
Originally posted by SuiCidE

Doesn't work..
...
gives this error....
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'NullIf' in expression..

Are you using Microsoft Access? This is a Microsoft SQL Server forum, if you are using something different it would be helpful if you at least said so so that folk don't spend time giving you advice that turns out not to work; you may be better off asking in a Microsoft Access Forum as there may not be many/any people knowledgeable about Access here

In Access can you use IIF() to force the divisor to be NULL when the summation of the two columns is zero? Alternatively try the second suggestion I made.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources