topic Re: Looking for help with new Open SQL Features in Application Development and Automation Discussions

Looking for help with new Open SQL Features

ron_mitton — Thu, 13 Jul 2017 16:17:41 GMT

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

Re: Looking for help with new Open SQL Features

fabianlupa — Thu, 13 Jul 2017 19:41:51 GMT

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

Re: Looking for help with new Open SQL Features

retired_member — Thu, 13 Jul 2017 20:07:15 GMT

More elegant? Hello, in SQL? 😉

Re: Looking for help with new Open SQL Features

retired_member — Thu, 13 Jul 2017 20:10:00 GMT

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.

Re: Looking for help with new Open SQL Features

fabianlupa — Fri, 14 Jul 2017 07:33:44 GMT

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.

Re: Looking for help with new Open SQL Features

retired_member — Fri, 14 Jul 2017 07:44:43 GMT

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

Re: Looking for help with new Open SQL Features

Sathya_Gunasekaran — Fri, 14 Jul 2017 09:26:03 GMT

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

Something like

Sathya

Re: Looking for help with new Open SQL Features

jrgen_bauer — Fri, 08 Apr 2022 14:08:02 GMT

I've the same question.

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