Inner join not working.

[former_member677973]

1. when i comment inner join lfa1 line, only then data is getting fetched
2. SELECT a~rldnr,
3. a~rbukrs,
4. a~gjahr,
5. a~belnr,
6. a~racct,
7. a~rcntr,
8. a~prctr,
9. a~wsl,
10. a~budat,
11. a~blart,
12. a~zuonr,
13. a~lifnr,
14. a~ps_posid,
15. a~sgtxt,
16. a~koart,
17. a~timestamp,
18. b~saknr,
19. b~txt50,
20. c~bukrs,
21. c~belnr AS belnr1,
22. c~gjahr AS gjahr1,
23. c~bktxt,
24. d~name1,
25. d~lifnr AS lifnr1,
26. e~posid,
27. e~post1,
28. f~kostl,
29. f~ltext
30. FROM acdoca AS a
31. INNER JOIN skat AS b ON a~racct EQ b~saknr AND b~spras = @sy-langu
32. INNER JOIN bkpf AS c ON a~rbukrs EQ c~bukrs AND a~gjahr EQ c~gjahr AND a~belnr EQ c~belnr
33. INNER JOIN lfa1 AS d ON a~lifnr EQ d~lifnr INNER JOIN prps AS e ON a~ps_posid EQ e~posid
34. LEFT OUTER JOIN cskt AS f ON a~rcntr EQ f~kostl AND f~spras = @sy-langu
35. INTO TABLE @DATA(lt_acdoca)
36. WHERE a~rldnr = @lc_led
37. AND a~rbukrs = @p_rbukrs
38. AND a~belnr IN @so_belnr
39. AND a~racct IN @so_racct.

[matt]

Please use the "code" button when posting code.

[Sandra_Rossi]

Please format your code with button "CODE".

[gasparerdelyi]

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

[michael_piesche]

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.

[michael_piesche]

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.