The MOD() function in various SQL products works differently. I need
to update a table in SQL FOR SMARTIES. Mind helping me out? Tell me
the name and release of your SQL, and the results of these function
calls.

MOD (12, 5) =

MOD(-12, 5) =

MOD (-12, -5) =

MOD (12, -5) =

MOD (NULL, 5) =

MOD (NULL, NULL) =

MOD (12, NULL) =

MOD (12, 0) =

MOD (-12, 0) =

MOD (0, 5) =

MOD (0, -5) =

MOD (0, 0) =

I originally found three different approaches to this problem. Answers
were 2, -2, NULL, error, 3, -3, 12 and -12. I just hope that things
are more consistent today.

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

<http://www.h2database.com/>

Welcome to H2 Shell 1.2.137 (2010-06-06)
....
sql> list
Result list mode is now on
sql> SELECT MOD (12, 5) FROM DUAL;
SELECT MOD(-12, 5) FROM DUAL;
SELECT MOD (-12, -5) FROM DUAL;
SELECT MOD (12, -5) FROM DUAL;
SELECT MOD (NULL, 5) FROM DUAL;
SELECT MOD (NULL, NULL) FROM DUAL;
SELECT MOD (12, NULL) FROM DUAL;
SELECT MOD (12, 0) FROM DUAL;
SELECT MOD (-12, 0) FROM DUAL;
SELECT MOD (0, 5) FROM DUAL;
SELECT MOD (0, -5) FROM DUAL;
SELECT MOD (0, 0) FROM DUAL;
2: 2
(1 row, 37 ms)
sql> -2: -2
(1 row, 1 ms)
sql> -2: -2
(1 row, 0 ms)
sql> 2: 2
(1 row, 0 ms)
sql> NULL: null
(1 row, 0 ms)
sql> NULL: null
(1 row, 0 ms)
sql> NULL: null
(1 row, 1 ms)
sql> Error: org.h2.jdbc.JdbcSQLException: Division by zero: "MOD(12,
0)"; SQL statement:
SELECT MOD (12, 0) FROM DUAL [22012-137]
org.h2.jdbc.JdbcSQLException: Division by zero: "MOD(12, 0)";
....
sql> Error: org.h2.jdbc.JdbcSQLException: Division by zero: "MOD(-12,
0)"; SQL statement:
SELECT MOD (-12, 0) FROM DUAL [22012-137]
org.h2.jdbc.JdbcSQLException: Division by zero: "MOD(-12, 0)";
....
sql> 0: 0
(1 row, 1 ms)
sql> 0: 0
(1 row, 0 ms)
sql> Error: org.h2.jdbc.JdbcSQLException: Division by zero: "MOD(0, 0)";
SQL statement:
SELECT MOD (0, 0) FROM DUAL [22012-137]
org.h2.jdbc.JdbcSQLException: Division by zero: "MOD(0, 0)";
....
sql>

[Stack traces elided.]

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

On 2010-06-08 01:47, --CELKO-- wrote:
db2 9.5 fixpak 5 on Linux

[db2inst1@wb11 ~]$ db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release
"SQL09055"
with level identifier "06060107".
Informational tokens are "DB2 v9.5.0.5", "s091123", "MI00316", and Fix Pack
"5".
Product is installed at "/opt/IBM/db2/V9.5".

[db2inst1@wb11 ~]$ db2 -tvf celko.sql | tee celko.out
values ( 'MOD (12, 5) =',MOD (12, 5) )

1 2
------------- -----------
MOD (12, 5) = 2

1 record(s) selected.


values ( 'MOD(-12, 5) =', MOD(-12, 5) )

1 2
------------- -----------
MOD(-12, 5) = -2

1 record(s) selected.


values ( 'MOD (-12, -5) =', MOD (-12, -5) )

1 2
--------------- -----------
MOD (-12, -5) = -2

1 record(s) selected.


values ( 'MOD (12, -5) =', MOD (12, -5) )

1 2
-------------- -----------
MOD (12, -5) = 2

1 record(s) selected.


values ( 'MOD (NULL, 5) =', MOD (NULL, 5) )
SQL0206N "NULL" is not valid in the context where it is used.
SQLSTATE=42703

values ( 'MOD (NULL, NULL) =', MOD (NULL, NULL) )
SQL0206N "NULL" is not valid in the context where it is used.
SQLSTATE=42703

values ( 'MOD (12, NULL) =', MOD (12, NULL) )
SQL0206N "NULL" is not valid in the context where it is used.
SQLSTATE=42703

values ( 'MOD (12, 0) =', MOD (-12, 0) )

1 2
------------- -----------
SQL0443N Routine "SYSFUN.MOD" (specific name "MOD1") has returned an error
SQLSTATE with diagnostic text "SYSFUN:02". SQLSTATE=38552

values ( 'MOD (-12, 0) =', MOD (-12, 0) )

1 2
-------------- -----------
SQL0443N Routine "SYSFUN.MOD" (specific name "MOD1") has returned an error
SQLSTATE with diagnostic text "SYSFUN:02". SQLSTATE=38552

values ( 'MOD (0, 5) =', MOD (0, 5) )

1 2
------------ -----------
MOD (0, 5) = 0

1 record(s) selected.


values ( 'MOD (0, -5) =', MOD (0, -5) )

1 2
------------- -----------
MOD (0, -5) = 0

1 record(s) selected.


values ( 'MOD (0, 0) =', MOD (0, 0) )

1 2
------------ -----------
SQL0443N Routine "SYSFUN.MOD" (specific name "MOD1") has returned an error
SQLSTATE with diagnostic text "SYSFUN:02". SQLSTATE=38552





mysql> select @@version;
+----------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


mysql> select 'MOD (12, 5) =',MOD (12, 5) ;
+---------------+-------------+
1 row in set (0.01 sec)

mysql> select 'MOD(-12, 5) =', MOD(-12, 5) ;
+---------------+-------------+
1 row in set (0.00 sec)

mysql> select 'MOD (-12, -5) =', MOD (-12, -5) ;
+-----------------+---------------+
1 row in set (0.00 sec)

mysql> select 'MOD (12, -5) =', MOD (12, -5) ;
+----------------+--------------+
1 row in set (0.00 sec)

mysql> select 'MOD (NULL, 5) =', MOD (NULL, 5) ;
+-----------------+---------------+
1 row in set (0.00 sec)

mysql> select 'MOD (NULL, NULL) =', MOD (NULL, NULL) ;
+--------------------+------------------+
1 row in set (0.00 sec)

mysql> select 'MOD (12, NULL) =', MOD (12, NULL) ;
+------------------+----------------+
1 row in set (0.00 sec)

mysql> select 'MOD (12, 0) =', MOD (-12, 0) ;
+---------------+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'MOD (-12, 0) =', MOD (-12, 0) ;
+----------------+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'MOD (0, 5) =', MOD (0, 5) ;
+--------------+------------+
1 row in set (0.00 sec)

mysql> select 'MOD (0, -5) =', MOD (0, -5) ;
+---------------+-------------+
1 row in set (0.00 sec)

mysql> select 'MOD (0, 0) =', MOD (0, 0) ;
+--------------+------------+
1 row in set, 1 warning (0.00 sec)



/Lennart

On Mon, 7 Jun 2010 16:47:31 -0700 (PDT), --CELKO--
<jcelko212 (AT) earthlink (DOT) net> wrote:

Microsoft Visual FoxPro version 9 SP 2. I used the statements
select <expression> from csys
(csys is an app table.) My answers are on the same line as your
expressions.

Sincerely,

Gene Wirchenko

--CELKO-- <jcelko212 (AT) earthlink (DOT) net> writes:

=====
$ uname -o
GNU/Linux

$ psql --version | head -n 1
psql (PostgreSQL) 8.4.4

$ grep NULL ~/.psqlrc
-- Display string for NULLs
\pset null '‼NULL‼'

$ psql -e bignose << _EOT_
SELECT MOD (12, 5);
SELECT MOD(-12, 5);
SELECT MOD(-12, -5);
SELECT MOD(12, -5);
SELECT MOD(NULL, 5);
SELECT MOD(NULL, NULL);
SELECT MOD(12, NULL);
SELECT MOD(12, 0);
SELECT MOD(-12, 0);
SELECT MOD(0, 5);
SELECT MOD(0, -5);
SELECT MOD(0, 0);
_EOT_

SELECT MOD (12, 5);
mod
-----
2
(1 row)

SELECT MOD(-12, 5);
mod
-----
-2
(1 row)

SELECT MOD(-12, -5);
mod
-----
-2
(1 row)

SELECT MOD(12, -5);
mod
-----
2
(1 row)

SELECT MOD(NULL, 5);
mod
--------
‼NULL‼
(1 row)

SELECT MOD(NULL, NULL);
ERROR: function mod(unknown, unknown) is not unique
LINE 1: SELECT MOD(NULL, NULL);
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.

SELECT MOD(12, NULL);
mod
--------
‼NULL‼
(1 row)

SELECT MOD(12, 0);
ERROR: division by zero

SELECT MOD(-12, 0);
ERROR: division by zero

SELECT MOD(0, 5);
mod
-----
0
(1 row)

SELECT MOD(0, -5);
mod
-----
0
(1 row)

SELECT MOD(0, 0);
ERROR: division by zero
=====

--
\ “All opinions are not equal. Some are a very great deal more |
`\ robust, sophisticated and well supported in logic and argument |
_o__) than others.” —Douglas Adams |
Ben Finney

--CELKO--, 08.06.2010 01:47:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT MOD (12, 5) FROM DUAL;

MOD(12,5)
----------
2

SQL> SELECT MOD(-12, 5) FROM DUAL;

MOD(-12,5)
----------
-2

SQL> SELECT MOD (-12, -5) FROM DUAL;

MOD(-12,-5)
-----------
-2

SQL> SELECT MOD (12, -5) FROM DUAL;

MOD(12,-5)
----------
2

SQL> SELECT MOD (NULL, 5) FROM DUAL;

MOD(NULL,5)
-----------


SQL> SELECT MOD (NULL, NULL) FROM DUAL;

MOD(NULL,NULL)
--------------


SQL> SELECT MOD (12, NULL) FROM DUAL;

MOD(12,NULL)
------------


SQL> SELECT MOD (12, 0) FROM DUAL;

MOD(12,0)
----------
12

SQL> SELECT MOD (-12, 0) FROM DUAL;

MOD(-12,0)
----------
-12

SQL> SELECT MOD (0, 5) FROM DUAL;

MOD(0,5)
----------
0

SQL> SELECT MOD (0, -5) FROM DUAL;

MOD(0,-5)
----------
0

SQL> SELECT MOD (0, 0) FROM DUAL;

MOD(0,0)
----------
0

SQL>

Regards
Thomas

In article <0ea81dbb-8093-41ce-b7a7-
a18c4f2f24db (AT) g19g2000yqc (DOT) googlegroups.com>, jcelko212 (AT) earthlink (DOT) net
says...
Sybase SQL Anywhere 9.0.2.3886:

select MOD (12, 5) as a,
MOD(-12, 5) as b,
MOD (-12, -5) as c,
MOD (12, -5) as d,
MOD (NULL, 5) as e,
MOD (NULL, NULL) as f,
MOD (12, NULL) as g,
MOD (12, 0) as h,
MOD (-12, 0) as i,
MOD (0, 5) as j,
MOD (0, -5) as k,
MOD (0, 0) as l

returns:

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

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

Mimer SQL v10.1.0:

MOD (12, 5) = 2
MOD (-12, 5) = -2
MOD (-12, -5) = -2
MOD (12, -5) = 2
MOD (cast(NULL as integer), 5) = <NULL>
MOD (cast(NULL as integer), cast(NULL as integer)) = <NULL>
MOD (12, cast(NULL as integer)) = <NULL>
MOD (12, 0) -> error -10304, Division by zero attempted
MOD (-12, 0) -> error -10304, Division by zero attempted
MOD (0, 5) = 0
MOD (0, -5) = 0
MOD (0, 0) -> error -10304, Division by zero attempted

On 2010-06-08 06:34, Lennart Jonsson wrote:
[...]

Ah yes, silly me. Seeing Jarls post reminded me that I had to cast null
values MOD (cast(NULL as int), 5)

1
-----------
-

1 record(s) selected.


values MOD (cast(NULL as int), cast(NULL as int))

1
-----------
-

1 record(s) selected.


values MOD (12, cast(NULL as int))

1
-----------
-

1 record(s) selected.

[...]

Teradata 12.00.03.07, same results for any other release: