Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Friday, 1 April 2016

SQL-Trunc

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

      Syntax: trunc (value, precision)      

 Ex:
 SQL> select trunc(123.2345), trunc(123.2345,2), trunc(123.2354,2) from dual;

TRUNC(123.2345) TRUNC(123.2345,2) TRUNC(123.2354,2)
---------------------  -----------------------  -----------------------
            123                        123.23                     123.23

SQL> select trunc(123.2345,-1), trunc(123.2345,-2), trunc(123.2345,-3),trunc(123.2345,-4) from dual;

TRUNC(123.2345,-1) TRUNC(123.2345,-2) TRUNC(123.2345,-3) TRUNC(123.2345,-4)
------------------------  ------------------------   -----------------------  ------------------------
               120                              100                                0                             0

SQL> select trunc(123,0), trunc(123,1), trunc(123,2) from dual;

TRUNC(123,0) TRUNC(123,1) TRUNC(123,2)
----------------   ----------------  -----------------
         123                    123                 123

SQL> select trunc(-123,0), trunc(-123,1), trunc(-123,2) from dual;

TRUNC(-123,0) TRUNC(-123,1) TRUNC(-123,2)
-----------------   -----------------  -----------------
         -123                    -123                -123

SQL> select trunc(123,-1), trunc(123,-2), trunc(123,-3), trunc(-123,-1), trunc(-123,2), trunc(-123,-3) from dual;

TRUNC(123,-1) TRUNC(123,-2) TRUNC(123,-3) TRUNC(-123,-1) TRUNC(-123,2) TRUNC(-
123,-3)
------------- ------------- ------------- -------------- ------------- --------------
          120           100             0                   -120          -123              0

SQL> select trunc(null,null), trunc(0,0), trunc(1,1), trunc(-1,-1), trunc(-2,-2) from dual;

TRUNC(NULL,NULL) TRUNC(0,0) TRUNC(1,1) TRUNC(-1,-1) TRUNC(-2,-2)
-----------------------  -------------  -------------  ---------------  ----------------
                    0                   1                     0                      0


You May Like                                             
                            SQL Abs
                            SQL Sign
                            SQL Sqrt
                            SQL Mod
                            SQL NVL
                            SQL NVL2
                            SQL Power
                            SQL Exp
                            SQL Ln
                            SQL Log
                            SQL CEIL
                            SQL Floor
                            SQL Round                          
                            SQL Bitand
                            SQL Greatest
                            SQL Least
                            SQL Coalesce
Share this article :

2 comments:

  1. Hello There,


    Best thing I have read in a while on this
    Oracle Applications. There should be a standing ovation button. This is a great piece.

    I am new to Oracle, I'm not a DBA and I ve tring to figure out this error in a log:
    ERROR in QUETOL.tol_lc_movimientos_pg.movimientos SQLCODE[=ORA-20109:
    ORA-20109: ORA-01422: exact fetch returns more than request]

    The problem arises I don't even know exactly this error which object references to: ie package, procedure,table,etc

    no rows selected
    I thought it was a Package so I could see its DML code.
    I appreciate your help in advanced to identify which Object this log error point to point.

    Anyways great write up, your efforts are much appreciated.


    Obrigado,
    Nani

    ReplyDelete
  2. Hi There,

    Thanks for highlighting this and indicating about SQL-Trunc where more study and thought is necessary.

    I have some statements that I need to schedule in a procedure.

    Drop Table u_system.emp_load;
    CREATE TABLE u_system.emp_load
    (employee_number CHAR(5),
    employee_atten_time TIMESTAMP,
    machine_number CHAR(3),
    in_out CHAR(2)
    )
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY user_dir
    ACCESS PARAMETERS
    (RECORDS DELIMITED BY NEWLINE
    FIELDS (employee_number CHAR(4),
    employee_atten_time CHAR(20) date_format TIMESTAMP mask "dd/mm/yyyy hh24:mi:ss",
    machine_number CHAR(4),
    in_out CHAR(1)
    )
    )
    LOCATION ('emp2.dat')
    );
    insert into u_system.emp_attend(employee_number,employee_atten_time,machine_number,in_out)
    select * from u_system.emp_load;

    Follow my new blog if you interested in just tag along me in any social media platforms!

    Thanks,
    Preethi

    ReplyDelete