How to delete duplicate lines from Table

-
delete from my_table t1
 where  exists (select 'x' from my_table t2
                 where t2.key_value1 = t1.key_value1
                   and t2.key_value2 = t1.key_value2
                   and t2.rowid      > t1.rowid

Comments

Post a Comment

Popular posts from this blog

Oracle Shipping Network SQL Query

-
Useful Script for Oracle Shipping Network , I wrote custom function for GL Code , you may find below SELECT a.from_organization_code, a.from_organization_name,        a.to_organization_code, a.to_organization_name,  DECODE (a.intransit_type,                  2, 'Intransit',                  1, 'Direct'                 ) "Transfer Type",        (SELECT meaning           FROM apps.mfg_lookups          WHERE lookup_type = 'MTL_FOB_POINT'            AND lookup_code = a.fob_point) fob_point,        a.elemental_visibility_enabled, DECODE (a.routing_header_id,                  1, 'Standard',                  2, 'Insp...
Click for more

Oracle Ar Invoice Numbering Sequence alter to avoid missing in between by cache

-
1) Query up your invoice source , which user have reported Jump in number. Navigate>Setup>Invoice>Source 2) Execute the following script using your source as in step 1.   SELECT batch_source_id, org_id, name FROM ra_batch_sources_all WHERE name LIKE '<source>' ;   or use this query if you have access from database.   SELECT 'alter sequence AR.' ||seq.sequence_name|| ' nocache;' FROM ( SELECT name bsname, org_id org, 'RA_TRX_NUMBER_' ||BATCH_SOURCE_ID|| '_' ||org_id|| '_S' seqname FROM RA_batch_sources_all ) src, dba_sequences seq WHERE src.seqname=seq.sequence_name AND seq.cache_size; ORDER BY org /     3) Using the batch_source_id from the above query , execute the followingscript:   SELECT sequence_owner, sequence_name, cache_size FROM all_sequences WHERE sequence_name LIKE 'RA_TRX_NUMBER%1146%' ;     4) Execute the following:   ALTER sequence SEQ...
Click for more

Advance Supply Chaining Scripts - ASCP Full Pegging Concept Script

-
ASCP Pegging Script SELECT DISTINCT msc.organization_code m_organization_code,                       msc.category_name m_category_name,                       item_segments m_item_segments,                       msc.description m_description,                       order_type_text m_order_type_text,                       TO_CHAR (new_due_date) m_new_due_date,                       TO_CHAR (quantity_rate) m_quantity_rate,                                             --                TO_CHAR (msc.disposition_...
Click for more