duplicate index error due to datetime format

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

[Quoted] On a number of tables I am getting a "ORA-01452: cannot CREATE UNIQUE INDEX; [Quoted] duplicate keys found" which appears to be because of datetime data that [Quoted] Informix will index because it goes to milli-second detail but Oracle is [Quoted] 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:50:53 CEST