topic Re: Nested Select querry in Application Development and Automation Discussions

Nested Select querry

Former Member — Tue, 15 Jan 2008 07:43:59 GMT

Hello experts,

I want to get records from 3 tables at a time. Those are MARA, MSEG and MAKT. The fields are

MSEG - charg

ebeln

menge

bstmg

MARA - zmatnr1 (These are some z fields)

zthndia

zcolor1

MAKT - maktx.

How can i do it.

send the solution with code.

Points will be rewarded.

Thanks and Regards.

S Mahanta.

Re: Nested Select querry

JozsefSzikszai — Tue, 15 Jan 2008 07:48:53 GMT

hi Chandra,

SELECT mseg_{charg mseg}ebeln mseg_{menge mseg}bstmg

mara_{zmatnr1 mara}zthndia mara~zcolor1

makt~maktx

INTO TABLE itab "same structure like the fields above

FROM mseg AS mseg

INNER JOIN matnr AS matnr

ON mseg_{matnr EQ mara}matnr

INNER JOIN makt AS makt

ON mara_{matnr EQ makt}matnr

WHERE .... makt~langu EQ sy-langu.

if it is to slow, than you have to remove the INNER JOIN makt... (this can happen if material texts are maintained in more than one language) and make that in a separate selection and merge the two tables in the programs

hope this helps

Re: Nested Select querry

Former Member — Tue, 15 Jan 2008 08:14:11 GMT

Hi,

data: begin of g_t_intab occurs 0,

charg

ebeln

menge

bstmg

zmatnr1

zthndia

zcolor1

end of g_t_intab.

SELECT a_{charg a}ebeln a_{menge a}bstmg

b_{zmatnr1 b}zthndia b~zcolor1

c~maktx

INTO TABLE g_t_intab

FROM mseg AS a

INNER JOIN matnr AS b

ON a_{matnr = b}matnr

OUTER JOIN makt AS c

ON b_{matnr = c}matnr

WHERE

a~mblnr = ....

c~langu = sy-langu.

Use OUTER JOIN for MAKT as even if description doesnot exist material will be considered.

Re: Nested Select querry

Former Member — Tue, 15 Jan 2008 08:49:41 GMT

good

check the below code and than change your code accordingly.

Types: begin of ty_mesg_mkpf,

mblnr type mseg-mblnr,

matnr type mseg-matnr,

werks type mseg-werks,

charg type mseg-charg,

lifnr type mseg-lifnr,

bualt type mseg-bualt,

erfmg type mseg-erfmg,

ebeln type mseg-ebeln,

ebelp type mseg-ebelp,

budat type mkpf-budat,

end of ty_mseg_mkpf,

Begin og ty_mara_makt,

matnr type mara-matnr,

zzshelf_life type mara-zzshelf_life,

maktx type makt-maktx,

end of ty_mara_makt.

Data: it_mseg_mkpf type table of ty_mseg_mkpf,

wa_mseg_mkpf type ty_mseg_mkpf,

it_mara_makt type table of ty_mara_makt,

wa_mara_makt type ty_mara_makt.

SELECT m1~mblnr

m1~matnr

m1~werks

m1~charg

m1~lifnr

m1~bualt

m1~erfmg

m1~ebeln

m1~ebelp

m2~budat

FROM mseg

INTO TABLE it_mseg_mkpf

INNER JOIN mkpf

ON m1_{mblnr EQ m2}mblnr

WHERE m1~matnr IN s_matnr

AND m1~werks IN s_werks

AND m1~lifnr IN s_lifnr

AND m1~bwart EQ p_bwart

AND ( m1_{ebeln NE ' ' AND m1}ebeln IN s_ebeln )

AND m2~budat IN s_budat

if sy-subrc = 0.

Select m3~matnr

m3~zzshelf_life

m4~maktx from mara as m3

INNER JOIN makt as m4

ON m3_{matnr EQ m4}matnr

INTO TABLE it_mara_makt

where m3~mtart IN s_mtart

AND m4~spras EQ sy-langu.

endif.

loop at it_mseg_mkpf into wa_mseg_mkpf.

Move necessary field values from wa_mseg_mkpf to wa_mat_doc.

Read table it_mara_makt into wa_mara_makt with key matnr = wa_mseg_mkpf-matnr.

if sy-subrc = 0.

move remaining field values to wa_mat_doc.

append wa_mat_doc to gt_mat_doc.

clear wa_mat_doc.

endif.

endloop.

thanks

mrutyun^

Re: Nested Select querry

Former Member — Tue, 15 Jan 2008 09:03:06 GMT

Hi chandra,

do one thing make 3 internal table

write one select query using join for 2 tables

and other for one more for the 3 table

copy aall in one internal table

write the output table

thanks

swaroop