Remove duplicated values programmatically
By Jie Chen-Oracle on Oct 03, 2013
We may always see multiple duplicated values for one attribute on Agile WebClient, which happens sometimes, very often. It is OK to correct them from UI if less objects have such issue. But it is boring if too many exist there. We will have a very simple way to quickly identify the column in database table and fix them effectively by a PL/SQL Procedure.
Suppose we have one Part object TESTPART001 which has multiple values for "CM Access" attribute like below.
We need to figure out this attribute's table and column name in database. Since this "CM Access" attribute is from Page Two page, so we go to Part's subclass Page Two tab in JavaClient. We get "PAGE_TWO.MULTILIST02". That is to say, the attribute value is saved in Page_two table, multilist02 column.
To confirm this, we verify it against ITEM table, and it is.
Then we can query page_two table with the ID to get multilist02 column's value. You will find multiple duplicated values reside.
Then I coded a SQL procedure to fix it programmatically. You can revise it to put into your own scenario.
To remove the duplicated value:
update page_two set multilist02 = remove_dup_vals(multilist02) where id=967250145; commit;