topic Re: Inner join not working. in Application Development and Automation Discussions

Inner join not working.

former_member677973 — Fri, 10 Jul 2020 13:39:17 GMT

when i comment inner join lfa1 line, only then data is getting fetched
SELECT a~rldnr,
a~rbukrs,
a~gjahr,
a~belnr,
a~racct,
a~rcntr,
a~prctr,
a~wsl,
a~budat,
a~blart,
a~zuonr,
a~lifnr,
a~ps_posid,
a~sgtxt,
a~koart,
a~timestamp,
b~saknr,
b~txt50,
c~bukrs,
c~belnr AS belnr1,
c~gjahr AS gjahr1,
c~bktxt,
d~name1,
d~lifnr AS lifnr1,
e~posid,
e~post1,
f~kostl,
f~ltext
FROM acdoca AS a
INNER JOIN skat AS b ON a~racct EQ b~saknr AND b~spras = @sy-langu
INNER JOIN bkpf AS c ON a~rbukrs EQ c~bukrs AND a~gjahr EQ c~gjahr AND a~belnr EQ c~belnr
INNER JOIN lfa1 AS d ON a~lifnr EQ d~lifnr INNER JOIN prps AS e ON a~ps_posid EQ e~posid
LEFT OUTER JOIN cskt AS f ON a~rcntr EQ f~kostl AND f~spras = @sy-langu
INTO TABLE @DATA(lt_acdoca)
WHERE a~rldnr = @lc_led
AND a~rbukrs = @p_rbukrs
AND a~belnr IN @so_belnr
AND a~racct IN @so_racct.

Re: Inner join not working.

matt — Fri, 10 Jul 2020 14:43:18 GMT

Please use the "code" button when posting code.

Re: Inner join not working.

Sandra_Rossi — Fri, 10 Jul 2020 16:41:13 GMT

Please format your code with button "CODE".

Re: Inner join not working.

gasparerdelyi — Mon, 13 Jul 2020 22:36:15 GMT

Do you not have matching records in LFA1?
An inner join filters against the other side per definition...

Re: Inner join not working.

michael_piesche — Tue, 14 Jul 2020 09:26:19 GMT

Just as you did with table CSKT, define for LFA1 a LEFT JOIN instead of an INNER JOIN, because the reason for your select to not have any hits with the INNER JOIN, is that either your selected ACDOCA~LIFNR records do either not contain any data, or they do not contain any matching LFA1~LIFNR data, which could also because of missing zeros for ACDOCA~LIFNR. So first analyse what data you have in your selected ACDOCA records for attribute LIFNR, and then compare it to those in LFA1 for attribute LIFNR. You might have to analyse it in the debugger to realize whether it is because of missing zeros or not.

SELECT a~rldnr, a~rbukrs, a~gjahr, a~belnr, a~racct, a~rcntr, a~prctr, a~wsl, a~budat,
       a~blart, a~zuonr, a~lifnr, a~ps_posid, a~sgtxt, a~koart, a~timestamp,
       b~saknr, b~txt50, c~bukrs, c~belnr AS belnr1, c~gjahr AS gjahr1, c~bktxt,
       d~name1, d~lifnr AS lifnr1, e~posid, e~post1, f~kostl, f~ltext
FROM acdoca AS a
INNER JOIN skat AS b ON a~racct EQ b~saknr AND b~spras = @sy-langu
INNER JOIN bkpf AS c ON a~rbukrs EQ c~bukrs AND a~gjahr EQ c~gjahr AND a~belnr EQ c~belnr
LEFT  JOIN lfa1 AS d ON a~lifnr EQ d~lifnr
INNER JOIN prps AS e ON a~ps_posid EQ e~posid
LEFT  JOIN cskt AS f ON a~rcntr EQ f~kostl AND f~spras = @sy-langu
INTO TABLE @DATA(lt_acdoca).
WHERE a~rldnr = @lc_led
  AND a~rbukrs = @p_rbukrs
  AND a~belnr IN @so_belnr
  AND a~racct IN @so_racct.

cl_demo_output=>display( lt_acdoca ).

Once you have assessed the problem, this will explain to you, why you dont hit any records with the INNER JOIN for LFA1. Afterwards, the community will be able to help you out more, but this would require another community question in case it deals with a situation where ACDOCA~LIFNR is not filled or filled incorrectly.

Re: Inner join not working.

michael_piesche — Sun, 13 Sep 2020 04:18:13 GMT

shanmukhtejas6242_3, do you continue to have issues or were you able to solve your problem?

Please add comments to your question that further describe your problem or add an answer that describes how you solved your problem.

If your problem is solved, accept an answer if it helped you and please close the question.