how to write select query using for all entries

[Former Member]

Hi,

following is my select query which is wrritten using joins.

DATA: BEGIN OF IT_MKPF_MSEG OCCURS 0,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

BUDAT LIKE MKPF-BUDAT,

BWART LIKE MSEG-BWART,

MATNR LIKE MSEG-MATNR,

MENGE LIKE MSEG-MENGE,

MEINS LIKE MSEG-MEINS,

DMBTR LIKE MSEG-DMBTR,

WERKS LIKE MSEG-WERKS,

END OF IT_MKPF_MSEG.

SELECT MKPF_{MBLNR MKPF}MJAHR MKPF~BUDAT

MSEG_{BWART MSEG}MATNR MSEG_{MENGE MSEG}MEINS MSEG_{DMBTR MSEG}WERKS

INTO TABLE y_i_MKPF_MSEG

FROM MKPF JOIN MSEG

ON MKPF_{MBLNR EQ MSEG}MBLNR

WHERE MKPF~BUDAT BETWEEN AUX_LOW AND AUX_HIGH

AND MKPF~MJAHR EQ AUX_CURRENT+0(4)

AND MKPF~VGART EQ 'WS'

AND MSEG~ZEILE EQ 1

AND MSEG~WERKS EQ SO_WERKS

AND MSEG~MATNR EQ y_wa_PKHD-MATNR

AND MSEG~BWART IN ('131', '132').

please tell me how to write the same select statement using

for all entries.

[Former Member]

Hi,

following is my select query which is wrritten using joins.

DATA: BEGIN OF IT_MKPF_MSEG OCCURS 0,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

BUDAT LIKE MKPF-BUDAT,

BWART LIKE MSEG-BWART,

MATNR LIKE MSEG-MATNR,

MENGE LIKE MSEG-MENGE,

MEINS LIKE MSEG-MEINS,

DMBTR LIKE MSEG-DMBTR,

WERKS LIKE MSEG-WERKS,

END OF IT_MKPF_MSEG.

SELECT MKPF_{MBLNR MKPF}MJAHR MKPF~BUDAT

MSEG_{BWART MSEG}MATNR MSEG_{MENGE MSEG}MEINS MSEG_{DMBTR MSEG}WERKS

INTO TABLE y_i_MKPF_MSEG

FROM MKPF JOIN MSEG

ON MKPF_{MBLNR EQ MSEG}MBLNR

WHERE MKPF~BUDAT BETWEEN AUX_LOW AND AUX_HIGH

AND MKPF~MJAHR EQ AUX_CURRENT+0(4)

AND MKPF~VGART EQ 'WS'

AND MSEG~ZEILE EQ 1

AND MSEG~WERKS EQ SO_WERKS

AND MSEG~MATNR EQ y_wa_PKHD-MATNR

AND MSEG~BWART IN ('131', '132').

please tell me how to write the same select statement using

for all entries.

[Former Member]

Hi,

Check this once

SELECT MKPF_{MBLNR MKPF}MJAHR MKPF~BUDAT

MSEG_{BWART MSEG}MATNR MSEG_{MENGE MSEG}MEINS MSEG_{DMBTR MSEG}WERKS

FROM MKPF

INTO TABLE y_i_MKPF_MSEG

FOR ALL ENTRIES IN MSEG

where MKPF_{MBLNR = MSEG}MBLNR

and MKPF~BUDAT BETWEEN AUX_LOW AND AUX_HIGH

AND MKPF~MJAHR EQ AUX_CURRENT+0(4)

AND MKPF~VGART EQ 'WS'

AND MSEG~ZEILE EQ 1

AND MSEG~WERKS EQ SO_WERKS

AND MSEG~MATNR EQ y_wa_PKHD-MATNR

AND MSEG~BWART IN ('131', '132').

Regards

[Former Member]

Hi,

write like this,

first select from mkpf table into l_tab_mkpf

where condition..

then,

if not l_tab_mkpf [] is initial.

select from mseg table into l_tab_mseg

for all entries in l_tab_mkpf

where condition ..

in the loop u can all these fields into final internal table abd display the same.

endif.

sample code,

select bukrs

saknr

lifnr

hkont

fdlev

gjahr

belnr

fdgrp

buzei

from

bseg

into table l_tab_bseg

where

bukrs in l_r_bukrs

and

belnr in l_r_belnr

and

gjahr in l_r_gjahr

and

hkont in l_r_hkont.

sort l_tab_bseg by bukrs belnr hkont.

*Get Planning Level for all accounts using account master.

if l_tab_bseg[] is not initial.

select bukrs

saknr

fdlev

waers

from

skb1

into table

lt_skb1

for all entries in

l_tab_bseg

where

bukrs = l_tab_bseg-bukrs

and

saknr = l_tab_bseg-hkont

and

( fdlev = 'B1' or fdlev = 'B2' ).

endif.

Now loop condition.

seshu.

[former_member609120]

.

DATA: BEGIN OF ITAB_MKPF OCCURS 0,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

BUDAT LIKE MKPF-BUDAT,

END OF ITAB_MKPF.

DATA: BEGIN OF ITAB_MSEG OCCURS 0,

MBLNR LIKE MSEG-MBLNR,

BWART LIKE MSEG-BWART,

MATNR LIKE MSEG-MATNR,

MENGE LIKE MSEG-MENGE,

MEINS LIKE MSEG-MEINS,

DMBTR LIKE MSEG-DMBTR,

WERKS LIKE MSEG-WERKS,

END OF ITAB_MSEG.

First Write select for MKPF table

select mblnr mjahr budat from mkpf into table itab_mkpf

where mjahr = aux_current+0(4) and vgart = 'WS' and budat between aux_low and aux_high.

Now the for all entries query

select bwart matnr menge meins dmbtr werks from mseg into table itab_mseg for all entries in itab_mkpf

where mblnr = itab_mkpf-mblnr and zeile = '1' and werks = so_werks and matnr = y_wa_pkhd-matnr and bwart in ('131','132').

[Former Member]

Try the following code:

select MBLNR

MJAHR

BUDAT

from mkpf

into table y_i_MKPF

where BUDAT BETWEEN AUX_LOW AND AUX_HIGH

AND MJAHR EQ AUX_CURRENT+0(4)

AND MKPF~VGART EQ 'WS'.

if sy-subrc eq 0 .

SELECT BWART MATNR MENGE MEINS DMBTR WERKS

INTO TABLE y_i_MSEG

FROM MSEG

for all entries in y_i_MKPF

WHERE mblnr eq y_i_MKPF-mblnr

and mjahr eq y_i_MKPF-mjahr

and ZEILE EQ 1

AND WERKS EQ SO_WERKS

AND MATNR EQ y_wa_PKHD-MATNR

AND BWART IN ('131', '132').

endif .

Reward if helpful.

Regards,

Swapnil.

[Former Member]

HI,

here is the code...

types : begin of ty_mkpf,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

BUDAT LIKE MKPF-BUDAT,

end of ty_mkpf.

types : begin of ty_mseg,

BWART LIKE MSEG-BWART,

MATNR LIKE MSEG-MATNR,

MENGE LIKE MSEG-MENGE,

MEINS LIKE MSEG-MEINS,

DMBTR LIKE MSEG-DMBTR,

WERKS LIKE MSEG-WERKS,

mblnr type mseg-mblnr,

end of ty_mseg.

types : begin of ty_tot,

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

BUDAT LIKE MKPF-BUDAT,

BWART LIKE MSEG-BWART,

MATNR LIKE MSEG-MATNR,

MENGE LIKE MSEG-MENGE,

MEINS LIKE MSEG-MEINS,

DMBTR LIKE MSEG-DMBTR,

WERKS LIKE MSEG-WERKS,

end of ty_tot.

data : int_mkpf type table of ty_mkpf,

fs_mkpf type ty_mkpf,

int_mseg type table of ty_mseg,

fs_mseg type ty_mseg,

int_tot type table of ty_tot,

fs_tot type ty_tot.

step 1: (select from MKPF)

select mblnr mjahr budat from MKPF into table int_mkpf

where budat ge aux_low and

budat le aux_high and

MJAHR EQ AUX_CURRENT+0(4) and

VGART EQ 'WS'.

step 2 : (select from MSEG based on MKPF's MBLNR)

select bwart matnr menge meins dmbtr werks mblnr into table int_mseg

for all entries in int_mkpf

where mblnr = int_mkpf-mblnr and

ZEILE EQ 1 and

WERKS EQ SO_WERKS and

MATNR EQ y_wa_PKHD-MATNR and

BWART eq '131' or

BWART eq '132'.

step 3: (combine the data from both the tables)

clear fs_mkpf.

loop at int_mkpf into fs_mkpf.

clear fs_mseg.

read table int_mseg into fs_mseg with key mblnr = fs_mkpf-mblnr.

if sy-subrc = 0.

clear ty_tot.

ty_tot-MBLNR = fs_mkpf-MBLNR .

ty_tot-MJAHR = fs_mkpf-MJAHR.

ty_tot-BUDAT = fs_mkpf-BUDAT.

ty_tot-BWART = fs_mseg-BWART.

ty_tot-MATNR = fs_mseg-MATNR.

ty_tot-MENGE = fs_mseg-MENGE.

ty_tot-MEINS = fs_mseg-MEINS.

ty_tot-DMBTR = fs_mseg-DMBTR.

ty_tot-WERKS = fs_mseg-WERKS.

append ty_tot to int_tot.

endif.

clear fs_mkpf.

endloop.

Kindly reward points if useful

Regards,

Shanthi.

Edited by: Shanthi on Mar 3, 2008 8:55 AM