Re: Your ideas will be helpful

From: Jon Walthour <jonw_at_fuse.net>
Date: Fri, 03 Aug 2001 11:38:30 -0700
Message-ID: <F001.0035EBB6.20010802203233@fatcity.com>

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
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Helen rwulfjeq

To: <A title=ORACLE-L_at_fatcity.com
href="mailto:ORACLE-L_at_fatcity.com">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 (<FONT face=Courier   size=1>ASSET_NUM,COMPANY_ID,CONSOLI_ID……) to   schema2.table2 (<FONT face=Courier
  size=1>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! Messenger<A
href="http://phonecard.yahoo.com/?.refer=mailtagline">http://phonecard.yahoo.com/ Received on Fri Aug 03 2001 - 13:38:30 CDT