topic Re: Problem with Left Outer Join in Application Development and Automation Discussions

Problem with Left Outer Join

Former Member — Wed, 14 Dec 2005 17:30:29 GMT

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 AFKO_{AUFNR AFKO}GETRI AFPO_{AUFNR AFPO}SERNP QMEL_{FERTAUFNR QMEL}QMNUM QMFE_{FENUM QMFE}QMNUM

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

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

FROM ( AFKO

INNER JOIN AFPO

ON AFPO_{AUFNR = AFKO}AUFNR

LEFT OUTER JOIN QMEL

ON QMEL_{FERTAUFNR = AFPO}AUFNR

INNER JOIN QMFE

ON QMFE_{QMNUM = QMEL}QMNUM )

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.

Re: Problem with Left Outer Join

Former Member — Wed, 14 Dec 2005 17:37:22 GMT

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.

Re: Problem with Left Outer Join

Former Member — Wed, 14 Dec 2005 17:43:55 GMT

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?

Re: Problem with Left Outer Join

Former Member — Wed, 14 Dec 2005 17:51:18 GMT

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

Re: Problem with Left Outer Join

Former Member — Wed, 14 Dec 2005 18:29:21 GMT

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.

Re: Problem with Left Outer Join

Former Member — Wed, 14 Dec 2005 18:37:34 GMT

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

Re: Problem with Left Outer Join

Former Member — Wed, 11 Jun 2008 20:46:46 GMT

Thanks gentlemen fo rall your help.