redundant vales due to joins.

[former_member603305]

Hi All,

we have used 3 different tables for Material interface joining with left outer joins and material ID is used as common field.

Now, Issue is few materials have different sales organizations and descriptions so in case if material has two sales org and description for each sales org then interface should transfer 2 records however it is transferring 4 records because descriptions are sent twice for each sales org.

example :

Material ID 767

sales org 0001 and 0002

description are AA with language key 1 and BB with language key 2.

language key

now, output is like

0001 AA

0001 BB

0002 AA

0002 BB

we need output like below using language key

0001 AA

0002 BB

can you please help here? I

[akhileshkiran]

Hi ashanbhog,

In the query level itself you can apply the ifthenelse() condition based on the respective sales org with language key and use distinct on the query.

Language Key Mapping :

ifthenelse(Query.Saleorg = 0001,'AA',ifthenelse(Query.Saleorg = 0002,'BB',nul))

Regards,

Akhilesh Kiran

[werner_daehn]

I fail to grasp the goal.

You have a material with two sales orgs (MVKE) and a material has two descriptions (MAKT). Why would you want e.g. the german text in sales org 0001 and the english text in org 0002?? What is the rule here?

• Primary key of MARA: MATNR
• Primary key of MVKE: MATNR, VKORG (Sales Org) and VTWEG (sales channel)
• Primary key of MAKT: MATNR, SPRAS (language)

Hence, without further constraints in the joins, the resulting dataset has the PK

• MATNR, VKORG, VTWEG, SPRAS

[akhileshkiran]

Hi ashanbhog,

In the query level itself you can apply the ifthenelse() condition based on the respective sales org with language key and use distinct on the query.

Language Key Mapping :

ifthenelse(Query.Saleorg =0001,'AA',ifthenelse(Query.Saleorg =0002,'BB',null))

Regards,

Akhilesh Kiran

[mateuszadamus]

Hi,

Are you retrieving material number and material description only?
In this case use DISTINCT keyword in the SELECT statement.

regards,

Mateusz

Edit: if this does not help, then please provide more information about the SQL query you use.

[former_member603305]

Hi,

thanks for the answer.

I'm extracting 18 fields from 3 different tables but issue highlighted is because of the sales org and description.

I did use DISTINCT keyword but still it is sending 4 records instead of 2 records as it is sending 2 records for 2 sales org each

[mateuszadamus]

Can you provide the current SQL statement?