topic Re: Need Help in Select Statement in Application Development and Automation Discussions

Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 11:49:21 GMT

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

Re: Need Help in Select Statement

kesavadas_thekkillath — Thu, 05 Nov 2009 11:53:48 GMT

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

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 11:55:05 GMT

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.

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 11:59:52 GMT

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

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 12:00:42 GMT

Dear Keshu Thekkillam

For both the entries sy-subrc is 0.

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 12:06:07 GMT

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.

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 12:06:24 GMT

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

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 12:07:19 GMT

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

Re: Need Help in Select Statement

kesavadas_thekkillath — Thu, 05 Nov 2009 12:12:23 GMT

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

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 12:16:00 GMT

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

Re: Need Help in Select Statement

murat_kaya — Thu, 05 Nov 2009 12:19:17 GMT

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

Re: Need Help in Select Statement

kesavadas_thekkillath — Thu, 05 Nov 2009 12:23:28 GMT

hi vikranth

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

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 12:30:28 GMT

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

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 12:35:30 GMT

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

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 12:37:57 GMT

hi what is OP ?

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 12:57:46 GMT

OP stands for Original Poster as per our SCN language

Re: Need Help in Select Statement

Former Member — Thu, 05 Nov 2009 18:05:15 GMT

Thank you all for the quick response.

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

Thanks again!.