select statement

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[former_member186741]

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.

[Former Member]

Hi ,

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

[Former Member]

Hi,

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

Regards

vijay

[Former Member]

Hi,

Can you give me that select statement and the DATA declarations

Message was edited by: amit teja

[Former Member]

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.

[Former Member]

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.

or

select * from wb2_v_mkpf_mseg2 where mblnr = mblnr

note : wb2_v_mkpf_mseg2 is database view

[Former Member]

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