topic Re: Select statement ........... in Application Development and Automation Discussions

Select statement ...........

Former Member — Mon, 31 Dec 2007 08:51:14 GMT

Hi Experts,

Loop at itab.

SELECT sum( lfimg ) INTO itab-lfimg FROM lips

WHERE vgbel = itab-vbeln AND vgpos = itab-posnr AND

pstyv = 'TAN' AND werks = 'HIP' AND lgort = 'BSR'.

Modify itab.

Endloop.

This query is take long time to execute becaz of i lips table vgbel and vgpos ot an primary key. Pl. guide what to do?

Yusuf

Re: Select statement ...........

Former Member — Mon, 31 Dec 2007 09:01:59 GMT

Hi Yusuf,

Create a secodary index for the table LIPS with the field

vgbel,vgpos,pstyv,werks ,lgort.

Regards]

Debjani

Rewards point if find helpful

Re: Select statement ...........

former_member386202 — Mon, 31 Dec 2007 09:05:16 GMT

Hi,

Dont use select statement within the loop. First select data from table LIPS using FOR ALL ENTRIES then modify table using read statement.

SELECT sum( lfimg ) INTO itab1 FROM lips for all entries in itab

WHERE vgbel = itab-vbeln AND vgpos = itab-posnr AND

pstyv = 'TAN' AND werks = 'HIP' AND lgort = 'BSR'.

loop at itab.

read table itab1 where condition

then pass the value to field

modify itab index transporting field

endloop.

Regards,

PRashant

Re: Select statement ...........

Former Member — Mon, 31 Dec 2007 09:05:34 GMT

Hi Debjani,

I am sorry but i dont know how to creat secondary index. Will u give some example or explain?

Yusuf

Re: Select statement ...........

Former Member — Mon, 31 Dec 2007 09:12:34 GMT

hi,

steps for creating index:

SE11 - LIPS-click on tab INDEX- want to create -press yes and then hope you understand .

regards

debjani

Re: Select statement ...........

Former Member — Mon, 31 Dec 2007 09:15:39 GMT

Hello Yusuf ,

First and important thing don't use Select statment in the loop.

How to resolve you can do with the following way.

types : begin of tp_lips,

vbeln type lips-vbeln,

lfimg type lips-lfimg ,

end of tp_lips.

data : t_lips1 type table of tp_lips with header line,

t_lips2 type table of tp_lips with header line,

SELECT vbeln, lfimg INTO t_lips1 FROM lips

for All entries in itab

WHERE vgbel = itab-vbeln AND vgpos = itab-posnr AND

pstyv = 'TAN' AND werks = 'HIP' AND lgort = 'BSR'.

if sy-subrc = 0.

t_lips2[] = t_lips1[].

endif.

delete adjacents duplicates from t_lips1 comparing vbeln.

loop at t_lips1.

loop at t_lips2 where vbeln = t_lips1-vbeln.

t_lips1-lifmg = t_lips1-lifmg + t_lips2-lifmg.

endloop.

modify t_lips1.

endloop.

refresh t_lips2.

clear t_lips2.

Just Try this it will help you and also the performance will be good.

Regards

Swati Namdeo

Re: Select statement ...........

Former Member — Mon, 31 Dec 2007 09:20:25 GMT

Hi Prasant,

Read table statement will read only one row but in itab1 there are multiple records.

I want a sum of lfimg from itab1. I itab1 there are multiple vgbel and vgpos for single vbel posnr.

Yusuf

Re: Select statement ...........

Former Member — Mon, 31 Dec 2007 09:44:51 GMT

Hi Yusuf,

Normally you should not perform select statements with in a loop. It will consume more time with respect to performance.

And more over do not try performing operations like SUM, COUNT directly at database level. Retrieve the data into an internal table and process at the application server level.

Please try to keep database hit to minimum.

Now coming to your query,

Your code:

Loop at itab.

SELECT sum( lfimg ) INTO itab-lfimg FROM lips

WHERE vgbel = itab-vbeln AND vgpos = itab-posnr AND

pstyv = 'TAN' AND werks = 'HIP' AND lgort = 'BSR'.

Modify itab.

Endloop.

Replace it with this code:

Declare an internal table

DATA:

BEGIN OF wa_lips,

vgbel LIKE lips-vgbel,

vgpos LIKE lips-vgpos,

lfimg LIKE lips-lfimg,

END OF wa_lips,

i_lips LIKE TABLE OF wa_lips INITIAL SIZE 0.

SORT itab BY vbeln posnr.

SELECT vgbel vgpos lfimg

FROM lips

INTO TABLE i_lips

FOR ALL ENTRIES IN itab

WHERE pstyv EQ 'TAN'

AND werks EQ 'HIP'

AND lgort EQ 'BSR'

AND vgbel EQ itab-vbeln

AND vgpos EQ itab-posnr.

IF sy-subrc EQ 0.

Do your operation of calculating the count of lfimg and update it in itab table.

ENDIF.

Ensure you follow the order of database while giving the where condition.

Do not forget to sort the table itab by vbeln and posnr.

Note: SORT itab BY vbeln posnr.

Award points, if found useful to you...

Have a great day and successful new year ahead!

Thanks,

Ananth

Re: Select statement ...........

Former Member — Mon, 31 Dec 2007 12:46:18 GMT

Hi Swati,

FOR ALL ENTRIES will deletes duplicates records from lips. There is some duplicate records and i want to fetch them also.

Which we made delivery multiple times for single vbeln and posnr for material. It won't work.

Yusuf

Re: Select statement ...........

Former Member — Mon, 31 Dec 2007 12:47:59 GMT

Hi Venkatesan ,

I solved with the following coding....and thanks for ur reply.

IF itab[] IS NOT INITIAL.

SELECT a_{vgbel a}vgpos a~lfimg INTO TABLE i_lips

FROM lips AS a INNER JOIN likp AS b

ON a_{vbeln EQ b}vbeln

WHERE a~pstyv EQ 'TAN'

AND a~werks EQ 'HIP'

AND a~lgort EQ 'BSR'

AND a~vgbel IN oano

AND b~kunnr IN code

AND b~vstel EQ 'BSP'

AND b~vkorg EQ 'BSO'

AND b~vbtyp EQ 'J'.

ENDIF.

SORT i_lips BY vgbel vgpos.

LOOP AT itab.

LOOP AT i_lips WHERE vgbel = itab-vbeln

AND vgpos = itab-posnr.

itab-lfimg = itab-lfimg + i_lips-lfimg.

ENDLOOP.

itab-bal_qty = itab-kwmeng - itab-lfimg.

MODIFY itab.

ENDLOOP.

REFRESH i_lips.

Yusuf

Re: Select statement ...........

Former Member — Mon, 31 Dec 2007 13:18:59 GMT

Hi Yusuf,

Good to hear you have done it.

Cheers,

Ananth