From jonw@fuse.net Fri, 03 Aug 2001 11:38:30 -0700 From: "Jon Walthour" Date: Fri, 03 Aug 2001 11:38:30 -0700 Subject: Re: Your ideas will be helpful Message-ID: MIME-Version: 1.0 Content-Type: text/plain Helen:   How about a select statement that will do it for you instead of all the looping? Does this work?   select   asset_num       , decode(min(num), 1, 'DHI', 2, 'AHI', 3, 'CHI', '???') as company_id    from (select asset_num               , decode(company_id, 'DHI', 1, 'AHI',2, 'CHI', 3, 4) as num            from assets)group by asset_num;   Hope this helps.   Jon Walthour
----- Original Message -----
From: Helen rwulfjeq To: Multiple recipients of list ORACLE-L Sent: Wednesday, August 01, 2001 5:12 PM Subject: Your ideas will be helpful  Hello, all: I'm import data from schema1.table1 (ASSET_NUM,COMPANY_ID,CONSOLI_ID……) to schema2.table2 (ASSET_NUM,COMPANY_ID,CONSOLI_ID……)using SQL script on Oracle 8i. The situation is like this: In schema1.table1, it does not has a Primary Key and Table1.ASSET_NUM has duplicated records while values in COMPANY_ID are different. For example: ASSET_NUM    COMPANY_ID ----------   ---------- AA237334       AHI AA237334       DHI On the other hand, in schema2.table2, the corresponding column Table2.ASSET_NUM is defined as a NOT NULL, Primary Key. These tables' definition can't be modified. So, I need to drop the duplicated ASSET_NUM/records from schema1.table1 and then import into schema2.table2. The rule of drop will depend on the priorities of COMPANY_ID (ranking as < Dxx, Axx, Cxx >, from < Keep to Drop >. which means between Axx & Dxx, "Dxx" will be imported. For instances, like the above example, record contains 'AHI' will be dropped. This rule is only depended on the first Character -- substr(company_id, 1,1). ) I thought about: 1/ loop a cursor on whole table1? 2/ just duplicate records and then the rest records will be "insert into table2" directly. Then how do I decide which record to drop? Use a second cursor? 3/ screen all the duplicate record into a temp table and create a PK on that table and then decide… (this sounds very redundant) Any ideas how can I do this? Thanks in advance Helen Do You Yahoo!?Make international calls for as low as $.04/minute with Yahoo! Messengerhttp://phonecard.yahoo.com/