| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problems creading a Index
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
TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5
STORAGE ( INITIAL 40960 ))
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
 |
 |