topic SQL-Join with priority select in Application Development and Automation Discussions

SQL-Join with priority select

jrgen_bauer — Fri, 08 Apr 2022 13:55:08 GMT

Dear All
Is there a possibilty to select data with SQL-Joins in a priority way?
For example there are some material description stored in a MAKT and I want to select one description.
At first the description in 'DE' if available, or  description in 'EN' if available, else any other (only 1)

Example (given data)
MAKT-MATNR  MAKT-SPRAS  MAKT-MAKTX
2001        DE          Beschreibung für 2001
2001        EN          Description for 2001
2001        FR          Descriptif pour 2001
2002        EN          Description for 2002
2002        FR          Descriptif pour 2003
2003        BG          Описание за 2004г
2003        FR          Descriptif pour 2004
2003        IT          Descrizione per il 2001

SELECT FROM marc  
INNER JOIN makt
  ON marc~matnr = makt~matnr
  AND spras = ?
  FIELDS
    marc~matnr,
    marc~werks,
    marc~dispo,
    makt~spras,
    makt~maktx
  WHERE marc~matnr BETWEEN `000000000000002001` AND `000000000000002003`
    AND marc~werks = '0002'<br>  INTO TABLE @DATA(lt_mat).


Desired results:

2001  0002  D01  DE  Beschreibung für 2001
2002  0002  D01  EN  Description for 2002
2003  0002  D01  BG  Описание за 2004г

Any idea to solve it?

Re: SQL-Join with priority select

Jrg_Brandeis — Fri, 08 Apr 2022 14:48:47 GMT

Hi Jürgen,

you can do multiple LEFT OUTER JOINs, each for one language (SPRAS). The priorization will be implemented in the SELECT-List with COALESCE. The code should look similar to this:

SELECT FROM marc  
LEFT OUTER JOIN makt AS text_DE
  ON marc~matnr = makt~matnr
  AND spras = 'D'
LEFT OUTER JOIN makt AS text_EN
  ON marc~matnr = makt~matnr
  AND spras = 'E'
LEFT OUTER JOIN makt AS text_BG
  ON marc~matnr = makt~matnr
  AND spras = 'BG' "???
  FIELDS
    marc~matnr,
    marc~werks,
    marc~dispo,
    coalesce(text_DE.spras,
             text_en.spras, 
             text_bg.spras) as spras
     coalesce(text_DE.maktx,          
              text_en.maktx,                     
              text_bg.maktx) as maktx
  WHERE marc~matnr BETWEEN `000000000000002001` AND `000000000000002003`
    AND marc~werks = '0002'

Regards,
Jörg

Re: SQL-Join with priority select

jrgen_bauer — Mon, 11 Apr 2022 10:00:51 GMT

Hello Jörg

Thank you for sharing your ideas!

Unfortunately I don't know in which language the text is available. That would mean that I would have to check about 20 languages!

A select single or something similar would be helpful!

Best Regards
Jürgen