topic Re: Select Query Problem in Application Development and Automation Discussions

Select Query Problem

Former Member — Tue, 23 Feb 2010 03:39:21 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 03:48:49 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 03:57:37 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 04:10:31 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 04:11:48 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 04:13:16 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 04:16:45 GMT

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

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

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

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 04:24:37 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 04:25:02 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 04:35:34 GMT

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.

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 04:45:03 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 05:03:02 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 05:20:22 GMT

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.

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 05:26:50 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 07:16:56 GMT

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

Re: Select Query Problem

SuhaSaha — Tue, 23 Feb 2010 07:26:03 GMT

Hello,

The declaration for work ares should be like:

DATA : wa LIKE LINE OF itab.

This should correct your compilation error.

BR,

Suhas

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 07:26:24 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 07:35:54 GMT

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

Re: Select Query Problem

Former Member — Tue, 23 Feb 2010 07:49:35 GMT

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.

Re: Select Query Problem

SuhaSaha — Tue, 23 Feb 2010 08:23:58 GMT

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