topic Re: select option from table which is left outer join in Application Development and Automation Discussions

select option from table which is left outer join

Former Member — Thu, 27 Sep 2007 12:01:15 GMT

Hi,

I have problems with select option field again. I have conected 3 tables EKKO and EKPO as inner join and EKKN table as left outer join.

How to include into select option fields from EKKN table?

Please give me an example,

Thanks in advance

here is my select:

SELECT EKKO_{BUKRS EKKO}EBELN EKKO_{LIFNR EKKO}LPONR EKPO~EBELN

EKPO_{EBELP EKPO}LGORT EKPO_{MEINS EKPO}MENGE EKPO~WERKS

EKKN_{AUFNR EKKN}EBELN EKKN_{EBELP EKKN}KOSTL

INTO (LW_EKKO-BUKRS, LW_EKKO-EBELN, LW_EKKO-LIFNR, LW_EKKO-LPONR,

LW_EKPO-EBELN, LW_EKPO-EBELP , LW_EKPO-LGORT , LW_EKPO-MEINS ,

LW_EKPO-MENGE, EKPO-WERKS, LW_EKKN-AUFNR, LW_EKKN-EBELN, LW_EKKN-EBELP,

LW_EKKN-KOSTL )

FROM ( EKKO

INNER JOIN EKPO

ON EKPO_{EBELN = EKKO}EBELN

AND EKPO_{EBELP = EKKO}LPONR

LEFT OUTER JOIN EKKN

ON EKKN_{EBELN = EKPO}EBELN

AND EKKN_{EBELP = EKPO}EBELP )

WHERE EKKO~EBELN IN EBELN

AND EKKO~LIFNR IN LIFNR

AND EKPO~EBELP IN EBELP

AND EKPO~WERKS IN WERKS.

MOVE-CORRESPONDING LW_EKKO TO INT.

MOVE-CORRESPONDING LW_EKPO TO INT.

MOVE-CORRESPONDING LW_EKKN TO INT.

APPEND INT.

ENDSELECT.

Re: select option from table which is left outer join

Former Member — Thu, 27 Sep 2007 12:07:49 GMT

not possible. you can better loop through the result table and make the selection on ekkn table and fill the corresponding fields accordingly if exist

loop at INT.

select single * from ekkn where EBELN EQ INT-EBELN and EBELP EQ INT-EBELP.

if sy-subrc = 0.

MOVE-CORRESPONDING EKKN TO INT.

modify INT.

endloop

Re: select option from table which is left outer join

Former Member — Thu, 27 Sep 2007 12:10:45 GMT

Hi ,

Don't use select ..endselect.

The performance of the program will get hampered,

so try to use loop ...and modify statement.

regards,

Santosh

Re: select option from table which is left outer join

varma_narayana — Thu, 27 Sep 2007 12:14:53 GMT

hi..

Point1: Dont use SELECT.. ENDSELECT with APPEND . It will give poor performance.

So change it using INTO CORRESPONDING FIELDS OF TABLE ITAB.

Declare the itab accordingly.

Point 2: You can check the SELECT-OPTIONS using WHERE Clause .

SELECT EKKO_{BUKRS EKKO}EBELN EKKO_{LIFNR EKKO}LPONR EKPO~EBELN

EKPO_{EBELP EKPO}LGORT EKPO_{MEINS EKPO}MENGE EKPO~WERKS

EKKN_{AUFNR EKKN}EBELN EKKN_{EBELP EKKN}KOSTL

INTO corresponding fields of table INT

FROM EKKO

INNER JOIN EKPO

ON EKPO_{EBELN = EKKO}EBELN

AND EKPO_{EBELP = EKKO}LPONR

LEFT OUTER JOIN EKKN

ON EKKN_{EBELN = EKPO}EBELN

AND EKKN_{EBELP = EKPO}EBELP

WHERE EKKO~EBELN IN EBELN

AND EKKO~LIFNR IN LIFNR

AND EKPO~EBELP IN EBELP

AND EKPO~WERKS IN s_WERKS. "Checking SELECT-OPTIONS

<b>Reward if Helpful.</b>

Re: select option from table which is left outer join

Jelena_Perfiljeva — Thu, 27 Sep 2007 21:17:45 GMT

Unfortunately, it is not possible to include the table in WHERE clause if it was joined with a LEFT JOIN.

You might want to modify SELECT ... ENDSELECT to SELECT INTO TABLE ITAB, for example, and then do a separate SELECT:

SELECT ... FROM EKKN 
FOR ALL ENTRIES IN ITAB 
WHERE EBELN = ITAB-EBELN
AND EBELP = ITAB-EBELP

plus whatever additional selection criteria you might need.

Good luck!

Re: select option from table which is left outer join

Former Member — Thu, 27 Sep 2007 21:26:39 GMT

if the user chooses to use the select-option for the table which was outer-joined, it means that it should no longer be outer-joined for that instance.

what i do in such cases is create a seperate select which is activated by checking if the specific select-option has a value.

this would be something like:

select-options: s_ekkn (whatever variable you wish to test from EKKN).

if s_ekkn is initial.

*do the regular join with ekkn being outer joined

else.

*connect ekkn as an inner join and use s_ekkn in the WHERE condition).

endif.

Re: select option from table which is left outer join

Former Member — Mon, 01 Oct 2007 08:05:30 GMT

Thanks Guys for all posts end coments, i rewarded 3 of you,

Saso

Re: select option from table which is left outer join

Former Member — Mon, 01 Oct 2007 08:06:30 GMT

Thanks a lot!!