Re: duplicate index error due to datetime format

Karl Hewlett wrote:
>
> Hi
>
> 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

sorry - dates do not contain anything below seconds in Oracle. A solution would be a surrogate key (populated by a sequence) to give a unique key with a non-unique index on the date column.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse