topic Re: clarification on Select statement in Application Development and Automation Discussions

clarification on Select statement

Former Member — Wed, 16 May 2007 05:17:18 GMT

Hello all,

can u please any one tell me the solution.

In one condition.

i am getting the data from marc mkpf and mseg

my requirement is

get the material number from marc based on plant on selection screen

get some data from mkpf table based on posting date(budat) on selection screen entered .

then

get the material data from mseg table based on matnr = equal to point1

mblnr = equal to point2

mjahr = equal to point2.

here some fields is there that equal to selection screen fields.

for that i have written the code like this

one way:

select matnr

werks

from marc

into table out_it_marc

where werks in s_werks.

sort out_it_marc by matnr werks.

delete adjacent duplicates from out_it_marc

comparing matnr werks.

loop at out_it_marc into lfl_marc.

vr_matnr-low = lfl_marc-matnr.

vr_matnr-sign = c_in.

vr_matnr-option = c_eq.

append vr_matnr.

clear : lfl_marc.

endloop.

FORM get_mseg_data

tables i_r_matnr structure vr_matnr

USING in_it_mkpf type t_it_mkpf

changing out_it_mseg type t_it_mseg.

if not in_it_mkpf[] is initial.

select

MBLNR "Number of Material Document

MJAHR "Material Document Year

BWART "Movement Type (Inventory Management)

MATNR "Material Number

WERKS "Plant

LGORT "Storage location

SOBKZ "Special Stock Indicator

LIFNR "Vendor's account number

KUNNR "Account number of customer

DMBTR "Amount in local currency

MENGE "Quantity

MEINS "Base Unit of Measure

EBELN "Purchase order number

KOSTL "Cost Center

AUFNR "Order Number

BUKRS "company code

PS_PSP_PNR "Work Breakdown Structure Element (WBS Element)

SAKTO "G/L account number

from mseg

into table out_it_mseg

for all entries in in_it_mkpf

where mblnr eq in_it_mkpf-mblnr

and mjahr eq in_it_mkpf-mjahr

and bwart in s_bwart

and matnr in vr_matnr

and werks in s_werks

and kostl in s_kostl

and ps_psp_pnr in s_pspnr.

endif.

but its getting dump because of so many records

so that i have written again like this.

----

FORM get_mkpf_mseg_data USING in_it_marc type t_it_marc

changing out_it_mkpf_mseg type t_it_mkpf_mseg.

if not in_it_marc[] is not initial.

select A~mblnr

A~mjahr

A~budat

A~cpudt

A~cputm

A~usnam

A~xblnr

B~BWART "Movement Type (Inventory Management)

B~MATNR "Material Number

B~WERKS "Plant

B~LGORT "Storage location

B~SOBKZ "Special Stock Indicator

B~LIFNR "Vendor's account number

B~KUNNR "Account number of customer

B~DMBTR "Amount in local currency

B~MENGE "Quantity

B~MEINS "Base Unit of Measure

B~EBELN "Purchase order number

B~KOSTL "Cost Center

B~AUFNR "Order Number

B~BUKRS "company code

B~PS_PSP_PNR "Work Breakdown Structure Element (WBS Element)

B~SAKTO "G/L account number

INTO TABLE out_it_mkpf_mseg

from mkpf AS A INNER JOIN MSEG AS B

ON A_{MBLNR = B}MBLNR

AND A_{MJAHR = B}MJAHR

FOR ALL ENTRIES IN IN_IT_MARC

WHERE B~MATNR = IN_IT_MARC-MATNR

AND B~WERKS = IN_IT_MARC-WERKS

AND A~BUDAT IN S_BUDAT

AND B~BWART IN S_BWART

AND MATNR = IN_IT_MSEG-MATNR
AND WERKS = IN_IT_MSEG-WERKS

AND B~KOSTL IN S_KOSTL

AND B~PS_PSP_PNR IN S_PSPNR.

ENDIF.

BREAK-POINT.

ENDFORM. " get_mkpf_mseg_data

but this select statement is not working properly

is there anyway to solve it.

please give me the solution ASAP, its urgent.

Thanks in advance....

Re: clarification on Select statement

Former Member — Wed, 16 May 2007 05:23:47 GMT

Hi bs,

you dont\'t have to build a range table vr_matnr for the selection, you just can use for all entries. See help on WHERE-clause:

Suppose FTAB is filled as follows:
CARRID CONNID
--------------
LH 2415
SQ 0026
LH 0400

SELECT * FROM sflight INTO wa_sflight

FOR ALL ENTRIES IN ftab

WHERE CARRID = ftab-carrid AND

CONNID = ftab-connid AND

fldate = '20010228'.

free = wa_sflight-seatsocc - wa_sflight-seatsmax.

WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

ENDSELECT.

Cheers,

Stefan

Re: clarification on Select statement

Former Member — Wed, 16 May 2007 05:25:13 GMT

Hi Prasad,

The select statements you have written seem correct. Can you tell as to what exactly the dump message is. May be then i can help you out. try using "into corresponding fields of table" when you are using the select statement.

Karan

Re: clarification on Select statement

Former Member — Wed, 16 May 2007 05:26:51 GMT

Hi,

This is problem with your where clause, so first try to analyse withther in_it_mkpf have relevent data and ur other where clasue have correct data. bcoz joins are correct.

i am suggesting u to remove all where clasue and add clause one by one and debug the resul..

This will solve ur problem.

Jogdand M B

Re: clarification on Select statement

Former Member — Wed, 16 May 2007 05:35:17 GMT

hi,

i think how u declared your internal table [workarea also if u uesd external one ]. i mean the way and in same order of fields you used in your seelct query please check it once both should be in same order and in same sequence

Re: clarification on Select statement

Former Member — Mon, 23 Jul 2007 13:02:12 GMT

Hi,

The problem has been solved.

Thanks a lot..