-- SQL Server Express 2008 SP1 (10.0.2531)
-- No MOD available, but %

SELECT 12 % 5 = 2
SELECT -12 % 5 = -2
SELECT -12 % -5 = -2
SELECT 12 % -5 = 2
SELECT CAST(NULL AS INTEGER) % 5 = NULL
SELECT CAST(NULL AS INTEGER) % CAST(NULL AS INTEGER) = NULL
SELECT 12 % CAST(NULL AS INTEGER) = NULL
SELECT 12 % 0 = Error 8134 Division by Zero
SELECT -12 % 0 = Error 8134 Division by Zero
SELECT 0 % 5 = 0
SELECT 0 % -5 = 0
SELECT 0 % 0 = Error 8134 Division by Zero

-- IBM DB2 Express-C 9.7.0.441 LUW on Windows
VALUES MOD (12, 5) = 2
VALUES MOD (-12, 5) = -2
VALUES MOD (-12, -5) = -2
VALUES MOD (12, -5) = 2
VALUES MOD (CAST(NULL AS INTEGER), 5) = NULL
VALUES MOD (CAST(NULL AS INTEGER), CAST(NULL AS INTEGER)) = NULL
VALUES MOD (12, CAST(NULL AS INTEGER)) = NULL
VALUES MOD (12, 0) = Error SYSFUN:02 Division by Zero
VALUES MOD (-12, 0) = Error SYSFUN:02 Division by Zero
VALUES MOD (0, 5) = 0
VALUES MOD (0, -5) = 0
VALUES MOD (0, 0) = Error SYSFUN:02 Division by Zero

brgds

Philipp Post

--CELKO-- <jcelko212 (AT) earthlink (DOT) net> wrote in news:0ea81dbb-8093-41ce-b7a7-
a18c4f2f24db (AT) g19g2000yqc (DOT) googlegroups.com:

Another case that may give different results is MOD(NULL,0). Mimer SQL
returns NULL.


/Jarl

Forgot to add:
In Teradata MOD must be written as "12 MOD 5" instead of MOD(12,5)
And "NULL MOD 0" returns NULL.

Dieter

Dieter Noeth wrote:

On 2010-06-10 07:51, Jarl Hermansson wrote:
db2 9.5 returnes null in this case as well


/Lennart

In article <huql0r$3bb$1 (AT) news (DOT) eternal-september.org>,
Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:

H2 Database 1.2.137 does likewise.

--
John B. Matthews
trashgod at gmail dot com
<http://sites.google.com/site/drjohnbmatthews>

Here is the ANSI/ISO definition for reference:

MOD(n, m) is the function that performs modulo or remainder
arithmetic. If either n or m is NULL, then the result is NULL. If m is
zero, then we get a division by zero exception. Otherwise, the result
is the unique non-negative exact numeric value r with scale zero such
that

1)r has the same sign as n.
2)the absolute value of r is less than the absolute value of m.
3)n = m * k + r for some exact numeric value k with scale zero.

a, b, c, d, e, f, g, h, i, j, k, l
2,-2,-2, 2, , , , , , 0, 0,
<<

Are the comma/empty strings to show NULLs, errors, or something else?

On 2010-06-11 01:03, --CELKO-- wrote:
[...]
For negative n should r be positive or negative?

[...]

/Lennart

On 2010-06-11, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:
from the above I think that version of the standard only allows for whole number n



--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

In article <b2465168-6750-4dc7-b983-
ee384eafa07f (AT) r27g2000yqb (DOT) googlegroups.com>, jcelko212 (AT) earthlink (DOT) net
says...
Sorry; all are Nulls. They just didn't show as such when I did a
copy/paste from isql. No exceptions were returned.

Dave