topic Re: selecting records... in Application Development and Automation Discussions

selecting records...

aris_hidalgo — Wed, 12 Apr 2006 05:41:02 GMT

Hello experts,

I am currently having a problem with the select statement below.

**-get material documents "1st select statement

SELECT mblnr mjahr budat xblnr tcode2 cputm blart INTO TABLE it_mkpf

FROM mkpf

WHERE mblnr IN lr_mblnr

AND budat IN s_datum

AND tcode2 IN r_tcode2.

IF NOT it_mkpf[] IS INITIAL.

**- stock transfer order "2nd select statement

SELECT mblnr mjahr matnr ebeln ebelp menge

INTO TABLE it_mseg

FROM mseg

FOR ALL ENTRIES IN it_mkpf

WHERE mblnr = it_mkpf-mblnr

AND mjahr = it_mkpf-mjahr

AND bwart = '351'

AND werks = p_werks

AND ebeln IN s_mblnr

AND parent_id = '000000'

AND lgort IN r_lgort. "AVH - 04/11/06

IF NOT it_mseg[] IS INITIAL.

**-get dest sloc "3rd select statement

SELECT b_{ebeln b}ebelp b_{matnr b}lgort b_{matkl b}menge b~meins

a_{bedat a}aedat b_{txz01 b}bukrs

INTO TABLE it_ekpo

FROM ekpo AS b INNER JOIN ekko AS a

ON a_{ebeln = b}ebeln

FOR ALL ENTRIES IN it_mseg

WHERE b~ebeln = it_mseg-ebeln

AND b~ebelp = it_mseg-ebelp

AND b~matnr = it_mseg-matnr

AND ( b_{bukrs = c_globe OR b}bukrs = c_innove )

AND b~werks = p_werks

AND b~matkl IN s_matkl

AND b~matkl IN r_matkl.

IF NOT it_ekpo[] IS INITIAL.

*AVH - start of insertion - 04/10/06 "4th select

SELECT ebeln ebelp matnr lgort mblnr mjahr

menge meins bwart bukrs

FROM mseg

INTO TABLE it_mseg2

FOR ALL ENTRIES IN it_ekpo

WHERE ebeln = it_ekpo-ebeln

AND ebelp = it_ekpo-ebelp

AND matnr = it_ekpo-matnr

AND bwart = 'Z11'

AND parent_id = '000000'.

Now, my problem is the last select statement that I did must be equated to the first select statement by:

mblnr = it_mkpf-mblnr

AND mjahr = it_mkpf-mjahr

How can I do this since it already has an FOR ALL ENTRIES statement? Thanks guys and take care!

Re: selecting records...

Former Member — Wed, 12 Apr 2006 05:51:42 GMT

Hi,

Fill ranges for

mblnr AND mjahr by looping on it_mkpf

and then use these ranges in your last querry.

SELECT ebeln ebelp matnr lgort mblnr mjahr

menge meins bwart bukrs

FROM mseg

INTO TABLE it_mseg2

FOR ALL ENTRIES IN it_ekpo

WHERE ebeln = it_ekpo-ebeln

AND ebelp = it_ekpo-ebelp

AND matnr = it_ekpo-matnr

AND bwart = 'Z11'

AND parent_id = '000000'

<b>AND mblnr IN r_mblnr

AND mjahr IN r_mjahr.</b>

Hope it helps.

Regards,

Shashank

Re: selecting records...

Former Member — Wed, 12 Apr 2006 05:56:04 GMT

make a internal table it_mkpf as ur requirment.

sort it_mseg2.

loop at it_mkpf2.

read table it_mseg2 binary search with key mblnr = it_mkpf-mblnr

mjahr = it_mkpf-mjahr

it_mkpf = it_mkpf2.

append it_mkpf2.

clear it_mkpf2.

endloop.

Message was edited by: kishan negi