topic Re: Select statement in Application Development and Automation Discussions

Select statement

Former Member — Fri, 22 Sep 2006 09:26:55 GMT

Hi all,

I need to retrieve records from mseg for some materials with respect to the lastest document of that particular material.

for example.

table : mseg

_____________

Doc No. Mat

001 a

002 a

011 b

012 b

____________

i need to retrieve doc 002 and 012 for mat a and b.

Thanks for ur suggestion,

Rajesh.

Re: Select statement

Former Member — Fri, 22 Sep 2006 09:30:57 GMT

Hi,

After retreiving the data from the table, sort the internal table on material document no and material in the descending sequence.

E.g.

sort itab by matnr mblnr descending.

Loop at itab1.

on change of itab-matnr.

read itab with key matnr = itab1-matnr.

endon.

endloop.

P.S. mark all helpful asnwers for points

JLN

Re: Select statement

Former Member — Fri, 22 Sep 2006 09:32:11 GMT

Hi,

Select MBLNR

MATNR

into table itab.

sort ITAB by MBLNR MATNR DESCENDING.

itab1[] = itab[].

LOOP AT ITAB.

AT NEW ITAB-MATNR.

read table itab1 index sy-tabix.

append itab1 into itfinal.

ENDAT.

ENDLOOP.

ITFINAL will have the req. records.

Best regards,

Prashant

Re: Select statement

Former Member — Fri, 22 Sep 2006 09:33:24 GMT

Hi,

You can use aggregate function MAX in the select statement.

Ex. select max ( MBLNR ) into table itab where

...

Hope this helps.

Re: Select statement

Former Member — Fri, 22 Sep 2006 09:40:58 GMT

i think its code help u.:

First sort table on docno & mat

sort T_mseg.

loop at T_mseg.

AT END OF NAME.

WRITE: / T_mseg-Doc, T_mseg-Mat.

ENDAT.

Endloop.

Re: Select statement

Former Member — Fri, 22 Sep 2006 09:41:22 GMT

SELECT MATNR MAX(mblnr) into table itab

from mseg

group by matnr

where matnr in s_matnr.

Itab should have the required data

Re: Select statement

Former Member — Fri, 22 Sep 2006 10:00:21 GMT

Thanks Anurag and everybody for your help.

Rajesh.