ABAP CDS read line based on other language if language is not maintained

[former_member676157]

Hello All,

I have requirement where I need to read one text based on language using ABAP CDS view. If the specific text is not maintained(lets say in DE) in that language then default read English one.

Is this possible using CASE syntax in ABAP CDS views?

Thanks

[Sathees_P]

Hello,

SAP ABAP CDS view - text field with substituting / supplementation language via left outer join using coalesce function

@AbapCatalog.sqlViewName: 'zvmeals'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Meals'

define view ZMeals 
  with parameters 
    p_langu : abap.lang
    
  as select from smeal
    left outer join ZMeal_Text as _lgText
       on smeal.carrid  = _lgText.AirlineID
      and smeal.mealnumber = _lgText.MealNumber
      and _lgText.Langu = :p_langu
       
    left outer join ZMeal_Text as _slText
       on smeal.carrid  = _slText.AirlineID
      and smeal.mealnumber = _slText.MealNumber
      and _slText.Langu = 'E' 
{
  key smeal.carrid as AirlineID,
  key smeal.mealnumber as MealNumber,
      smeal.mealtype as MealType,
      coalesce( _lgText.Text, _slText.Text ) as Text
}<br>

[Sathees_P]

Hello,

One example to get text with substituting / supplementation language using ABAP SQL with subqueries

  SELECT mt~carrid, mt~mealnumber, mt~sprache, mt~text
    FROM smealt AS mt
   WHERE mt~carrid = 'AA' AND
         mt~sprache IN ( SELECT m~sprache
                           FROM smealt AS m
                          WHERE m~carrid = mt~carrid
                            AND m~mealnumber = mt~mealnumber
                            AND m~sprache = @sy-langu
                          UNION ALL
                         SELECT m~sprache
                           FROM smealt AS m
                          WHERE m~carrid = mt~carrid
                            AND m~mealnumber = mt~mealnumber
                            AND m~sprache = 'D'   " Substitution Language
                            AND m~mealnumber NOT IN ( 
                                SELECT mealnumber 
                                  FROM smealt 
                                 WHERE carrid = mt~carrid 
                                   AND mealnumber = mt~mealnumber 
                                   AND sprache = 'E') )
  ORDER BY mt~carrid, mt~mealnumber<br>

[gasparerdelyi]

You can have it by union brancges with UNION ALL.
The tricky part is the WHERE condition for the default English language branch -- should only evaluate to true when there is no text in the primary language -- can be done by a subquery.