SQL | ORACLE | How-To | Snippets

SQL | ORACLE 10g+ |

To get the next N months (text) from a start Date.Here the Next 12 months given a start_date of 08/2013


  • select add_months( start_date, level-1 ) as monthName from (select to_date(’08/2013′,’mm-yyyy’) start_date,  ADD_MONTHS(to_date(’08/2013′, ‘mm-yyyy’), 12) end_date from dual)  connect by level <= months_between(trunc(end_date,’MM’), trunc(start_date,’MM’) )   * + 1;

To detect which tables are referencing a table via FKs


select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = ‘R’
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in (‘P’, ‘U’)
and table_name = :r_table_name
and owner = :r_owner
)
order by table_name, constraint_name;

To detect and remove duplicates via self-join

SELF JOIN TO DETECT AND REMOVE DUPLICATES

select
FIELD_A,
FIELD_B,
FIELD_C
from
MY_TABLE  a
where
rowid >
(select min(rowid) from MY_TABLE b
where
b.FIELD_A = a.FIELD_A
and
b.FIELD_B = a.FIELD_B
and
b.FIELD_C = a.FIELD_C
);

Advertisements

#how-to, #oracle, #snippets, #sql