Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problems creading a Index

RE: Problems creading a Index

From: <Govind.Arumugam_at_alltel.com>
Date: Mon, 29 Sep 2003 13:29:43 -0800
Message-ID: <F001.005D1655.20030929132943@fatcity.com>

 

Run the following sql statement to see whether there are duplicate entries. Chances are that you will find duplicates hence you get the above error. You may choose to remove the duplicates or create a non-unique index otherwise.  

select COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM, count(*) from LAWSON2.OEINVCLINE
group by COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM having count(*) > 1;  

-----Original Message-----
Teresita Castro
Sent: Monday, September 29, 2003 3:35 PM To: Multiple recipients of list ORACLE-L

HI!!
I want to create the next index:  

CREATE UNIQUE INDEX LAWSON2.IOEINVCLINE1 ON
"LAWSON2".OEINVCLINE(COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM)
  TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 )   But I can't because Oracle send me the next error:  

The following error has occurred:  

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found  

I checked on TOAD ( with F4 on the table name) and It give me the next script. I don't have an index with the field ITEM on it, so I don't undestand what I am getting this error.  

DROP TABLE OEINVCLINE CASCADE CONSTRAINTS ;   CREATE TABLE OEINVCLINE (

  COMPANY           NUMBER (4)    NOT NULL, 
  INVC_PREFIX       CHAR (2)      NOT NULL, 
  INVC_NUMBER       NUMBER (8)    NOT NULL, 
  LINE_NBR          NUMBER (6)    NOT NULL, 
  LINE_TYPE         CHAR (1)      NOT NULL, 
  ITEM              CHAR (32)     NOT NULL, 
  DESCRIPTION       CHAR (30)     NOT NULL, 
  ORDER_NBR         NUMBER (8)    NOT NULL, 
  SHIPMENT_NBR      NUMBER (10)   NOT NULL, 
  QUANTITY          NUMBER (13,4) NOT NULL, 
  INVC_CW_QTY       NUMBER (13,4) NOT NULL, 
  SPR_UOM           CHAR (4)      NOT NULL, 
  SELL_UOM          CHAR (4)      NOT NULL, 
  SEC_UOM           CHAR (4)      NOT NULL, 
  MULT_SPR_FL       CHAR (1)      NOT NULL, 
  SPR_TO_STOCK      NUMBER (13,7) NOT NULL, 
  SELL_TO_STOCK     NUMBER (13,7) NOT NULL, 
  SEC_UOM_MULT      NUMBER (13,7) NOT NULL, 
  LOCATION          CHAR (5)      NOT NULL, 
  PRICE_STATUS      CHAR (1)      NOT NULL, 
  ENTERED_PRICE     NUMBER (13,5) NOT NULL, 
  UNIT_PRICE        NUMBER (13,5) NOT NULL, 
  SELL_PRC_CURR     NUMBER (15,7) NOT NULL, 
  SELL_UNIT_PRC     NUMBER (15,7) NOT NULL, 
  UNIT_COST         NUMBER (13,5) NOT NULL, 
  CURRENT_COST      NUMBER (13,5) NOT NULL, 
  NO_CHARGE_FL      CHAR (1)      NOT NULL, 
  ENTERED_DISC      NUMBER (15,2) NOT NULL, 
  ADD_ON_DISC       NUMBER (15,2) NOT NULL, 
  ALLOC_DISC        NUMBER (15,2) NOT NULL, 
  TAX_EXEMPT_CD     CHAR (1)      NOT NULL, 
  TAX_CODE          CHAR (10)     NOT NULL, 
  ENT_TAXABLE       NUMBER (15,2) NOT NULL, 
  TAXABLE_BSE       NUMBER (15,2) NOT NULL, 
  TAX_AMT_CURR      NUMBER (15,2) NOT NULL, 
  TAX_AMT_BSE       NUMBER (15,2) NOT NULL, 
  REASON_CODE       CHAR (4)      NOT NULL, 
  DISC_CODE         CHAR (10)     NOT NULL, 
  ORD_DISC_FL       CHAR (1)      NOT NULL, 
  CONTRACT_NBR      CHAR (14)     NOT NULL, 
  PROMOTION         CHAR (10)     NOT NULL, 
  ACTIVITY          CHAR (15)     NOT NULL, 
  ACCT_CATEGORY     CHAR (5)      NOT NULL, 
  ATN_OBJ_ID        NUMBER (12)   NOT NULL, 
  ACTIVITY_C        CHAR (15)     NOT NULL, 
  ACCT_CATEG_C      CHAR (5)      NOT NULL, 
  ATN_OBJ_ID_C      NUMBER (12)   NOT NULL, 
  FINAL_INVC_FL     CHAR (1)      NOT NULL, 
  SLS_ACCT_UNIT     CHAR (15)     NOT NULL, 
  SLS_ACCOUNT       NUMBER (6)    NOT NULL, 
  SLS_SUB_ACCT      NUMBER (4)    NOT NULL, 
  SALES_MAJCL       CHAR (4)      NOT NULL, 
  SALES_MINCL       CHAR (4)      NOT NULL, 
  DSC_AMT_01        NUMBER (15,2) NOT NULL, 
  DSC_AMT_02        NUMBER (15,2) NOT NULL, 
  DSC_AMT_03        NUMBER (15,2) NOT NULL, 
  DSC_ACCT_UNIT_01  CHAR (15)     NOT NULL, 
  DSC_ACCT_UNIT_02  CHAR (15)     NOT NULL, 
  DSC_ACCT_UNIT_03  CHAR (15)     NOT NULL, 
  DSC_ACCOUNT_01    NUMBER (6)    NOT NULL, 
  DSC_ACCOUNT_02    NUMBER (6)    NOT NULL, 
  DSC_ACCOUNT_03    NUMBER (6)    NOT NULL, 
  DSC_SUB_ACCT_01   NUMBER (4)    NOT NULL, 
  DSC_SUB_ACCT_02   NUMBER (4)    NOT NULL, 
  DSC_SUB_ACCT_03   NUMBER (4)    NOT NULL, 
  DSC_AMT_BASE      NUMBER (15,2) NOT NULL, 
  OFF_ACCT_UNIT     CHAR (15)     NOT NULL, 
  OFF_ACCOUNT       NUMBER (6)    NOT NULL, 
  OFF_SUB_ACCT      NUMBER (4)    NOT NULL, 
  CGS_ACCT_UNIT     CHAR (15)     NOT NULL, 
  CGS_ACCOUNT       NUMBER (6)    NOT NULL, 
  CGS_SUB_ACCT      NUMBER (4)    NOT NULL, 
  LAST_MISC_SEQ     NUMBER (3)    NOT NULL, 
  LAST_COMM_SEQ     NUMBER (3)    NOT NULL, 
  TERRITORY         CHAR (4)      NOT NULL, 
  SALESMAN          NUMBER (4)    NOT NULL, 
  SALESMAN_2        NUMBER (4)    NOT NULL, 
  COMM_RATE_1       NUMBER (7,7)  NOT NULL, 
  COMM_RATE_2       NUMBER (7,7)  NOT NULL, 
  COMM_SPLIT        NUMBER (5,5)  NOT NULL, 
  USER_FLD1         CHAR (2)      NOT NULL, 
  USER_FLD2         CHAR (30)     NOT NULL, 
  USER_FLD3         CHAR (15)     NOT NULL, 
  LINE_GRS_CURR     NUMBER (15,2) NOT NULL, 
  INVL_OBJ_ID       NUMBER (12)   NOT NULL, 
  PROD_TAX_CAT      CHAR (15)     NOT NULL, 
  LINE_GRS_BASE     NUMBER (15,2) NOT NULL, 
  OILSET2_SS_SW     CHAR (1)      NOT NULL, 
  OILSET3_SS_SW     CHAR (1)      NOT NULL, 
  OILSET4_SS_SW     CHAR (1)      NOT NULL, 
  CONSTRAINT OILSET1
  PRIMARY KEY ( COMPANY, INVC_PREFIX, INVC_NUMBER, LINE_NBR )     USING INDEX
     TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5
     STORAGE ( INITIAL 40960 ))

   TABLESPACE LAWSON_PRUEBAS_DATOS
   PCTFREE 5
   PCTUSED 94
   INITRANS 1
   MAXTRANS 255
  STORAGE (
   INITIAL 1024000
   MINEXTENTS 1
   MAXEXTENTS 2147483645
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE;   CREATE INDEX LAWSON2.OILSET2 ON
"LAWSON2".OEINVCLINE(OILSET2_SS_SW, COMPANY, INVC_PREFIX, INVC_NUMBER, SALES_MAJCL, SALES_MINCL, LINE_NBR)
  TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 61440 ) ;  

CREATE INDEX LAWSON2.OILSET3 ON
"LAWSON2".OEINVCLINE(OILSET3_SS_SW, ATN_OBJ_ID)
  TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 ) ;  

CREATE INDEX LAWSON2.OILSET4 ON
"LAWSON2".OEINVCLINE(OILSET4_SS_SW, ATN_OBJ_ID_C)
  TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 ) ;  

CREATE UNIQUE INDEX LAWSON2.OILSET5 ON
"LAWSON2".OEINVCLINE(COMPANY, INVC_PREFIX, INVC_NUMBER, TAX_CODE, PROD_TAX_CAT, LINE_NBR)
  TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 81920 ) ;  

CREATE UNIQUE INDEX LAWSON2.OILSET6 ON
"LAWSON2".OEINVCLINE(COMPANY, INVC_PREFIX, LINE_NBR, INVC_NUMBER)
  TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 ) ;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <Govind.Arumugam_at_alltel.com
  INET: Govind.Arumugam_at_alltel.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Sep 29 2003 - 16:29:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US