ABAP SELECT case and Common Table Expressions

I need to construct a statement that fills column value depending on existance in another table. I am guessing combination of CTE (7.51 abapwith ) and select case would do the job, but I cannot write it and ask for help.

ABAP is 7.52

The logic is the following: I have a Z table with 2 columns - MAKTL and FLAG (char1), also tables LIPS and MARA. I need to produce a table that has the matnr, lfimg, and a field which is either X or blank. Logic for X is: IF MARA-MHDHB is not initial or MARA-MATKL is found in z_tab1-MATKL and z_tab1-FLAG = X Otherwise leave empty.

Here is what I have written, which does not work, but I hope is clear what I want to do:

WITH
  +tab1 AS (
    SELECT matkl, flag  
      FROM z_tab1
    )
SELECT  lips~matnr,
        lips~lfimg,
*        mara~matnr,
        mara~matkl,
        CASE
          WHEN  ( mara~mhdhb is not initial ) or ( mara~matkl IN +tab1-maktl and +tab1-flag = 'X' ) THEN 'X'
          ELSE '-'
        END AS result_field
  INTO TABLE @DATA(i_tab)
  FROM lips
  JOIN mara ON lips~matnr = mara~matnr.

Solution

First of all, your question is not related to CTE, it's a question about how to achieve one result, CTE may or may not be needed.

In standard SAP R/3, ERP or S/4HANA, there is the table MARA but no table Z_TAB1 so I will use the tables EBAN or T023 instead and an alternative column instead of FLAG. Also, the example can be simplified by removing LIPS.

In the latest ABAP version (7.58), you can't indicate SELECT in the list of columns (right after SELECT or right after FIELDS). A CTE must be indicated in the list of data sources (after FROM, usually in a join).

In short, you want to do CASE WHEN EXISTS ( SELECT * FROM z_tab1 WHERE ... ) THEN ... ELSE ... ENDCASE. The simplest way to simulate it is to use a left join with 0 or 1 possible result on the right (z_tab1) and test the joined column with IS NULL which means 0 result, versus IS NOT NULL which means 1 result, e.g. (here, I use t023 instead of z_tab1, and j_1bnbm instead of flag):

SELECT CASE
         WHEN t023~matkl IS NOT NULL
         THEN 'X'
         ELSE ' '
       END AS result_field
  FROM mara
  LEFT JOIN t023 ON t023~matkl   = mara~matkl
                AND t023~j_1bnbm = 'NBM1'
  INTO TABLE @DATA(i_tab).

This left join works only because matkl is a unique key of t023, hence it produces maximum 1 row per row of MARA.

In case z_tab1 may have more than 1 row, only then a CTE is useful, e.g. (here, I use eban instead of z_tab1, and wepos instead of flag, also notice the use of DISTINCT):

WITH +eban AS (
    SELECT DISTINCT matkl
      FROM eban
      WHERE wepos = 'X' )
SELECT CASE 
         WHEN +eban~matkl IS NOT NULL
         THEN 'X'
         ELSE ' '
       END AS result_field
  FROM mara
  LEFT JOIN +eban ON +eban~matkl = mara~matkl
  INTO TABLE @DATA(i_tab).