duplicate index error due to datetime format

From: Karl Hewlett <fake_address_at_auckland.ac.nz>
Date: 2000/04/12
Message-ID: <8d0ami$ma3$1@scream.auckland.ac.nz>#1/1

I am involved in a project to move Peoplesoft data from Informix to Oracle.

On a number of tables I am getting a "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found" which appears to be because of datetime data that Informix will index because it goes to milli-second detail but Oracle is only going to seconds.

How do I

1/ supply a format with the to_char to see the milli-seconds

2/ create the index

_Note the last two lines of the SQL return below_

Oracle:

1 select to_char(DT_TIMESTAMP, 'yyyy-dd-mm hh:mi:ss') 2 from ps_cost_inv
3* where INV_ITEM_ID = '000000000000012376' SQL> / TO_CHAR(DT_TIMESTAM

1998-13-12 12:39:42
1999-16-07 12:35:00
1999-16-07 12:35:00

Informix:

SELECT DT_TIMESTAMP
FROM ps_cost_inv
WHERE INV_ITEM_ID = '000000000000012376'; dt_timestamp

1998-12-13 00:39:42.000
1999-07-16 12:35:00.260
1999-07-16 12:35:00.840

CREATE UNIQUE INDEX PS_COST_INV ON PS_COST_INV (BUSINESS_UNIT,    INV_ITEM_ID,
   DT_TIMESTAMP,
   SEQ_NBR,
   COST_ELEMENT,
   TRANS_LINE_TYPE,
   AP_SEQ_NBR) TABLESPACE PSINDEX STORAGE (INITIAL 163840 NEXT 163840 MAXEXTENTS 110 PCTINCREASE 0) Thanks in advance for your help
Karl Hewlett Received on Wed Apr 12 2000 - 00:00:00 CDT