Looking for help with new Open SQL Features

[ron_mitton]

I want to return the material description in the user's logon language. If it doesn't exist return the English version. The following works, it seems with the new features available (we are on 7.5) there should be a more elegant solution. Any ideas ?

SELECT mara~matnr,
CASE WHEN lng~maktx IS NOT NULL THEN lng~maktx
ELSE eng~maktx
END AS desc
FROM mara
LEFT OUTER JOIN makt AS eng
ON eng~matnr = mara~matnr
AND eng~spras = 'E'
LEFT OUTER JOIN makt AS lng
ON lng~matnr = mara~matnr
AND lng~spras = @sy-langu
WHERE mara~matnr IN @s_matnr
INTO TABLE @DATA(m).

[fabianlupa]

Not sure if there's something on 7.50. On 7.51 CTEs might be useful for this:

https://blogs.sap.com/2016/10/18/abap-news-release-7.51-common-table-expressions-cte-open-sql/

I don't have access to a 7.51 system but I assume something along those lines should work?

WITH
  +texts AS (
    SELECT ktext, CASE WHEN langu = 'E' THEN 2 ELSE 1 END AS order
      FROM tcurt
      UP TO 1 ROWS
      WHERE spras IN ('E', @sy-langu )
      ORDER BY order ASCENDING
  )
  SELECT tcurc~waers, t~ktext
    FROM tcurc
    LEFT OUTER JOIN +texts AS t ON tcurc~waers = t~waers
    INTO TABLE @DATA(gt_waers).

[horst_keller]

Yep, but is it more elegant if you can express it in joins anyway? Maybe a bit more readable. I guess the value of CTEs comes in, when you do things that are not possible with joins alone, e.g. unions in the subqueries.

[fabianlupa]

Yes, a better use case would probably be if you had more than 2 languages to search for and a defined priority.

Btw: Is there a generic function module for getting localized texts from text tables with a defined priority regarding languages? I'd assume there is because dynpros need to get their localized labels somehow but I didn't find it yet.

[horst_keller]

I don't know of any. I assume that for dynpros and ABAP it is implemented in the kernel.

[horst_keller]

More elegant? Hello, in SQL? 😉

[sathyags]

The only thing that I can think of is replacing case/when with coalesce.

Something like

Sathya

[jrgen_bauer]

I've the same question.

And also, in step 2, I want to read some description, and exactly one.