SQL :> Select Decode
(Gender,Male,Female,Female,Male)
From Table_Name...
Saturday, September 13, 2014
Labels:Triggers | 0
comments
external tables are mainly used when we have the data in flat files and we need to import that data into database then we go for external tables.
eg xml files, csv files, etc.
Using external tables as server face you can define table using position and access data same as structured table, using dml. Perhaps good use is to load external data without use loader with temporal...
Sub-query - The inner query execute first, the outer query deprnds on inner query output.
Correlated sub-query - The outer query execute first, the inner query deprnds on outer query result.
A correlated sub-query is where the sub-query part makes reference to values from the outer query.
------------------------------------------------------------------------------------------------------------------------------
SELECT...
Use rowid pseudo column. All you have to do is to keep the latest data (i.e. highest ROWID) and remove other duplicated rows.SELECT * FROM table1 a WHERE rowid < (SELECT max(rowid) FROM table1 bWHERE a.column1 = b.column1 AND etc...);
OR
create table testtt (num number);insert into testtt values(111);insert into testtt values(111);insert into testtt values(111);insert into testtt values(111);insert into testtt values(222);insert...
update table_name set (col1,col2,col3)=(select t.col4,t.col5,t.col6 from table_name2 t where something)
OR
create table tgt ( id number /*primary key*/, x number, y number )/insert into tgt ( id, x, y )select level, level, level from dual connect by level < 4/create table src ( id number /*primary key*/, x number, y number )/insert into src ( id, x, y )select level+1, level*5, level*5 from dual connect by level <...
we can get the last updated date based on the following inbuild function:
scn_to_timestamp(max(ora_rowscn))
Example:
create table Test_last_upd
(a number,
b varchar2(3)
);
begin
insert into Test_last_upd values (1,'AAA');
insert into Test_last_upd values (2,'BBB');
insert into Test_last_upd values (3,'CCC');
end;
update Test_last_upd
set b = 'ABC'
where a =3;
select TLU.*, scn_to_timestamp(max(ora_rowscn))
from...
Subscribe to:
Posts
(Atom)