Left join on 2 tables in ABAP 7.02

1
votes

I want to join 4 tables T001, KNKK, KNB1 and KNA1, to get all customers from KNB1/KNA1 and their Risk Category in KNKK.

We need a left join from KNKK but the join condition is based on 2 tables: the T001-KKBER and KNA1-KUNNR.

But I get the syntax error:

Unable to compare with "T001~KKBER". A table can be joined with a maximum of one other table using LEFT OUTER JOIN.

The select is: 

SELECT knb1~kunnr kna1~name1 kna1~lifnr knb1~akont
       INTO TABLE git_kunnr_tab
       FROM kna1
       INNER JOIN knb1
       ON kna1~kunnr EQ knb1~kunnr
       INNER JOIN t001
       ON knb1~bukrs EQ t001~bukrs
       LEFT JOIN knkk
       ON knb1~kunnr EQ knkk~kunnr AND
          t001~kkber EQ knkk~kkber
       WHERE knb1~kunnr IN s_kunnr
          AND knb1~bukrs = p_bukrs.

Our system is old ECC6 7.02 so we cannot implement the new features of ABAP.

Is there any way to do it in 1 select?

2
abap
Could you provide the ABAP SQL (MRE) with the syntax error please? Thanks. – Sandra Rossi
Can you try to remove one of the JOIN conditions of KNKK and move it to the WHERE conditions? So, LEFT JOIN knkk ON knb1~kunnr EQ knkk~kunnr WHERE ... AND t001~kkber EQ knkk~kkber. – József Szikszai
I did it but again I received the same error message. – ekekakos
Than you have to break into 2 select statements, as you did... – József Szikszai

2 Answers

0
votes

Not sure, what error you get, but this is syntactically correct:

SELECT ...
       INTO TABLE ...
       FROM kna1
       INNER JOIN knb1
       ON kna1~kunnr EQ knb1~kunnr
       INNER JOIN t001
       ON knb1~bukrs EQ t001~bukrs
       LEFT JOIN knkk
       ON knb1~kunnr EQ knkk~kunnr AND
          t001~kkber EQ knkk~kkber
       WHERE ...
0
votes

I believe that there is no way to do a join with all these tables and select ALL customers from KNA1 even if they do not exist in KNKK.
Maybe there is a way with SubQuery but I cannot think sth now.
So I made 2 selects and fill 2 ITABs and then I loop into the one with KUNNR & CTLPC and modify the 2nd by passing the CTLPC value. So the code is now:

select knb1~kunnr kna1~name1 kna1~lifnr knb1~akont
           knb1~fdgrv knb1~frgrp 
          into table git_kunnr_tab
        from knb1 as knb1 left join kna1 as kna1
            on knb1~kunnr = kna1~kunnr
        where knb1~kunnr in s_kunnr
          and knb1~bukrs = p_bukrs.

select knkk~kunnr knkk~ctlpc into table lit_kunnr_risk
    from knkk as knkk inner join t001 as t001
        on knkk~kkber = t001~kkber
    where t001~bukrs = p_bukrs and
          knkk~ctlpc <> ''.

  sort git_kunnr_tab by kunnr.
  loop at lit_kunnr_risk into lwa_kunnr_risk.
    gwa_kunnr_tab-ctlpc = lwa_kunnr_risk-ctlpc.
    modify git_kunnr_tab from gwa_kunnr_tab transporting ctlpc
                         where kunnr = lwa_kunnr_risk-kunnr.
    clear gwa_kunnr_tab.
  endloop.

If there is a better way to do this, please post it.