Use second condition instead if rows are not found

Question

I have the following select:

SELECT name, text, lang FROM texts
  WHERE name IN @r_names
    AND lang IN ( @lv_lang, 'E' )
  INTO TABLE @DATA(lt_texts).

It will select texts multiple lines of texts for a given name.

How do I say that I want texts with lang = lv_lang, but if they don't exist, then select ones with lang = 'E' all within one request to the DB and no processing on the application side?

I feel it is easier to do it by deleting adjacent duplicates than modifying the query but I will wait for others to respond if they have any solution for this. — Pavan Kumar Polavarapu, Sep 30 '19 at 18:22

score 4 · Answer 1 · edited Oct 01 '19 at 14:49

4

You can use UNION operator for this task:

SELECT name, text, lang FROM texts
 WHERE name IN @r_names
   AND lang = @lv_lang
UNION
SELECT name, text, lang FROM texts
 WHERE name IN @r_names
   AND lang = 'E'
 AND NOT EXISTS ( SELECT name
                  FROM texts
                  WHERE name IN @r_names
                  AND lang = @lv_lang
                )
INTO TABLE @DATA(lt_texts).

edited Oct 01 '19 at 14:49

kdobrev

270
1
3
11

answered Sep 30 '19 at 23:34

Suncatcher

10,355
10
52
90

1

[`UNION`](https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abapunion.htm) since [7.50](https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abennews-750-open_sql.htm) and [`EXISTS`](https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abenwhere_logexp_exists.htm) (since ever) – Sandra Rossi Oct 01 '19 at 08:13
The other two answers run significantly faster – András Oct 12 '19 at 09:40

Pilot · Answer 2 · 2019-10-02T11:11:16.983

I like coalesce for this sort of thing (it will fill-in your target with the first non-null value). You can have sy-langu as default and more languages in order of precedence:

SELECT SINGLE coalesce( default~eqktx, greek~eqktx, english~eqktx )
  FROM equi AS e LEFT OUTER JOIN eqkt AS default
                 ON  default~equnr = e~equnr
                 AND default~spras = @sy-langu
                 LEFT OUTER JOIN eqkt AS greek
                 ON  greek~equnr = e~equnr
                 AND greek~spras = 'G'
                 LEFT OUTER JOIN eqkt AS english
                 ON  english~equnr = e~equnr
                 AND english~spras = 'E'
  WHERE e~equnr = @ls_equi-equnr
   INTO @DATA(lv_eqktx).

Your example would become:

SELECT coalesce( default~name, english~name ),
       coalesce( default~text, english~text ),
       coalesce( default~lang, english~lang )
  FROM texts AS default LEFT OUTER JOIN texts AS english
                        ON  english~name = default~name
                        AND english~lang = 'E'
  WHERE default~name IN @r_names
    AND default~lang = @lv_lang
  INTO TABLE @DATA(lt_texts).

score 1 · Answer 3 · answered Oct 04 '19 at 09:23

Based on Suncatcher answer but without the UNION. Didn't tried, so I don't know if it works as expected:

SELECT name, text, lang FROM texts
 WHERE name IN @r_names
 AND ( lang = @lv_lang
       OR ( NOT EXISTS ( SELECT name
                         FROM texts
                         WHERE name IN @r_names
                         AND lang = @lv_lang
                       )
              AND lang = 'E'
          )        
       )
 INTO TABLE @DATA(lt_texts).

Use second condition instead if rows are not found

3 Answers3