Select statement taking much time.......

[kiran_k8]

Hi,

IF NOT i601[] is initial.

select vbelv

posnv

vbeln

posnn

vbtyp_v

matnr

from vbfa into table ivbfa

FOR ALL ENTRIES IN i601

where vbeln = i601-mblnr and

posnn = i601-zeile2 and

vbtyp_v = 'J'.

select vbeln

matnr

werks

lgort

vgbel

vgpos

mwsbp

from vbrp into table ivbrp

FOR ALL ENTRIES IN ivbfa

where vgbel = ivbfa-vbelv and

vgpos = ivbfa-posnv and

vgtyp = 'J' and

werks IN werks.

CLEAR i601.

FREE i601.

ENDIF.

At the above highlighted select statement it is getting stucked up.I was not able to figure out the reason.No loops nothing but still is not moving from the second select statement,quite a long time it is taking.Can any one here throw some light.By the way none of the fields in the where clause of the 2nd select are primary keys.

Thanks,

K.Kiran.

[Former Member]

Hi,

After first select statement u need to check the table

ivbfa is initial or not.

then only u need to write the second select statement.

if u don't check the table ivbfa then the second select statement fetches all records if there are no entries in the table ivbfa.

This is also because VBRP is the item table. it contains many entries. for each header record u may have many item records so generally it takes time to fetch all the corresponding entries .

reward if helpful

raam

[kiran_k8]

Hi,

IVBFA is having records but still it getting stucked up there.

K.Kiran.

[Former Member]

check the duplicate records

[Former Member]

Hi,

In second table you are tring to extract the records without passing the primary key values...

Any how you have values vbeln ,posnr in internal table i601.So pass those values to VBRP.

IF NOT i601[] is initial.

select vbelv

posnv

vbeln

posnn

vbtyp_v

matnr

from vbfa into table ivbfa

FOR ALL ENTRIES IN i601

where vbeln = i601-mblnr and

posnn = i601-zeile2 and

vbtyp_v = 'J'.

IF NOT ivbfa[] IS INITIAL

select vbeln

matnr

werks

lgort

vgbel

vgpos

mwsbp

from vbrp into table ivbrp

FOR ALL ENTRIES IN ivbfa

where *vbeln = ivbfa-vbeln AND *

posnr = ivbfa-posnv AND

vgbel = ivbfa-vbelv and

vgpos = ivbfa-posnv and

vgtyp = 'J' and

werks IN werks.

ENDIF.

CLEAR i601.

FREE i601.

ENDIF.

Now check your program.

Pls. reward if useful.....

[kiran_k8]

Murali,

VBELN and POSNR if I use in the where clause I am not getting the Sales Invoice No,so I can't use vbeln and posnr in the where clause.

As suggested I will check if ivbfa[] is not initial .

But here I am debugging in the production,ivbfa is having records,so will checking for ivbfa is not initial make any difference.

If IVBFA is not having records then it is fine,but here IVBFA is having records but still it is taking time.Should I create an index on those fields in the where clause ?

Thanks,

K.Kiran.

[kiran_k8]

Hi,

Kindly Opine.

Thanks,

K.Kiran.

[Former Member]

Hi,

Its always a good practice to use sy-subrc after a select statement.

So after the first statement just put the below statement,

if sy-subrc = 0. and then use the second select statement.

Hope this solves the problem.

Pls reward if useful.

Thanks,

Sirisha.

[Former Member]

Hi,

In the beginning of 2nd select statement

Check

IF NOT ivbfa[] is initial.

In the where condition you have written werks IN werks.

Check That also

And if possible try to use primary key values in where condition.

Regards

Sandipan

[Former Member]

You can try to use Inner Join for this query like,

IF NOT i601[] IS INITIAL.

SELECT

vbfa~vbelv

vbfa~posnv

vbfa~vbeln

vbfa~posnn

vbfa~vbtyp_v

vbfa~matnr

vbrp~werks

vbrp~lgort

vbrp~vgbel

vbrp~vgpos

vbrp~mwsbp

INTO corresponding fields of table TABLE <itab>

FROM vbfa

INNER JOIN vbrp

ON vbfa_{vbelv = vbrp}vgbel AND

vbfa_{posnv = vbrp}vgpos

FOR ALL entries IN i601

WHERE vbfa~vbeln = i601-mblnr AND

vbfa~posnn = i601-zeile2 AND

where VBRP~VBELN = IVBFA-VBELN AND

vbrp~vgtyp = 'J'.

<itab> is an internal table which contains all the(required) fields of both vbrp and vbfa tables.

[Former Member]

hi kiran,

sort i601 by mblnr zeile2.

IF NOT i601[] is initial.

select vbelv

posnv

vbeln

posnn

vbtyp_v

matnr

from vbfa into table ivbfa

FOR ALL ENTRIES IN i601

where vbeln = i601-mblnr and

posnn = i601-zeile2 and

vbtyp_v = 'J'.

endif.

sort ivbfa by vbelv posnv werks.

if not ivbfa[] is initial.

select vbeln

matnr

werks

lgort

vgbel

vgpos

mwsbp

from vbrp into table ivbrp

FOR ALL ENTRIES IN ivbfa

where vgbel = ivbfa-vbelv and

vgpos = ivbfa-posnv and

vgtyp = 'J' and

werks IN werks.

endif.

CLEAR i601.

FREE i601.

regards,

ravi shankar reddy