Urgent Help required on table join

[Former Member]

Hi,

I have a requirement to bring the email add field in one the reports. This report basically displays contracts and orders.

I have following joins in this report:

VBAK – VBAP (Join on Sales Doc, Join type is inner)

VBAK – KNA1 (Join on KUNNR, Join type is inner)

KNA1 – ADR6 (Join on ADRNR-KNA1 and ADDRNUMBER-ADR6, Join type is Left Outer since not all customers have email add)

In ADR6, ADDRNUMBER is not the only primary key. There exist records which have more then 1 email address. In this case they have same ADDRNUMBER and so report brings in same record twice with diff email add.

So in this case where a customer has more then one email add there is a field default email address (FLGDEFAULT-ADR6) marked for one out of the two addresses.

I put the field default email address in the selection of the report. Now when I execute the report with this field marked it avoids duplicate entries which is used to come in the before but now it also restricts all those customers who don’t have email address.

WHAT I WANT IS THAT ONLY FOR THOSE RECORDS IN WHICH JOIN CONDITION IS SATISFIED BETWEEN ADR6 AND KNAI IT SHOULD CHECK FOR THE DEFAULT EMAIL ADDRESS FLAG. HOW CAN I ACHIEVE THIS LOGIC WITHIN INFOSET?

Thank you,

sam

[Former Member]

Hi,

I have a requirement to bring the email add field in one the reports. This report basically displays contracts and orders.

I have following joins in this report:

VBAK – VBAP (Join on Sales Doc, Join type is inner)

VBAK – KNA1 (Join on KUNNR, Join type is inner)

KNA1 – ADR6 (Join on ADRNR-KNA1 and ADDRNUMBER-ADR6, Join type is Left Outer since not all customers have email add)

In ADR6, ADDRNUMBER is not the only primary key. There exist records which have more then 1 email address. In this case they have same ADDRNUMBER and so report brings in same record twice with diff email add.

So in this case where a customer has more then one email add there is a field default email address (FLGDEFAULT-ADR6) marked for one out of the two addresses.

I put the field default email address in the selection of the report. Now when I execute the report with this field marked it avoids duplicate entries which is used to come in the before but now it also restricts all those customers who don’t have email address.

WHAT I WANT IS THAT ONLY FOR THOSE RECORDS IN WHICH JOIN CONDITION IS SATISFIED BETWEEN ADR6 AND KNAI IT SHOULD CHECK FOR THE DEFAULT EMAIL ADDRESS FLAG. HOW CAN I ACHIEVE THIS LOGIC WITHIN INFOSET?

Thank you,

sam

[Former Member]

Hi..

don't involve that flag in selection..

so...u will get duplicatess....

retreive address number also..

now..with the internal table itab...

sort itab by adrnr.

delete adjacent duplicates from itab comparing adrnr.

this will give u single entries..

[Former Member]

i didnt quite get it. can you please give me step by step procedure.

[Former Member]

Hi..

1. get the data (retreive adrnr also) without involving that flag (FLGDEFAULT).

so..u will have the output internal table with duplicate reccords as a person

having 2 or more email addresses will be repeated.

2. Now sort the itab by adrnr.

so..all the records in itab with similar adrnrs come together.

3. DELETE ADJACENT DUPLICATES FORM itab COMPARING ADRNR.

so..u will eliminate the duplicate entries....which are having similar adrnrs..

ok...

[Former Member]

Ram, thanks for help.

This is an infoset and I don’t know ABAP too. Is the logic which you are saying can be implemented within the InfoSet. I would appreciate if you can guide me in more detail.

[Former Member]

Hi heny..

I am sorry ..i don't know about INFOSETs..

but...in ABAP it works fine...

Ram