Update record

update att_emp_attendance set aea_sts = 5 where aea_sts = 2 and emp_id in (
select EMP_ID from emp_empl_terms1 where eet_work_cs_id = 74 and eet_sts = 2
and (EET_EFF_TO is null or EET_EFF_TO > sysdate)
)

Setting in SQL Console

set linesize 200
set pagesize 3000

Select record by time format

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss' <-- set the second shown by define format.

select * from epis_file where site_code = 'YT' and trunc(import_date) = to_date('2008/05/09');

trunc <--- limit the time not select.


or


select * from epis_file where site_code = 'YT' and trunc(import_date) = to_date('2008/05/09','yyyy-mm-dd');

Link to other DB format

link to hr db from jr
select * from inbox_msg@hrlink;

Invert value to Table

insert into misc_type_map select mtm_id_seq.nextval,4,'emp_prs_info','emp_mar_st',1,'0',to_date('01-mar-08'),'',sysdate,'0',sysdate from dual;
commit;

Searching by some like case

select * from tab where tname like 'EMP%'; <--- must big case !!!

Delete Route

Delete the router record ( Sysm_id = 9 , 9 = 01/2008 , del after current month is better )

Find out the rsrf_id for TPMM's record ( TPMM Site_id = 85 )
select * from ros_site_roster_file where site_id = 85;

delete ros_site_roster_details where rsrf_id in (7461,7462,7463,7464);

delete ros_site_roster_file where rsrf_id = 7464;

commit;

Create New Table

CREATE TABLE misc_type_map (
mtm_id NUMBER(5) NOT NULL,
mt_id NUMBER(10) NOT NULL,
mtm_table_nam VARCHAR2(50) NOT NULL,
mtm_field_nam VARCHAR2(50) NOT NULL,
mtm_sts NUMBER(1) NOT NULL,
created_by NUMBER(10) NOT NULL,
creation_date DATE NOT NULL,
last_modified_by NUMBER(10) NOT NULL,
last_modified_date DATE NOT NULL,
CONSTRAINT pk_mtm_id_1 PRIMARY KEY(mtm_id)
);
CREATE SEQUENCE mtm_id_seq INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;