Re: Problems creading a Index

Teresita,

The problem has nothing to do with existing indexes, but rather that the combination of the columns is not unique. I don't know Lawson, so I'm taking a guess, but I think the issue may be that the same item could appear more than once on an invoice. If you add invoice line do you have the same error.

Daniel Fink
Teresita Castro wrote:

> 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: Daniel Fink
  INET: Daniel.Fink_at_Sun.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).



text/x-vcard attachment: Card for Daniel Fink