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

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.

Nani

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.

(employee_number CHAR(5),
employee_atten_time TIMESTAMP,
machine_number CHAR(3),
in_out CHAR(2)
)
ORGANIZATION EXTERNAL
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)