topic Re: select single!!!!!! in Application Development and Automation Discussions

select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:08:28 GMT

hi all,

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

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.

i just wanted to pick all records from that line of view.

regards

Deepak

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:15:14 GMT

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

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:18:50 GMT

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

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:19:32 GMT

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

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:20:30 GMT

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

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:21:36 GMT

Hi Deepak ,

Try using

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

ENDSELECT.

Regards

Arun

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:26:05 GMT

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

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:28:28 GMT

ITS NOT WORKING ITS ONLY GETTING FIRST RECORD FROM DATABASE.

DEEPAK

Re: select single!!!!!!

Simha_ — Thu, 25 Jan 2007 04:31:31 GMT

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.

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:35:13 GMT

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

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:40:00 GMT

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

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:45:55 GMT

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.

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:46:20 GMT

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

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:51:01 GMT

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

Re: select single!!!!!!

Former Member — Thu, 25 Jan 2007 04:51:51 GMT

hi,

thanx for the soln. problem is solved

regards

deepak