05-17-2016 6:40 AM
Hello All,
I have a requirement where I need to get short text based on the logon language.
If the text is not maintained in logon language, then I need fetch it from English language.
Example
Consider a join between MARA and MAKT.
In Table MAKT
MATNR | SPRAS | MAKTX |
---|---|---|
1 | DE | Test1-DE |
1 | EN | Test1-EN |
2 | EN | Test2-EN |
Consider the logon language in DE,
For material 1 there is text in language DE, so the output should to be Test1-DE
For material 2 there is no text in language DE, so the output should be from EN language Test2-EN
I tried to use left outer join , but that gives few records with empty text or text in both languages, which we can later delete them.
And there are lot of other ways by which we can refine the internal table after the select is performed.
What I am looking for, is there any way we can do it in select query itself, considering new advancements in ABAP open SQL and CDS views.
With regards,
Sandeep Akella
05-17-2016 9:11 AM
Hi Sandeep Akella,
this is a typical use case for the function COALESCE.
You do two left out outer joins (one per language) and combine the two results with COALESCE
e.g.
select from MARA
left outer join MAKT as MAKT_EN on MARA.MATNR = MAKT_EN.MATNR and MAKT_EN.SPRAS = 'E'
left outer join MAKT as MAKT_DE on MARA.MATNR = MAKT_EN.MATNR and MAKT_EN.SPRAS = 'D'
{
key MATNR,
coalesce( MAKT_DE.MAKTX, MAKT_EN.MAKTX ) as Text
}
The function is available both in CDS and OpenSQL.
In addition you can use a parameter instead of a literal for your logon language.
Best Regards, Thomas
05-17-2016 9:11 AM
Hi Sandeep Akella,
this is a typical use case for the function COALESCE.
You do two left out outer joins (one per language) and combine the two results with COALESCE
e.g.
select from MARA
left outer join MAKT as MAKT_EN on MARA.MATNR = MAKT_EN.MATNR and MAKT_EN.SPRAS = 'E'
left outer join MAKT as MAKT_DE on MARA.MATNR = MAKT_EN.MATNR and MAKT_EN.SPRAS = 'D'
{
key MATNR,
coalesce( MAKT_DE.MAKTX, MAKT_EN.MAKTX ) as Text
}
The function is available both in CDS and OpenSQL.
In addition you can use a parameter instead of a literal for your logon language.
Best Regards, Thomas
05-17-2016 10:45 AM