problem in select query

[Former Member]

hi,

LOOP AT ITAB.

IF S_KUNNR IS not INITIAL.

SELECT MAX( MBLNR ) FROM MSEG INTO MBLNR

WHERE MATNR EQ ITAB-MATNR

AND KUNNR EQ ITAB-KUNNR

AND BWART eq '951'.

else.

SELECT MAX( MBLNR ) FROM MSEG INTO MBLNR

WHERE MATNR EQ ITAB-MATNR

AND KUNNR ne ' '

and sobkz eq 'V'

AND BWART eq '951'.

endif.

******************************************************************************

top part is working

from that query i need mjahr also from mseg.

LOOP AT ITAB.

IF S_KUNNR IS not INITIAL.

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO MBLNR mjahr

WHERE MATNR EQ ITAB-MATNR

AND KUNNR EQ ITAB-KUNNR

AND BWART eq '951'.

else.

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO MBLNR mjahr

WHERE MATNR EQ ITAB-MATNR

AND KUNNR ne ' '

and sobkz eq 'V'

AND BWART eq '951'.

endif.

after adding mjahr in select query it is not working.from the statement i need both (max)mblnr

and mjahr.how to write query.

[Former Member]

Hi Bathrinath

Can you specify what error it is giving.....

and use the same order in which fields are there in MSEG Table

i mean if in MSEG Table mjahr comes before than mblnr

then use

select mjahr max(mblnr) from mseg

and also check

whether u have defined internal table perfectly in the same order of fields in table

Hope it helps

Regards

Vikas C

[Former Member]

Write select Query as below INTO (MBLNR, mjahr)

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO (MBLNR, mjahr)
WHERE MATNR EQ ITAB-MATNR
AND KUNNR EQ ITAB-KUNNR
AND BWART eq '951'.


else.

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO (MBLNR, mjahr)
WHERE MATNR EQ ITAB-MATNR
AND KUNNR ne ' '
and sobkz eq 'V'
AND BWART eq '951'.

[Former Member]

HI,

In Select Query you can use Only one with with MAX Function.

1) Get the data into internal table.

2) Sort table on the field MBLNR first and first value in the itab will be max MBLNR

3) Sort table on the field mjahr after that and first value in the itab will be max mjahr

Hope this resolve your issue.

Regards,

Gurpreet

[Former Member]

Try to declare like this

data: begin of itab occurs 0,

pernr type persno,

nachn type nachn,

end of itab.

SELECT MAX( pernr ) nachn FROM pa0002 INTO itab

GROUP BY nachn.

ENDSELECT.

[Former Member]

Hi:

add group by only

like

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO (MBLNR, mjahr)

GROUP BY mjahr.

regards

Shashi

[Former Member]

Hi,

Since you are looping at the table ITAB take all the entries into a new internal table ITAB2.

1. After you end the loop ,sort the table ITAB2 in the descending order.

2. Get the first record from ITAB2 by reading the table ITAB2 with index 1.

Read table ITAB2 into WA_ITAB2 with index 1.

MAX_MBLNR(Maximum value of material doc) = WA_ITAB2-MBLNR.

3. This will give you the Maximum of all the MBLNRs available.

Please make sure that the Fields in the table ITAB2 are of type MBLNR and MJAHR respectively.

Regards,

Naveen Kumar Pulluri.

[former_member222860]

Check this

tables: mseg.
data: mblnr like mseg-mblnr,
      mjahr like mseg-mjahr.

SELECT MAX( MBLNR ) mseg~mjahr FROM MSEG INTO (MBLNR, mjahr)
WHERE MATNR EQ ITAB-MATNR
AND KUNNR EQ ITAB-KUNNR
AND BWART eq '951'
GROUP BY mseg~mjahr..

WRITE:/ MBLNR, MJAHR.
ENDSELECT.

Pl. don't duplicate the threads

thanks\

Mahesh

[Former Member]

Hi , wght is the problem?

[Former Member]

Hi Bathrinath,

this may help.....

data: w_mblnr type mseg-mblnr,

w_mjahr type mseg-mjahr.

LOOP AT ITAB.

IF S_KUNNR IS not INITIAL.

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO ( w_MBLNR , w_mjahr )

WHERE MATNR EQ ITAB-MATNR

AND KUNNR EQ ITAB-KUNNR

AND BWART eq '951'.

else.

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO (w_MBLNR ,w_mjahr)

WHERE MATNR EQ ITAB-MATNR

AND KUNNR ne ' '

and sobkz eq 'V'

AND BWART eq '951'.

endif.

Regards,

Mdi.Deeba

[Former Member]

Thank you all