Select with a preffered where condition, but if missing use another I have the following select in SAP HANA DB:

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


It will select multiple lines for a given name if translations exist for different languages.

How do I say that I want texts with language = 'S', but if it doesn't exist, then select ones with lang = 'E'. I want it in a single request to the DB and no processing on application level.

Thank you in advance
One way of accomplishing this might be a self-join (assuming (name, lang) uniquely identifies rows):

SELECT e.name, coalesce(s.text, e.text) AS text, coalesce(s.lang, e.lang) AS lang
FROM (
  SELECT name, text, lang FROM texts
  WHERE name IN ('name1', 'name2', 'name2')
    AND lang = 'E') AS e
LEFT JOIN (
  SELECT name, text, lang FROM texts
  WHERE name IN ('name1', 'name2', 'name2')
    AND lang = 'S') AS s
ON e.name = s.name