Select Query Problem

[Former Member]

Hi,

In my report output is showing like this...

PO Number...........Line..............103 Doc................105 Document

4500043018...........10...........5000509351...........5000509385

4500043018...........10...........5000521252...........5000509385

But it should be...

PO Number...........Line..............103 Doc................105 Document

4500043018...........10...........5000509351...........5000509385

4500043018...........10...........5000521252...........5000522039

Please look at 2nd row...where 105 document number is 5000522039.

My select query is..

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

SELECT a_{aedat a}lifnr b_{ebeln b}ebelp b_{gjahr b}belnr b_{bwart b}budat b_{bamng b}xblnr b_{lfgja b}lfbnr b_{lfpos b}matnr

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ekko AS a

INNER JOIN ekbe AS b ON a_{ebeln = b}ebeln

WHERE b~ebeln IN ebeln

AND b~ebelp IN ebelp

AND b~bwart EQ '103'

AND a~lifnr IN lifnr

AND b~budat IN budat.

IF sy-subrc <> 0.

MESSAGE 'DATA NOT FOUND.' TYPE 'I'(001).

ENDIF.

SORT itab BY ebeln ebelp.

LOOP AT itab.

SELECT SINGLE belnr budat menge dmbtr waers xblnr FROM ekbe INTO (itab-belnr1, itab-budat1, itab-menge1, itab-dmbtr1, itab-waers1, itab-xblnr1)

WHERE lfbnr = itab-lfbnr AND lfpos = itab-lfpos AND bwart EQ '105' AND ebeln = itab-ebeln AND ebelp = itab-ebelp.

MODIFY itab INDEX sy-tabix TRANSPORTING belnr1 budat1 menge1 dmbtr1 waers1 xblnr1 .

ENDLOOP.

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

In this case, lfbnr = itab-lfbnr has no value else in ITAB or EKBE table...

It is showing correct value in 1st row...but not clearing the itab in 2nd row for 105 document number...

What should be do..

[Former Member]

Hi,

From first query you are getting ebeln, ebelp, gjahr, belnr, bwart, budat, bamng, xblnr, lfgja, lfbnr, lfpos and matnr from EKBE table.

And according to you, you are not getting any values in field lfbnr. So. you can use only

LOOP AT itab.

SELECT SINGLE belnr budat menge dmbtr waers xblnr FROM ekbe INTO (itab-belnr1, itab-budat1, itab-menge1, itab-dmbtr1, itab-waers1, itab-xblnr1)

WHERE

ebeln = itab-ebeln AND

ebelp = itab-ebelp AND

gjahr = itab-gjahr AND

belnr = itab-belnr AND

bwart EQ '105' AND

lfpos = itab-lfpos.

MODIFY itab INDEX sy-tabix TRANSPORTING belnr1 budat1 menge1 dmbtr1 waers1 xblnr1 .

ENDLOOP.

Try this out. Here we will providing maximum key fields to EKBE table.

Thanks,

Archana

[Former Member]

as per ur suggested code...no value in 105 document number field...

[Former Member]

Hi,

Well, i think this is only problem with your where condition. You will have to check it with trial and method i suppose. So, before executing query, you can just try in se11 by passing just ebeln, ebelp and bwart if it is retrieving corrrect values. So, accordingly you can modify your select query as well.

Thanks,

Archana

[Former Member]

LOOP AT itab.

SELECT SINGLE belnr budat menge dmbtr waers xblnr FROM ekbe INTO (itab-belnr1, itab-budat1, itab-menge1, itab-dmbtr1, itab-waers1, itab-xblnr1)

WHERE

ebeln = itab-ebeln AND

ebelp = itab-ebelp AND

gjahr = itab-gjahr AND (Remove BELNR in Archana's suggestion; BELNR will be unique for each doc)

bwart EQ '105' AND

lfpos = itab-lfpos.

MODIFY itab INDEX sy-tabix TRANSPORTING belnr1 budat1 menge1 dmbtr1 waers1 xblnr1 .

ENDLOOP.

Hope this works.

One thing which I didn't understand is do you need to fire a select inside loop for this requirement?

Regards,

Ganga

[Former Member]

Please correct you query

You are trying to query the same table ekbe again...

Why is it required.. You can fetch the values from the first query itself..!

[Former Member]

after amend this one, same output is showing...

PO Number...........Line..............103 Doc................105 Document

4500043018...........10...........5000509351...........5000509385

4500043018...........10...........5000521252...........5000509385

[Former Member]

Hi,

Use the below logic.

As per your requirement, you need materials for which 103 movement type is done and 105 is NOT done.

So after fetching 103 and 105 docs from EKBE, you have to filter out the materials for which 103 is done and 105 NOT done. For that use the below logic.

SELECT a~aedat a~lifnr b~ebeln b~ebelp b~gjahr b~belnr b~bwart b~budat b~bamng b~xblnr b~lfgja b~lfbnr b~lfpos b~matnr
INTO CORRESPONDING FIELDS OF TABLE itab
FROM ekko AS a
INNER JOIN ekbe AS b ON a~ebeln = b~ebeln
WHERE b~ebeln IN ebeln
AND b~ebelp IN ebelp
AND b~bwart in ( '103', '105')
AND a~lifnr IN lifnr
AND b~budat IN budat.

T_EKBE_103 = T_EKBE.
Delete T_EKBE_103 where BWART = '105'.

T_EKBE_105 = T_EKBE.
Delete T_EKBE_105 where BWART = '103'.

Loop at T_EKBE_103.
Read T_EKBE_105 with key EBELN = T_EKBE_103-EBELN EBELP = T_EKBE_103-EBELP.
If SY-SUBRC is not equal to 0.
Append T_EKBE_103 to T_FINAL.
Endif.
Endloop.

After this T_FINAL consists of materials for which 103 is posted and 105 NOT posted.

There is already a thread posted by you for this requirement for which I gave you the logic as below.

[]

Regards,

Ganga

[Former Member]

Thanks dear...

as per ur suggested code..

DATA : t_ekbe TYPE ekbe.

DATA : t_ekbe_103 TYPE ekbe OCCURS 0 WITH HEADER LINE.

DATA : t_ekbe_105 TYPE ekbe OCCURS 0 WITH HEADER LINE.

SELECT a_{aedat a}lifnr b_{ebeln b}ebelp b_{gjahr b}belnr b_{bwart b}budat b_{bamng b}xblnr b_{lfgja b}lfbnr b_{lfpos b}matnr

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ekko AS a

INNER JOIN ekbe AS b ON a_{ebeln = b}ebeln

WHERE b~ebeln IN ebeln

AND b~ebelp IN ebelp

AND b~bwart IN ('103', '105')

AND a~lifnr IN lifnr

AND b~budat IN budat.

IF sy-subrc <> 0.

MESSAGE 'DATA NOT FOUND.' TYPE 'I'(001).

ENDIF.

SORT itab BY ebeln ebelp.

t_ekbe_103 = t_ekbe.

DELETE t_ekbe_103 WHERE bwart = '105'.

t_ekbe_105 = t_ekbe.

DELETE t_ekbe_105 WHERE bwart = '103'.

LOOP AT t_ekbe_103.

READ t_ekbe_105 WITH KEY ebeln = t_ekbe_103-ebeln ebelp = t_ekbe_103-ebelp.

IF sy-subrc IS NOT EQUAL to 0.

APPEND t_ekbe_103 TO t_final.

ENDIF.

ENDLOOP.

Error is showing on READ Statement "Unable to Interpret "t_ekbe_105". Possible Cause: Incorrect spelling or comma error"

what should be the problem here...

[Former Member]

You should use Read table T_EKBE_105...etc...

[Former Member]

Hi,

SELECT a~aedat a~lifnr b~ebeln b~ebelp b~gjahr b~belnr b~bwart b~budat b~bamng b~xblnr b~lfgja b~lfbnr b~lfpos b~matnr
INTO CORRESPONDING FIELDS OF TABLE itab
FROM ekko AS a
INNER JOIN ekbe AS b ON a~ebeln = b~ebeln
WHERE b~ebeln IN ebeln
AND b~ebelp IN ebelp
AND ( b~bwart EQ '103' or b~bwart Eq '105' ) " Add this 
AND a~lifnr IN lifnr" Avoid mulitple Call to same DB table this will improve performance
AND b~budat IN budat.

Cheerz

Ram

[Former Member]

Now output is showing correct but ...

PO Number........Line.......103 Doc. No.........105 Doc. No.........

4500043018........10........5000509351

4500043018........10........5000509385

4500043018........10........5000521252

4500043018........10........5000522039

Here in below 105 document number, no records shows...because 105 document number shows under 103 document number..

How can we query because we want 105 doc. number under itself heading...not under 103 document number.

[Former Member]

Hi ABAP,

PO Number........Line....Mtype...103 Doc. No.........105 Doc. No.........

4500043018........10......103....5000509351

4500043018........10......105....5000509385

4500043018........10.......103...5000521252

4500043018........10.......105...5000522039

here also add 1 more colom movement type....

please see..

data : wa type itab.

wa1 type itab.

take 2 more itab with same structure..let say it_itab, itab2.

itab2[] = itab[].

loop at itab into wa.

read table itab2 into wa1 with key ebeln = po and mtype = '105' and docno ne wa-docno.

if sy-subrc = 0.

wa-105doc = wa1-105doc.

append wa to it_itab.

delete itab where ebeln = po and mtype = 105 and docno = wa-docno.

endif.

endloop.

[Former Member]

First i declare..

DATA : wa TYPE itab,

wa1 TYPE itab.

and take 2 more itab with same structure..let say it_itab, itab2.

and in loop at itab...after 1st select query..

LOOP AT itab INTO wa.

READ TABLE itab2 INTO wa1 WITH KEY ebeln = wa-ebeln and bwart = '105' and belnr ne wa-belnr.

IF sy-subrc = 0.

wa-105doc = wa1-105doc.

APPEND wa TO it_itab.

DELETE itab WHERE ebeln = wa-ebeln AND bwart = '105' AND belnr ne wa-belnr.

ENDIF.

ENDLOOP.

there error showing like...on LOOP AT itab INTO wa.

A line of "ITAB" and "WA" are not mutually convertible. In a Unicode program, "ITAB" must have the same structure layout as "WA", irrespective of the length of a Unicode character. Unicode character.

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

plz help...

[SuhaSaha]

Hello,

The declaration for work ares should be like:

DATA : wa LIKE LINE OF itab.

This should correct your compilation error.

BR,

Suhas

[Former Member]

hi

Check itab and wa structres ,This error is mismatch of structure.

data:itab type table of ty_tab,

wa like line of itab.

READ TABLE itab2 INTO wa1 WITH KEY ebeln  =  wa-ebeln 
                                                                bwart  =  '105'
                                                                belnr   < > (NotEqual) wa-belnr1.

Not Equal <, >

Edited by: dharma raj on Feb 23, 2010 1:11 PM

[Former Member]

Now it is showing error "= ..." expected after "BELNR"

READ TABLE itab2 INTO wa1 WITH KEY ebeln = wa-ebeln bwart = '105' belnr ne wa-belnr1.

plz help..

[SuhaSaha]

This is because READ TABLE does not support NE operator with the KEY addition.

Do not take it in the wrong sense, but i think you should get your basics clear.

Cheers,

Suhas

[Former Member]

Hi,

Inside loop at you have used select single which will only retrieve one value. 1 ebeln can have more than one document number and the where condition remains same for all these values. So its retrieving the same value. try the modify statement inside select...endselect instead of select single.

Hope this helps

[Former Member]

Please try this way and see,

SELECT a_{aedat a}lifnr b_{ebeln b}ebelp b_{gjahr b}belnr b_{bwart b}budat b_{bamng b}xblnr b_{lfgja b}lfbnr b_{lfpos b}matnr

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ekko AS a

INNER JOIN ekbe AS b ON a_{ebeln = b}ebeln

WHERE b~ebeln IN ebeln

AND b~ebelp IN ebelp

• "AND b~bwart EQ '103' + " comment it here+

AND a~lifnr IN lifnr

AND b~budat IN budat.

IF sy-subrc 0.

MESSAGE 'DATA NOT FOUND.' TYPE 'I'(001).

ENDIF.

SORT itab BY ebeln ebelp.

LOOP AT itab.

if itab-bwart = '103' .

move gwa_itab to gwa_itab_final.

elseif itab-bwart = '105'.

if gwa_itab_final-lfbnr = gwa_itab-lfbnr

and gwa_itab_final-lfpos = gwa_itab-lfpos .

move gwa_itab-belnr to gwa_itab_final-belnr1.

move gwa_itab-budat to gwa_itab_final-budat1.

move gwa_itab-menge to gwa_itab_final-menge1.

move gwa_itab-dmbtr to gwa_itab_final-dmbtr1.

move gwa_itab-waers to gwa_itab_final-waers1.

move gwa_itab-xblnr to gwa_itab_final-xblnr1.

append gwa_itab_final to gt_final.

endif.

else. "if any other bwart set it to space to remove it later from the internal table.

itab-bwart = ''.

endif.

MODIFY itab INDEX sy-tabix TRANSPORTING bwart belnr1 budat1 menge1 dmbtr1 waers1 xblnr1 .

ENDLOOP.

delete itab where bwart = space.

the gt_itab_final shud be ur final internal table.

[Former Member]

All replies having the same optput..

PO Number.......Line.....103 Doc. No.........105 Doc. No.........

4500043018........10........5000509351

4500043018........10........5000509385

4500043018........10........5000521252

4500043018........10........5000522039

But i want 105 document number under the 105 Doc. no. column.

[Former Member]

When i saw in debugging, no value moving under 105 Doc. No.

It's showing under 103 Doc. number according to 105 movement ...

[Former Member]

HI,

Take a Temp Internal Table with 2 new fields other than your existing table

this contains 103Doc and 105Doc. Clear. Remaining fields are same as your original one.

"If BWART is  not there, First you add BWART field in your original Internal table and also in the Select Query 

loop at itab.
move-corresponding itab to new_tab.
if itab-bwart = '103'. " For 103 Movement type
move itab-document to new_itab-103doc.
elseif itab-bwart = '105'.
move itab-document to new_itab-105doc.
endif
endloop.

if you find this tough, take 2 temp tables for each 103 and 105 movement type

loop at itab where bwart = 103.
move-corresponding itab to itab_103.
append itab_103. " Similarly for 105.
endloop.

take a final internal table.

loop at itab.
read table itab_103 with key    itab-common field  " This 
                                                  bwart = 103. " and pass the required details to final table
Read table itab_105 with key bwart = 105" and pass the required detials to final tbal

endloop.

Cheerz

Ram