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 Oracle SQL 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
- Return remains once m divided by n
- Return null if one of the parameters is null
- Return error if parameters are not valid (Ex m or n is VARCHAR)
Input parameter datatype can be any of these
Mod return M or n |
SMALLINT | both m and n SMALLINT |
INTEGER | if m or n INTEGER other paramenter SMALLINT |
INTEGER | if m or n INTEGER other parameter SMALLINT |
BINARY_DOUBLE | both parameters BINARY_FLOAT |
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 by 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/