topic Re: problem in select query in Application Development and Automation Discussions

problem in select query

Former Member — Fri, 20 Feb 2009 05:01:11 GMT

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.

Re: problem in select query

Former Member — Fri, 20 Feb 2009 05:06:17 GMT

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

Re: problem in select query

Former Member — Fri, 20 Feb 2009 05:06:45 GMT

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'.

Re: problem in select query

Former Member — Fri, 20 Feb 2009 05:09:15 GMT

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

Re: problem in select query

Former Member — Fri, 20 Feb 2009 05:19:12 GMT

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.

Re: problem in select query

Former Member — Fri, 20 Feb 2009 05:29:56 GMT

Hi:

add group by only

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

GROUP BY mjahr.

regards

Shashi

Re: problem in select query

Former Member — Fri, 20 Feb 2009 05:34:53 GMT

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.

Re: problem in select query

former_member222860 — Fri, 20 Feb 2009 05:35:14 GMT

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

Re: problem in select query

Former Member — Fri, 20 Feb 2009 07:23:05 GMT

Hi , wght is the problem?

Re: problem in select query

Former Member — Fri, 20 Feb 2009 07:29:52 GMT

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

Re: problem in select query

Former Member — Fri, 20 Feb 2009 11:12:07 GMT

Thank you all