Numeric functions in Oracle SQL

  3shTech (trainings@trishtechnology.com)

String Functions

Group Functions

Date Functions

Conversion Functions

Analytical Functions

Numeric Functions

Numeric functions accept numeric input and return numeric values.

NUMERIC FUNCTIONS:-

·       ABS

·       SIGN

·       SQRT

·       MOD

·       NVL

·       POWER

·       EXP

·       LN

·       LOG

·       CEIL

·       FLOOR

·       ROUND

·       TRUNC

·       BITAND

·       GREATEST

·       LEAST

·       COALESCE

1) ABS:

·       Absolute value is the measure of the magnitude of value.

·       Absolute value is always a positive number.

Syntax: – ABS (value)

Example:–

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


 
2) SIGN:

·       Sign gives the sign of a value.

Syntax: – SIGN (value)

Example:–

SQL>SELECT SIGN (5), SIGN (-5), SIGN (0), SIGN (null), SIGN (0.5) FROM DUAL;



 

 
3) SQRT:

·       This will give the square root of the given value.

·       Input value must be positive.

Syntax: – SQRT (value)

Example:-

SQL>SELECT SQRT (2), SQRT (4), SQRT (9), SQRT (0), SQRT (null) FROM DUAL; 


In case, if we enter a negative number then system will raise an error.

SQL>SELECT SQRT (-2) FROM DUAL;
 
Output :

ORA-01428: argument ‘-2’ is out of range

 
4) MOD:

·       This will give the remainder.

Syntax: – MOD (value, divisor)

Example:–

SQL>SELECT MOD (1,5),MOD (5,1),MOD (0,0),MOD (null, null),MOD (7,4),MOD (4,7),MOD (-4,7) FROM DUAL;


 

 
5) NVL:

·       NVL replaces the NULL value with the specified value.

·       Nvl function takes two input parameters.

I.e. If first parameter is null then it will return the second parameter otherwise it will return the first parameter.

Syntax: – NVL (,)

Example:–

SQL>SELECT NVL (null, 5), NVL (100, 10), NVL (null, null) FROM DUAL;


 

In emp table for Emplyee ‘SMITH’, “comm” value is null…

SQL>SELECT empno, ename, comm, deptno FROM emp WHERE empno=7369;


 

Let’s use NVL function to convert the null value to known value for “comm” column in EMP table.

SQL>SELECT empno, ename, nvl (comm, 100) emp_comm, deptno FROM emp WHERE empno=7369; 



 
6)POWER:

·       POWER function is used to raise a value to a given exponent.

Syntax: – POWER (value, exponent)

Example:–

SQL>SELECT POWER (2,3),POWER (3,2),POWER (1,1),POWER (0,0),POWER (null, null),POWER (-2,-3) FROM DUAL;



 

 
7)EXP:

·       This function will raise the value to the given POWER.

Syntax: – EXP (value)

Example:–

SQL>SELECT EXP (0), EXP (1), EXP (2), EXP (null), EXP (-2) FROM DUAL;


 

 
8) LN:

·       This function is based on natural or base e logarithm.

·       Input must be greater than zero.

Syntax: – LN (value)

Example:–

SQL>SELECT LN (1), LN (2), LN (null) FROM DUAL; 



Suppose we try to enter a value that is zero or negative number then system will raise an error.

SELECT LN (1), LN (2), LN (0), LN (null) FROM DUAL;
 
Output :
 

ORA-01428: argument ‘0’ is out of range.

LN and EXP are reciprocal to each other.

EXP (3) = 20.0855369

LN (20.0855369) = 3
 
9) LOG:

·       This function is based on 10 based logarithms.

·       Input value must be greater than zero which is positive only.

Syntax: – LOG (10, value)

Example:–

SQL>SELECT LOG (10, 1), LOG (10, 10), LOG (10, null) FROM DUAL; 



SQL>SELECT LN (3), LOG (EXP (1), 3) FROM DUAL;

 

 
10) CEIL:

·       This function will produce a whole number that is greater than or equal to the specified value.

Syntax: – CEIL (value)

  Example:–

SQL>SELECT CEIL(5.1),CEIL(5),CEIL(-5),CEIL(-5.1),CEIL(0),CEIL(null) FROM DUAL;


 

11) FLOOR:

·       This function will produce a whole number that is less than or equal to the specified value.

Syntax: – FLOOR (value)

  Example:–

SQL>SELECT FLOOR(5.1),FLOOR(5),FLOOR(-5),FLOOR(-5.1),FLOOR(0),FLOOR(null) FROM DUAL;


 

12) ROUND:

·       This function will ROUND numbers to a given number of digits of precision.

Syntax: – ROUND (value, precision)

  Example:–

SQL>SELECT ROUND (123.2345), ROUND (123.2345, 2), ROUND (123.2354, 2) FROM DUAL;


 

SQL>SELECT ROUND (123.2345, 0), ROUND (123.2345,-2), ROUND (123.2354,-4) FROM DUAL; 



 
13) TRUNC:

·       This function will truncates or chops off digits of precision from a number.

Syntax: – TRUNC (value, precision)

  Example:–

SQL>SELECT TRUNC (123.2345), TRUNC (123.2345, 2), TRUNC (123.2354, 3) FROM DUAL;


 

 
14) BITAND:

·       This function will perform bitwise and operation.

Syntax: – BITAND (value1, value2)

  Example:–

SQL>SELECT BITAND (2,3),BITAND (1,1),BITAND (0,0),BITAND (0,1),BITAND (null, null),BITAND (-1,-2) FROM DUAL;


 

 
15) GREATEST:

·       This function will give the greatest number in list of values.

·       It will take ‘n’ number of arguments but data type should be same.

Syntax: – GREATEST (value1, value2….valuen)

Example:–

SQL>SELECT GREATEST (1, 4, null, 0,-5) FROM DUAL;


 

SQL>SELECT GREATEST (1, 4, 1000, 1.005,-5) FROM DUAL;


 

·       If all the values are zeros then it will display zero.

·       If all the parameters are nulls then it will display nothing.

·       If any of the parameters is null it will display nothing.

 
16) LEAST:

·       This function will give the least number in list of values.

·       It will take ‘n’ number of arguments but data type should be same.

Syntax: – LEAST (value1, value2….valuen)

  Example:–

SQL>SELECT LEAST (1, 2, 3), LEAST (-1, -2, -3) FROM DUAL;


 

·       If all the values are zeros then it will display zero.

·       If all the parameters are nulls then it will display nothing.

·       If any of the parameters is null it will display nothing.

 
17) COALESCE:

·       This function will return first non-null value in the specified list.

Syntax: – COALESCE (value1, value2….valuen)

  Example:–

SQL>SELECT COALESCE (null, 2, null, 1) FROM DUAL;



Happy Learning 😊

Thank you!!

 

Comments

Post a Comment

Popular posts from this blog

AD_ZD_TABLE : Steps to follow while creating table in R12.2.*

Image
  *********** * GRANTS * ********** Team – do not use Grant statements in the SQL scripts while migrating through STAT , instead use  EXEC AD_ZD.GRANT_PRIVS. Manual steps run in DEV Environment: 1. As XXCUST, manually Create/Alter custom table in EBSDEV or EBSPROJ (XXCUST schema for all custom tables). 2. In EBSDEV/EBSPROJ: If creating the table, run:  AD_ZD_TABLE.UPGRADE (‘SCHEMA’,’TABLE’); If altering the table, run: AD_ZD_TABLE.PATCH (‘SCHEMA’,’TABLE’); 3. Insert at least one row of data in the custom table. * This is mandatory for XDF to extract the table definition and for successful Stat archive set creation. 4. As table owner, grant privileges on new/altered table and editioned view to APPS, primarily so AD_ZD.GRANT_PRIVS can be run for privileges. GRANT ALL ON SCHEMA.TABLE TO APPS WITH GRANT OPTION; GRANT ALL ON SCHEMA.EDITIONED_VIEW# TO APPS WITH GRANT OPTION; 5. If additional privileges are required on the new/altered table, the following needs to be r...
Read more

Initiating a webservice API from Plsql package !!

Image
create or replace PACKAGE      xxat_3pl_webservice_pkg AS --=============================================================================================== -- Program name         Creation Date     Original Version   Created by   JIRA -- xx_webapi_dhl_fnc    ***********       1.0                 -- ----------------------------------------------------------------------------------------------- -- Description: -- This function will use a dummy waybill number for 3PL carriers and get the original waybill number. -- This program uses SOA platform to post and get the information from 3PL carrier. -- -- Parameter    : p_waybill_dummy_no      IN     Scan SHIP by user -- -- ----------------------------------------------------------------------------------------------- -- Modification History: -- Modified Dat...
Read more

OAF page to UPLOAD the data from excel sheet to the database table

Image
   UPLOAD BUTTON (logic below): Excel sheet column structure: Table Structure: Code inside Controller: public void processFormRequest(OAPageContext  pageContext,  OAWebBean    webBean) {      super.processFormRequest(pageContext, webBean);      OAApplicationModule  am = (OAApplicationModule)pageContext.getApplicationModule(webBean);      String   filename   = null;      String   fname       = null;      String   ext            = null;     pageContext.writeDiagnostics(this,"Inside PFR..",1);      if(pageContext.getParameter("UploadBtn") != null)   //ID of the Upload button       {                DataObject   upload  =  pageContext.getNamedDataObject("FileUpload");  //import orac...
Read more