How to use MOD Oracle SQL Function: 99.9%9

This is related to mod Oracle SQL function. I hope to write about the simple example of mod in Oracle SQL and the invalid use of mod in Oracle plsql Function. As you know module or Mod returns the remaining amount after dived two numbers. The same thing happens in the Oracle mod function as well

Definition of Mod by Oracle (Source: Oracle)

MOD returns the remainder (modulus) of argument 1 divided by argument 2. The result is negative only if argument 1 is negative.

--
--MOD function in Oracle/PLSQL syntax:
MOD( m, n )

mod(integer_type, integer_type)

--Syntax internal function 
m- n * floor (m/ n)
--

• M: Numeric value
• N: Numeric Value
• Return of the function
  1. Return remains once m divided by n
  2. Return null if one of the parameters is null
  3. Return error if parameters are not valid (Ex m or n is VARCHAR)

Input parameter datatype can be any of these

mod oracle sql function valid from Oracle 8i, oracle 9i, oracle 10g oracle 11g, and Oracle 12g.

Mod in Oracle sql Examples

--
Ex: 1
SELECT MOD(5,2) FROM dual;
-- Output
MOD(5,2)
1
--

--
Ex:2
SELECT MOD(-5,2) FROM dual;
-- Output
MOD(-5,2)
-1
--

--
Ex:3
SELECT MOD(5,-2) FROM dual;
-- Output
MOD(5,-2)
1
--

--
Ex:4
SELECT MOD(-5,-2) FROM dual;
-- Output
MOD(-5,-2)
-1
--

--
Ex:5
SELECT MOD(5.2,2) FROM dual;
-- Output
MOD(5.2,2)
1.2
--

Ex:6
SELECT MOD(5, 0)FROM DUAL;
-- Output
MOD(5, 0)
5
--

Mod SQL Function Database Example

Let’s think about how we can use MOD in real database operations. Think about we are processing orders in inventory whether you need to check whether orders are completed or still processing. then you can use the mod function to check the status of the order below I show a sample query to check the status.

Status: 1- New, 2-processing 3-verified 4-Authorised 5- completed

--
SELECT 
CASE MOD(status, 5)
       WHEN 0
       THEN 'Order Completed'
       ELSE 'Order Still processing'
       END AS order_status
FROM   orders
--

Invalid use of mod in oracle sql

--
Ex:1
SELECT MOD('Enni', 'Code')
FROM DUAL;
-- Output
Error starting at line : 1 in command -
SELECT MOD('Enni', 'Code')
FROM DUAL;
Error report -
ORA-01722: invalid number
--

--
SELECT MOD(5)
FROM DUAL
-- output
Error report -

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
--

--
SELECT MOD(10, 2, 3)
FROM DUAL
--output
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
--

Summarizing

Oracle mod function is a simple function by Oracle plsql returning the remaining amount once divided two numbers. here I try to add simple examples and hope those help your learning. thank you for reading us and we welcome comments and suggestions. share this if things are valuable to others.

All SQL Related posts https://ennicode.com/category/sql/