Skip to Content
1

Looking for help with new Open SQL Features

Jul 13, 2017 at 04:17 PM

83

avatar image

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).

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Sathya Gunasekaran Jul 14, 2017 at 09:26 AM
0

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

Something like

Sathya


v6dkm.png (8.8 kB)
xu7bj.png (9.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
Fabian Lupa Jul 13, 2017 at 07:41 PM
2

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).
Show 3 Share
10 |10000 characters needed characters left characters exceeded

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.

0

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.

0

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

1
Horst Keller
Jul 13, 2017 at 08:07 PM
0

More elegant? Hello, in SQL? ;-)

Share
10 |10000 characters needed characters left characters exceeded