CODEWITHSUNDEEP
sqloracle-databaseview
Adrian Obermühlner
Adrian Obermühlner

Reputation: 49

How to avoid 'unique constraint' error message when creating a view

I have a normal View and iam getting the error message : 

[Error] Execution (6: 83): ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_COL1) violated

But I don't get what I am doing wrong. It says the bacl.Description, batl.Description, bagl.description are violating the uniqueness constraint.

    CREATE OR REPLACE FORCE VIEW CCI.VW_TA04_BAC_PAGE_4_2 as
   SELECT 
          bac.id, 
         bac.code code, 
         bacl.DESCRIPTION, 
         bac.order_key, 
        bac.bat_id,  
        batl.description,  
        bac.bag_id,  
        bagl.description,  
        bac.weight_factor,  
        bac.display
     FROM BART_CATEGORIES bac,
          BART_CATEGORIES_LAE bacl,
          BART_CATEGORY_GROUPS bag,
          BART_CATEGORY_GROUPS_LAE bagl,
          BART_CATEGORY_TYPES bat,
          BART_CATEGORY_TYPES_LAE batl
    WHERE bacl.lae_id = pkg_process.language
    AND batl.lae_id = pkg_process.language
    AND bagl.lae_id = pkg_process.language

    AND (bac.bag_id = bag.id)
    AND (bac.bat_id = bat.id)     

    AND (bacl.BAC_ID = bac.id)
    AND (bagl.BAG_ID = bag.id)
    AND (batl.BAT_ID = bat.id)

Thanks for any advice.

Upvotes: 2

Views: 5867

Answers (1)

APC
APC

Reputation: 146239

" It says the bacl.Description, batl.Description, bagl.description are violating the uniqueness constraint"

Your view has columns with the same name from three different tables. Column name must be unique in the view. So you need to alias those columns. For instance this would do the trick: 

CREATE OR REPLACE FORCE VIEW CCI.VW_TA04_BAC_PAGE_4_2 as
SELECT 
      bac.id, 
     bac.code code, 
     bacl.DESCRIPTION as bac_description, 
     bac.order_key, 
    bac.bat_id,  
    batl.description as bat_description,  
    bac.bag_id,  
    bagl.description  as bag_description,  
    bac.weight_factor,  
    bac.display

" i tought writing "bacl" in front or would be enough."

We need both. The table alias tells the SQL engine which table provides the referenced value but it is not part of the column name.

Upvotes: 1

Related Questions