Re: duplicate index error due to datetime format

From: <schroeerm_at_my-deja.com>
Date: Wed, 12 Apr 2000 10:15:18 GMT
Message-ID: <8d1ibf$34f$1_at_nnrp1.deja.com>


You cannot use milliseconds within the datetime format. You have to use a composed index, the datetime column and another "id-" column, for example based on a sequence

Greetings
Martin

In article <8d0a81$m5v$1_at_scream.auckland.ac.nz>,   "Karl Hewlett" <fake_address_at_auckland.ac.nz> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 12 2000 - 12:15:18 CEST

Original text of this message