Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with Left Outer Join

Former Member
0 Kudos

I'm experiencing a problem with my select statement below. I've created a join on 4 tables, namely.

AFKO (Order Hdr), AFPO (Order Itm), QMEL (Qnote), QMFE (Qnote problem codes).

I expect the result to give me all the matching records for QMEL and QMFE on QMNUM (Qnote no) but its only returning one entry of QMFE.

Any suggestions is very much appreciated.

REPORT ZTEST14 .

tables: afko, afpo, qmel, qmfe.

select-options: sgentr for afko-getri,

ssernp for afpo-sernp.

types: begin of stab,

aufnr type aufnr,

getri type datum,

sernp type serail,

qnote type qmnum,

fenum type fenum,

end of stab.

data: itab type table of stab.

refresh: itab.

START-OF-SELECTION.

SELECT AFKOAUFNR AFKOGETRI AFPOAUFNR AFPOSERNP QMELFERTAUFNR QMELQMNUM QMFEFENUM QMFEQMNUM

INTO (AFKO-AUFNR , AFKO-GETRI , AFPO-AUFNR , AFPO-SERNP

, QMEL-FERTAUFNR , QMEL-QMNUM , QMFE-FENUM , QMFE-QMNUM )

FROM ( AFKO

INNER JOIN AFPO

ON AFPOAUFNR = AFKOAUFNR

LEFT OUTER JOIN QMEL

ON QMELFERTAUFNR = AFPOAUFNR

INNER JOIN QMFE

ON QMFEQMNUM = QMELQMNUM )

WHERE AFKO~GETRI IN SGENTR

AND AFPO~SERNP IN SSERNP.

write:/1 AFKO-AUFNR , 11 AFKO-GETRI , 22 AFPO-AUFNR , 33 AFPO-SERNP , 44 QMEL-FERTAUFNR , 55 QMEL-QMNUM , 66 QMFE-FENUM , 72 QMFE-QMNUM

.

endselect.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi,

Please use the following function module by using QMEL-QMNUM this will give all the data you needed. Join may leads to performance related issues.

CALL FUNCTION 'READ_NOTIFICATION'

EXPORTING

QMNUM = QMEL-QMNUM

I_VIQMEL = QMEL

IV_AKTYP = 'A'

TABLES

IVIQMFE = IWQMFE

IVIQMMA = IWQMMA

IVIQMSM = IWQMSM

IVIQMUR = IWQMUR

TRIWO020TAB = G_RIWO020TAB

EXCEPTIONS

INVALID_NUMBER = 1

OTHERS = 2.

Need more help on this function let me know.

6 REPLIES 6

Former Member
0 Kudos

Hi,

Please use the following function module by using QMEL-QMNUM this will give all the data you needed. Join may leads to performance related issues.

CALL FUNCTION 'READ_NOTIFICATION'

EXPORTING

QMNUM = QMEL-QMNUM

I_VIQMEL = QMEL

IV_AKTYP = 'A'

TABLES

IVIQMFE = IWQMFE

IVIQMMA = IWQMMA

IVIQMSM = IWQMSM

IVIQMUR = IWQMUR

TRIWO020TAB = G_RIWO020TAB

EXCEPTIONS

INVALID_NUMBER = 1

OTHERS = 2.

Need more help on this function let me know.

0 Kudos

Thanks Lanka,

Actually this join statement was generated by SAP from an Infoset I'm building. I created an SAP Query and was trying to find a solution so I copy and pasted the join and placed it in an abap program to play with it.

Do you know if an Inner Join after an Outer Join causes it to become another Inner?

0 Kudos

Ok. Then try to combine tables .

USe Views : CUAFV (AFKO + AUFK)

VIQMFEL (QMFE,QMEL,QMIH,ILOA)

VIQMEL

Because you join really leads to performance problems.

Message was edited by: Lanka Murthy

Former Member
0 Kudos

Basically, the inner join will give you a record everytime the join conditions are met. The outter join fields will be null where the join conditions are not met and populated when they are met. So if you are not getting a record, then look to the inner join. If you are getting a record and the outer join fields are null then look into the relationships of the outter join.

Regards,

Brent

Former Member
0 Kudos

If ur using 4 tables using a single select statement it will lead to performance problem....so try to combine join 2 tables or 3 tables in an internal table and with this internal table and the other table use 'select for all entries ' to get ur data.

for now u can get easily. but in production you will have huge data. so try to reduce as possible as it is.

Former Member
0 Kudos

Thanks gentlemen fo rall your help.