I am trying to select some fields of the table BKPF by using the inner join of the tables BSEG and KNBK, into another table. The code for this is as below:
SELECT k~bukrs, k~belnr, k~budat, k~gjahr, s~buzei,
s~kunnr, i~banks, i~bankl, i~bankn
FROM bkpf AS k
INNER JOIN bseg AS s "Error
ON s~bukrs = k~bukrs AND
s~belnr = k~belnr AND
s~gjahr = k~gjahr
INNER JOIN knbk AS i
ON i~kunnr = s~kunnr
INTO TABLE @DATA(lt_data_knbk)
WHERE k~bukrs IN @so_bukrs
AND k~budat IN @so_budat
AND k~blart IN @so_blart.
The last three where conditions are select options. The error that I got from activating the program is from the bold line, where the error message is: JOIN is not allowed for pool tables, cluster tables, and projection views: ‘BSEG’.
May anyone know, how can I fix this problem?
Thank you in advance for your kind help!
BSEG is a cluster table, it cannot be indcuded in JOINs (if you does not use a HANA DB, but the error message says, it does not). To overcome this, the table has to be selected in a separate step. In your case this would actually mean three separate SELECTs, as BKPF cannot be JOINed with KNBK (no common field(s)).
So in this case, it is better to use the transparent tables for customer postings (BSID and BSAD), these can be included in a JOIN. The only disadvantage is, that the postings are separated: BSID: customer open postings BSAD: customer cleared postings so two separate SELECTs will be necessary (depending on the exact need, but this was stated in the original question).
In this case you can forget BKPF, as all fields you actually use in your code, are in BSID/BSAD.
SELECT bsid~bukrs, bsid~belnr, bsid~budat, bsid~gjahr, bsid~buzei,
bsid~kunnr, knbk~banks, knbk~bankl, knbk~bankn
FROM bsid
INNER JOIN knbk
ON bsid~kunnr EQ knbk~kunnr
INTO TABLE @DATA(lt_bsid_knbk)
WHERE bsid~bukrs IN @s_bukrs
AND bsid~budat IN @s_budat
AND bsid~blart IN @s_blart.
As told this has to be repeated, BSID has to be replaced by BSAD.