SQL Server - Rewrite Access SQL for SQL Server

Asked By Mike Will via SQLMonster.com

22-Aug-07 08:08 AM

I've tried unsuccessfully to rewrite this query from Access in a way that SQL
Server can interpret it.

This is the Access query:

[OTHours]+[OTType]/1000000+NZ([OTRefusal],0)/10000000+IIf(tblOvertime.
OTRoster=tblEmployee.OTRoster,0.00000001,0.00000002)


Here is what I've tried in SQL Server:

[OTHours]+[OTType] / 1000000+[OTRefusal] / 10000000+(CASE WHEN tblEmployee.
Overtime = tblEmployee.OTRoster THEN 0.0000001 ELSE 0.0000002 END)

I appreciate any help.

--
Message posted via http://www.sqlmonster.com

22-Aug-07 08:53 AM

Instead of NZ([OTRefusal],0)

use one of the followings

ISNULL([OTRefusal],0)
COALESCE([OTRefusal],0)

Madhivanan

22-Aug-07 09:07 AM

The syntax that was tried in SQL Server appears to be valid syntax, at
least I was able to get it to execute.  Is it giving you a syntax
error?  What error?  Or is it giving the wrong results?  How are the
results wrong?

In general any time I mix addition or subtraction with multiplication
or division I use (parentheses) to explicitly group items so it is
totally clear in what order the calculation should be made.  There is
an order of precedence for operators, but I never let it come into
play, as I have no interest in memorizing it and possibly remembering
it incorrectly.  I suggest that whatever else you do you  add
parentheses as appropriate.  (The one set of parentheses you have,
around the CASE expression, is actually not doing anything.)

The comment about replacing NZ with COALESCE is correct.

Roy Harvey
Beacon Falls, CT

On Wed, 22 Aug 2007 12:08:38 GMT, "Mike Will via SQLMonster.com"