duplicate records in database view for ANLA and ANLC tables

[Former Member]

HI all,

Can any one please suggest me how to remove duplicate records from ANLA and ANLC tables when creating a database view.

thanks in advance,

ben.

[Former Member]

HI,

ANLA has 4 key fields, while ANLC has 8 key fields. And if in view field all 8 fields of ANLC are not included, output result may look like a duplicate entry. To supress entries a selection condition on remainig key fields of table ANLC will be required.

Regards,

Pranav.

[Former Member]

Hi thanks for your reply ,

Can you please be more specific.

thanks in advance,

ben2012.

[Former Member]

Hi,

Suppose we have two tables one with one field and another with two fields:

TAB1 - Key field KEY1

TAB2 - Key fields KEY1 & Key 2.

No if we create a Database view of these two tables we can do by joining these two tables on Key field KEY1.

Now if in View tab we have inculded TAB1- Key1.

Now lets suppose following four entries are in table TAB1: (AAA), (BBB), (CCC).

and following entries are in table TAB2: (AAA, 1), (AAA, 2), (BBB, 3), (BBB, 5), (DDD, 3).

The data base view will show following entries:

AAA,

AAA,

BBB,

BBB,

Now these entris are duplicate in the output.

This is because TAB2 has multilple entries for same key value of TAB1.

Now if we want to remove multiple entries from ouput - we need to include an entry in selection conditions like TAB2-KEY2 = '1'.

Regards,

Pranav.

[Former Member]

Hi,

Use an inner join select on both table which will result in all entries in table 2 that are present in table 1.

Then just have to use a delete table2 from table itab...No loop needed here.

Thanks & regards.