topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Mon, 17 Jul 2006 05:48:49 GMT

Hi,

if i have to select Doc no.,posting date,material description for the particular Doc no. what is the select Query.

Doc no., is from mseg,

Posting date is from mkpf,

Material description is from makt.

Re: select statement

Former Member — Mon, 17 Jul 2006 05:53:27 GMT

Make a join on MKPF and MSEG, use the materials fetched above and select from MAKT..for material description..

thereby you are using two big tables in a join ,and getting the rest of the info in another select.

Re: select statement

Former Member — Mon, 17 Jul 2006 05:57:19 GMT

Hi amit ,

first select MBLNR BUDAT MATNR MENGE from MKPF+ MSEG.

and get the MATNR from that List , and get the MAKTX from MAKT.

SELECT A~MBLNR
         A~MJAHR
         A~VGART
         A~BUDAT
         A~XBLNR
         B~ZEILE
         B~BWART
         B~MATNR
         B~WERKS
         B~KUNNR
         B~SHKZG
         B~MENGE
         B~meins
   FROM  ( MKPF AS A JOIN MSEG AS B
   ON    A~MBLNR = B~MBLNR  AND
         A~MJAHR = B~MJAHR )
   INTO  TABLE T_MSEG
   WHERE A~BUDAT GE S_DATE-LOW     AND
         A~VGART in ('WA' ,'WL')   AND
         B~MATNR in S_MATNR        AND
         B~WERKS IN S_WERKS1       AND
         B~SOBKZ EQ 'V'            AND
         B~KUNNR EQ W_CUST         .

like .

data : r_matnr for makt-matnr.

loop at t_mseg.

r_matnr-sign = 'I'.

r_matnr-option = 'EQ'.

r_matnr-low = t_mseg-matnr.

apppend r_matnr.

endloop.

sort r_matnr by low.

delete adjacent duplicates from r_matnr comparing low.

sort r_matnr by low.

if r_matnr[] is not initial.

select matnr maktx

frm makt

into corresponding fields of table i_makt.

where matnr in r_matnr

and SPRAS eq 'E'.

endif/

in this case no need to check the Performance of report.

Regards

Prabhu

endloop.

Re: select statement

former_member186741 — Mon, 17 Jul 2006 05:57:43 GMT

types: begin of ty_m,

mblnr type mkpf-mblnr,

budat type mkpf-budat,

maktx type makt-maktx, end of ty_m.

data it_m type table of ty_m with header line

data w_mblnr type mkpf-mblnr.

select mkpf_{mblnr mkpf}budat makt~maktx

into corresponding fields of table it_m

from mkpf

join mseg on mseg_{mblnr = mkpf}mblnr

join makt on makt_{matnr = mseg}matnr

and spras = sy-langu

where mkpf~mblnr = w_mblnr.

Re: select statement

Former Member — Mon, 17 Jul 2006 05:58:15 GMT

Hi ,

Thank u Can you give me a select query for this.

Re: select statement

Former Member — Mon, 17 Jul 2006 05:59:58 GMT

Hi,

use join on mseg ,mkpf ,and use <b>for all entries</b> and get the material description from makt table.

Regards

vijay

Re: select statement

Former Member — Mon, 17 Jul 2006 06:01:13 GMT

select a_{mblnr a}mjahr a_{budat a}bldat

b_{matnr b}werks b_{zeile b}lifnr b_{erfmg b}ebeln b~ebelp into corresponding fields of table itab

from ( mkpf as a inner join mseg as b

on a_{mblnr = b}mblnr and a_{mjahr = b}mjahr )

where a~mblnr = mblnr.

select * from wb2_v_mkpf_mseg2 where mblnr = mblnr

note : wb2_v_mkpf_mseg2 is database view

Re: select statement

Former Member — Mon, 17 Jul 2006 06:05:33 GMT

Hi,

Can you give me that select statement and the DATA declarations

Message was edited by: amit teja

Re: select statement

Former Member — Mon, 17 Jul 2006 06:06:32 GMT

When ever u have to write select statement from 2 table its strongly recommended that you select the values from first header table (here its MKPF table in ur case ) than from the values from segement table (ie mseg in this case ) Than u select values from Makt .

Similarly for PO details selects 1st from EKKO ( header Po table first ) than from the line item table ie ekpo.

Similary from Delivery ir select vales fro LIKP ( delivery header table ) than from the Details table Lips.

Cheers

Niraj Kumar\

Patni Computers Systems

Re: select statement

Former Member — Mon, 17 Jul 2006 06:31:23 GMT

TABLES : MKPF, MSEG.

DATA : BEGIN OF ITAB.

MBLNR LIKE MKPF-MBLNR,

MJAHR LIKE MKPF-MJAHR,

BUDAT LIKE MKPF-BUDAT,

BLDAT LIKE MKPF-BLDAT,

MATNR LIKE MSEG-MATNR,

WERKS LIKE MSEG-WERKS,

ZEILE LIKE MSEG-ZEILE,

LIFNR LIKE MSEG-LIFNR,

ERFMG LIKE MSEG-ERFMG,

EBELN LIKE MSEG-EBELN,

EBELP LIKE MSEG-EBELP,

END OF ITAB.