Need Help in Select Statement

[Former Member]

Dear gurus

Below is my select statement. Im having problem with statement.

the problem is that the table vbfa have some entries like this

800	1400004654	10	3900012235	10	M	424,672.68
800	1400004654	10	3900012257	10	M	137,093.36
800	1400004654	20	3900012311	20	M	214,257.36
800	1400004654	30	3900012412	30	M	81,248.44
800	1400004654	30	3900012901	30	M	166,920.68

When the select statement is run it does not fetch the data of LINE number 2 and Line number 5

LOOP AT itab1.
    SELECT SINGLE * INTO CORRESPONDING FIELDS OF wa_vbfa
    FROM vbfa
    WHERE vbelv = itab1-vgbel
    AND posnn = itab1-vgpos
    AND vbtyp_n = 'M'.


    SELECT SINGLE * INTO CORRESPONDING FIELDS OF wa_vbrk
      FROM vbrk
      WHERE vbeln = wa_vbfa-vbeln
      AND vbtyp = 'M'.

    IF sy-subrc = 0.
        itab1-lfimg = wa_vbfa-rfmng.
       itab1-old_price = wa_vbfa-rfwrt.
       MODIFY itab1.
       ELSE.
    ENDIF.

  ENDLOOP.

Please Help

Regards

Saad Nisar

[kesavadas_thekkillath]

check sy-subrc values of both the queries for those entries.

[Former Member]

Dear Keshu Thekkillam

For both the entries sy-subrc is 0.

[Former Member]

Hi Saad,

In ITAB1 do you have the fourth field of below data.

800 1400004654 10 3900012235 10 M 424,672.68

800 1400004654 10 3900012257 10 M 137,093.36

800 1400004654 20 3900012311 20 M 214,257.36

800 1400004654 30 3900012412 30 M 81,248.44

800 1400004654 30 3900012901 30 M 166,920.68

if yes give that also in the select query.

[Former Member]

Hi,

If you use SELECT SINGLE the system only get the first found record, that's why it didn't get the next records you want.

If you really want to get all the records according your selction criteria, then you should change your statement by using SELECT .. INTO TABLE ..., and in the next selection statement (vbrk) you should change to SELECT ... INTO TABLE ... FOR ALL ENTRIES IN ...

This way you get all the records you want.

Hope it helps,

Chang

[Former Member]

Hi,

Select single will only give you the first record for the where clause. In your case, i see two options for doing this.

1) select all records using into table clause in the select statement and then loop this internal table inside the loop for itab1.

2) use select..endselect inside the loop for itab1.

both of these will result in performance overhead..so u need to analyze ST05 trace to check the most effective solution.

regards,

nilesh.

[Former Member]

Hello

800	_1400004654	10_	3900012235	10	M	424,672.68
800	_1400004654	10_	3900012257	10	M	137,093.36

SELECT SINGLE * INTO CORRESPONDING FIELDS OF wa_vbfa
    FROM vbfa
    WHERE vbelv = itab1-vgbel
    AND posnn = itab1-vgpos
    AND vbtyp_n = 'M'.

In VBFA 1and 2 records have same VBELV and POSNV. So in loop/endloop select only first record.

But this records have different values VBELN.

So add in condition field VBELN and add VBELN into itab1:

SELECT SINGLE * INTO CORRESPONDING FIELDS OF wa_vbfa
    FROM vbfa
    WHERE vbelv = itab1-vgbel
    AND posnn = itab1-vgpos
    AND vbeln = itab1-vbeln
    AND vbtyp_n = 'M'.

[Former Member]

Hello Saad,

The reason why you are not getting the 2nd and 5th entries is that, the where conditions vbelv, posnn and vbtyp_n matches for both the 1st and 2nd record where select will pick only the 1st record. The same way for 4th and 5th record. so its picking only 4th.

So to avoid this add even vbeln in the where condition of the select query

LOOP AT itab1.
    SELECT SINGLE * INTO CORRESPONDING FIELDS OF wa_vbfa
    FROM vbfa
    WHERE vbelv = itab1-vgbel
    AND posnn = itab1-vgpos
   AND vbeln = itab1-field           " Add the corresponding field here
    AND vbtyp_n = 'M'.
 
 
    SELECT SINGLE * INTO CORRESPONDING FIELDS OF wa_vbrk
      FROM vbrk
      WHERE vbeln = wa_vbfa-vbeln
      AND vbtyp = 'M'.
 
    IF sy-subrc = 0.
        itab1-lfimg = wa_vbfa-rfmng.
       itab1-old_price = wa_vbfa-rfwrt.
       MODIFY itab1.
       ELSE.
    ENDIF.
 
  ENDLOOP.

Vikranth

[kesavadas_thekkillath]

select single only selects single record...hope you got it now.

if itab1[] is not initial.
sort itab1 by vgbel vgpos.
    SELECT * INTO CORRESPONDING FIELDS OF it_vbfa
    FROM vbfa for all entries in itab1
    WHERE vbelv = itab1-vgbel
    AND posnn = itab1-vgpos
    AND vbtyp_n = 'M'.
endif.
if it_vbfa[] is not initial.
sort it_vbfa by vbeln.
   SELECT  * INTO CORRESPONDING FIELDS OF it_vbrk
      FROM vbrk for all entries in it_vbfa
      WHERE vbeln = it_vbfa-vbeln
      AND vbtyp = 'M'.
endif.

now loop,read and move

[Former Member]

Hello Keshu,

Since the OP is using the select within the LOOP (not advisable though), select single will work for multiple records for each iteration

Vikranth

[kesavadas_thekkillath]

hi vikranth

what if the itab consists of only one entry and the table has two entries for that ?

[Former Member]

Hello Keshu,

Yes that's right. the OP should either use all the distinct fields in the where clause or use select into table itab and then pick which record is required

Vikranth

[Former Member]

hi what is OP ?

[Former Member]

OP stands for Original Poster as per our SCN language

[murat_kaya]

Hi,

this is probably because of the select single statement. when you try to fetch line 2, the select statement finds line 1 because the select conditions aren't explicit, line 1 and 2 has common values for your select options and select single finds the first line that meets the selection criteria. it is also does the same for line 5 because it has common values with line 4. if you make your selection criteria more explicit, your problem should be solved.

regards,

Murat Kaya

[Former Member]

Instead of using Select * statement into corresponding fields

use all fields so it will be able to get all distinct entries

SELECT VBELV

POSNV

VBELN

POSNN

VBTYP_N

RFMNG

FROM VBFA

INTO TABLE I_VBFA

FOR ALL ENTRIES IN I_ITAB

WHERE

CONDITION

[Former Member]

Thank you all for the quick response.

Every answer was very supportive and its very difficult to give rewards points 😄

Thanks again!.