select single!!!!!!

[Former Member]

hi all,

how to select single line from view WB2_V_MKPF_MSEG2 with higest <b>mblnr</b> & <b>mjahr</b> for a particulat <b>material no .</b> & <b>movement type</b> .

i just wanted to pick latest goods recipt document for a particular material no.

i am trying this select query but it is not working.....

SELECT SINGLE max( MBLNR_I ) MAX( MJAHR_I ) Into (mblnr,MJAHR)

FROM WB2_V_MKPF_MSEG2

WHERE MATNR_I = MATNR AND

BWART_I = '101' AND

MJAHR_I = '2006'.

GROUP BY MBLNR_I MJAHR_I.

<b>i just wanted to pick all records from that line of view.</b>

regards

Deepak

[Former Member]

Hi,

Is MATNR a local variable?

Check if MATNR is prefixed with '0'. The data in the standard tables is stored with leading zeroes.

If there is no zero, use CONVERSION_EXIT_ALPHA_INPUT to prefix zeroes and try.

Regards

Subramanian

[Former Member]

hi

no matnr in not hard coded i m using this query in loop & matnr value keeps on changing .....

LOOP AT MATRIX.

MATNR = MATRIX-IDNRK.

SELECT SINGLE max( MBLNR_I ) MAX( MJAHR_I ) Into (mblnr,MJAHR)

FROM WB2_V_MKPF_MSEG2

WHERE MATNR_I = MATNR AND

BWART_I = '101' AND

MJAHR_I = '2006'.

GROUP BY MBLNR_I MJAHR_I.

ENDLOOP.

regards

deepak

[Former Member]

Hi,

Try like this:

SELECT max( MBLNR_I ) MAX( MJAHR_I ) Into (mblnr,MJAHR)

FROM WB2_V_MKPF_MSEG2

WHERE MATNR_I = MATNR AND

BWART_I = '101' AND

MJAHR_I = '2006'.

GROUP BY MBLNR_I MJAHR_I.

Regards

Subramanian

[Former Member]

SELECT max( MBLNR_I ) MAX( MJAHR_I ) Into (mblnr,MJAHR)

FROM WB2_V_MKPF_MSEG2

WHERE MATNR_I = MATNR AND

BWART_I = '101' AND

MJAHR_I = '2006'

GROUP BY MBLNR_I MJAHR_I.

endselect.

now your mblnr and mjahr contains the maxm values

regards

shiba dutta

[Former Member]

HI

ITS NOT WORKING ITS ONLY GETTING FIRST RECORD FROM DATABASE.

DEEPAK

[Simha_]

Hi,

Select Single gives onlyy one recordd on satisfying the conditions u give in the query..

if u want al the items for that material go for

select * or select whatever the fields u want...

Cheers,

Simha.

[Former Member]

Hi Deepak ,

Try this

SELECT MBLNR_I MJAHR_I into ( MBLNR , MJAHR )

UP TO 1 ROWS

FROM FROM WB2_V_MKPF_MSEG2

WHERE MATNR_I = MATNR AND

BWART_I = '101' AND

MJAHR_I = '2006'

ORDER BY MBLNR_I DESCENDING.

Regards

Arun

[Former Member]

Hi,

Try the below code -

SELECT mblnr_i mjahr_i

INTO (mblnr,mjahr)

FROM wb2_v_mkpf_mseg2

UP TO 1 ROWS

WHERE matnr_i = matnr AND

bwart_i = '101' AND

mjahr_i = '2006'

ORDER BY mblnr_i DESCENDING mjahr_i DESCENDING.

IF sy-subrc EQ 0.

• Write the logic

ENDIF.

ENDSELECT.

Reward points if found useful...!

Cheers

Abhishek

[Former Member]

hi,

thanx for the soln. problem is solved

regards

deepak

[Former Member]

Hi Deepak ,

Try using

SELECT .. UP TO 1 ROWS into ...... ORDER BY <FIELD NAME> DESCENDING.

ENDSELECT.

Regards

Arun

[Former Member]

Hi,

Your code is fine for getting a single data.. since u used select single...

use select - endselect to get all data..

Also (MJAHR_I = '2006'.) , remove period at the end..

Just try this piece of code..

SELECT MAX( mblnr_i ) MAX( mjahr_i ) INTO (mblnr, mjahr)

FROM wb2_v_mkpf_mseg2

WHERE matnr_i = matnr AND

bwart_i = '101' AND

mjahr_i = '2006'

GROUP BY mblnr_i mjahr_i.

WRITE: / mblnr , mjahr.

ENDSELECT.

Thanks...

Rajiv

[Former Member]

Deepak,

First

SELECT max( MBLNR_I ) MAX( MJAHR_I ) Into (mblnr,MJAHR)

FROM WB2_V_MKPF_MSEG2

WHERE MATNR_I = MATNR AND

BWART_I = '101' AND

MJAHR_I = '2006'

GROUP BY MBLNR_I MJAHR_I.

endselect.

Second

select single * from WB2_V_MKPF_MSEG2 into table WB2_V_MKPF_MSEG2

where MBLNR_I eq mblnr and

MJAHR_I ew MJAHR.

Now it will work.

Pls. mark for all useful answers.

[Former Member]

one thing you can do

you are fetching only max value that is there are no fields other that aggregate fn so you can ommit group by there

and do like that.

SELECT max( MBLNR_I ) MAX( MJAHR_I ) Into (mblnr,MJAHR)

FROM WB2_V_MKPF_MSEG2

WHERE MATNR_I = MATNR AND

BWART_I = '101' AND

MJAHR_I = '2006'.

regards

shiba dutta

[Former Member]

Hey Deepak,

Since I do not have the view wb2_v_mkpf_mseg2 in the system that I am working, I am not aware of the exact fields in it.

Try the below code -

SELECT mblnr_i mjahr_i matnr_i

INTO (wa-mblnr,wa-mjahr,wa-matnr)

FROM wb2_v_mkpf_mseg2

WHERE matnr_i = matnr AND

bwart_i = '101' AND

mjahr_i = '2006'

ORDER BY matnr_i DESCENDING mblnr_i DESCENDING mjahr_i DESCENDING.

IF sy-subrc EQ 0.

IF sy-tabix EQ 1.

APPEND wa TO itab.

MOVE wa TO wa1.

ENDIF.

IF wa-matnr NE wa1-matnr.

APPEND wa TO itab.

MOVE wa TO wa1.

ENDIF.

ENDSELECT.

Reward points if found useful...!

Cheers

Abhishek