Conditional field by selected column in JOIN query

Question

At the moment my query looks like this

 SELECT CSKS~KOSTL CSKS~DATBI CSKS~NAME1 CSKS~KOSAR CSKS~VERAK CEPC~PRCTR CEPC~NAME3 CEPC~NAME4 UP TO X ROWS
 FROM CSKS INNER JOIN CEPC ON ( CSKS~PRCTR = CEPC~PRCTR AND CSKS~KOKRS = CEPC~KOKRS )
   INTO gwa_itab
 WHERE CSKS~BUKRS = cocode AND
    CSKS~KOKRS = cntrarea AND
   CSKS~DATBI  > d1.
   APPEND gwa_itab TO it_itab.
 ENDSELECT.

How can I modify it so it will see what number the cost centers start with and if they start with 1 for example, insert into the column Location which is already in the internal table a given string EG: Washington.

Your example query is a `select`. It does not "insert" data into a column. — Gordon Linoff, Feb 11 '20 at 12:30
@GordonLinoff Yes I know, but I need it to retrieve data from the table and based on what it retrieves, to look at cost centers and insert into the Location column — Danilo Vuita, Feb 11 '20 at 12:47
Your objective is a bit unclear: You want to check if a Cost Center starts with '1' and if so, then insert into the internal table, a specific value in the 'location' column ? Is that correct? — Ovidiu Pocnet, Feb 11 '20 at 13:00

Ovidiu Pocnet · Accepted Answer · 2020-02-11T13:08:46.147

1

 SELECT CSKS~KOSTL CSKS~DATBI CSKS~NAME1 CSKS~KOSAR CSKS~VERAK CEPC~PRCTR CEPC~NAME3 CEPC~NAME4 UP TO X ROWS
     FROM CSKS INNER JOIN CEPC ON ( CSKS~PRCTR = CEPC~PRCTR AND CSKS~KOKRS = CEPC~KOKRS )
       INTO gwa_itab
     WHERE CSKS~BUKRS = cocode AND
        CSKS~KOKRS = cntrarea AND
       CSKS~DATBI  > d1.

    SHIFT gwa_itab-kostl LEFT DELETING LEADING '0'.
      IF gwa_itab-kostl(1) = 1.
        gwa_itab-location = 'Washington'.
      ENDIF.

       APPEND gwa_itab TO it_itab.
       CLEAR: gwa_itab.
     ENDSELECT.

edited Feb 11 '20 at 13:08

answered Feb 11 '20 at 12:00

Ovidiu Pocnet

579
12
32

Thy this solution – Ovidiu Pocnet Feb 11 '20 at 13:09

Suncatcher · Answer 2 · 2020-02-12T09:19:51.520

On recent 7.50+ releases you can end up with single select without SELECT looping:

SELECT ks~kostl, ks~datbi, ks~name1, ks~kosar, ks~verak, pc~prctr, pc~name3, pc~name4,
        CASE SUBSTRING( ks~kostl, 1, 1 )
          WHEN '1' THEN 'Washington'
        END AS location
  FROM csks AS ks
 INNER JOIN cepc AS pc
    ON ks~prctr = pc~prctr AND ks~kokrs = pc~kokrs

 WHERE ks~bukrs = cocode AND
       ks~kokrs = cntrarea AND
       ks~datbi  > d1

  INTO TABLE @DATA(it_itab)
    UP TO 100 ROWS.

Conditional field by selected column in JOIN query

2 Answers2