topic Re: Remove inside loop , select.....end select in Application Development and Automation Discussions

Remove inside loop , select.....end select

Former Member — Thu, 21 Dec 2006 09:14:27 GMT

Hi experts in following code remove select... end select and remove select inside loop, reply me soon here 'T_' refers to internal tables

LOOP AT T_IAUG.

MOVE T_ICOLDATA-GJAHR TO T_ICOLDATA-XGJHR.

SELECT * FROM BSAK INTO TABLE T_IBSAK

WHERE BUKRS = T_IAUG-BUKRS

AND AUGDT = T_IAUG-AUGDT

AND AUGBL = T_IAUG-AUGBL.

LOOP AT T_IBSAK.

CLEAR: T_ICOLDATA, D_DATAD, D_DATAK.

SELECT * FROM BSEG WHERE BUKRS = T_IBSAK-BUKRS

AND BELNR = T_IBSAK-BELNR

AND GJAHR = T_IBSAK-GJAHR.

IF T_ICOLDATA-LIFNR IS INITIAL.

MOVE BSEG-LIFNR TO T_ICOLDATA-LIFNR.

ENDIF.

CASE BSEG-HKONT.

WHEN '0005700022'. " NSL

IF BSEG-SHKZG = 'S'.

T_ICOLDATA-NSLBT = T_ICOLDATA-NSLBT + BSEG-DMBTR.

ELSE.

T_ICOLDATA-NSLBT = T_ICOLDATA-NSLBT - BSEG-DMBTR.

ENDIF.

WHEN '0005700025'. " GST

IF BSEG-SHKZG = 'S'.

T_ICOLDATA-GSTBT = T_ICOLDATA-GSTBT + BSEG-DMBTR.

ELSE.

T_ICOLDATA-GSTBT = T_ICOLDATA-GSTBT - BSEG-DMBTR.

ENDIF.

ENDCASE.

IF ( NOT BSEG-KUNNR IS INITIAL OR NOT BSEG-LIFNR IS INITIAL ).

if bseg-shkzg = 'S'.

D_DATAD = D_DATAD + BSEG-DMBTR.

else.
d_datak = d_datak - bseg-dmbtr.

ENDIF.

endcase.

ENDSELECT.

IF NOT ( T_ICOLDATA-NSLBT IS INITIAL

AND T_ICOLDATA-GSTBT IS INITIAL ).

IF T_ICOLDATA-NSLBT LT 0 OR T_ICOLDATA-GSTBT LT 0.

T_ICOLDATA-INVBT = D_DATAD * -1.

ELSE.

T_ICOLDATA-INVBT = D_DATAD.

ENDIF.

SELECT SINGLE * FROM BKPF WHERE BUKRS = T_IBSAK-BUKRS

AND BELNR = T_IBSAK-BELNR

AND GJAHR = T_IBSAK-GJAHR.

MOVE BKPF-XBLNR TO T_ICOLDATA-XBLNR.

MOVE BKPF-BUDAT TO T_ICOLDATA-BUDAT.

MOVE BKPF-BELNR TO T_ICOLDATA-BELNR.

MOVE T_IAUG-AUGDT TO T_ICOLDATA-AUGDT.

MOVE T_IAUG-AUGBL TO T_ICOLDATA-AUGBL.

APPEND T_ICOLDATA.

ENDIF.

ENDLOOP.

Re: Remove inside loop , select.....end select

Former Member — Thu, 21 Dec 2006 09:16:52 GMT

hi,

Use FOR ALL ENTRIES to avoid loop endloop ..

i.e,


SELECT * FROM BSAK INTO TABLE T_IBSAK
FOR ALL ENTRIES IN T_IAUG 
WHERE BUKRS = T_IAUG-BUKRS
AND AUGDT = T_IAUG-AUGDT
AND AUGBL = T_IAUG-AUGBL.

Regards,

Santosh

Re: Remove inside loop , select.....end select

Former Member — Thu, 21 Dec 2006 09:19:40 GMT

Hi Sanjana,

Use the following code outside loop in place of select..endselect.

DATA: T_BSEG TYPE TABLE OF BSEG.

SELECT * FROM BSEG INTO TABLE T_BSEG

FOR ALL ENTRIES IN T_IBSAK

WHERE BUKRS = T_IBSAK-BUKRS

AND BELNR = T_IBSAK-BELNR

AND GJAHR = T_IBSAK-GJAHR.

Then you can loop at this table T_BSEG inside your loop & get the desired values for comparison.

You can keep the select single statement.

Regards,

Chetan.

PS:Reward points if this helps.

Re: Remove inside loop , select.....end select

Former Member — Thu, 21 Dec 2006 10:42:35 GMT

Hi Sanajana,

We should try to avoid writing select statements inside the loop.It decreases the performance of the program as it hits the database as many times you are looping.You can select the data from database table outside the loop and read those tables indide the loop.

Thaks,

Sutapa.

Re: Remove inside loop , select.....end select

Former Member — Thu, 21 Dec 2006 10:46:09 GMT

Hi Sanjana,

Try to remove Select......Endselect and try to collect all the data into internal table using FOR ALL ENTRIES of the table. This improves the performance.

-Priyanka.

Re: Remove inside loop , select.....end select

Former Member — Thu, 21 Dec 2006 10:52:07 GMT

for all entries in itab will remove the usage of select inside the loop.

LOOP AT T_IAUG.

MOVE T_ICOLDATA-GJAHR TO T_ICOLDATA-XGJHR.

ENDLOOP.

SELECT * FROM BSAK INTO TABLE T_IBSAK

FOR ALL ENTRIES IN T_IAUG

WHERE BUKRS = T_IAUG-BUKRS

AND AUGDT = T_IAUG-AUGDT

AND AUGBL = T_IAUG-AUGBL.

By using into table itab u can avoid using endselect.

Re: Remove inside loop , select.....end select

graghavendra_sharma — Thu, 21 Dec 2006 11:03:11 GMT

Hi Sanjana

I have just modified your code.

DATA: t_ibsak TYPE STANDARD TABLE OF bsak WITH HEADER LINE,

t_bseg TYPE STANDARD TABLE OF bseg WITH HEADER LINE.

IF NOT t_iaug[] IS INITIAL.

SELECT * FROM bsak INTO TABLE t_ibsak

FOR ALL ENTRIES IN t_iaug

WHERE bukrs = t_iaug-bukrs

AND augdt = t_iaug-augdt

AND augbl = t_iaug-augbl.

ENDIF.

IF NOT t_ibsak[] IS INITIAL.

SELECT * FROM bseg

INTO TABLE t_bseg.

for all entreis in t_ibsak

where bukrs = t_ibsak-bukrs

and belnr = t_ibsak-belnr

and gjahr = t_ibsak-gjahr.

ENDIF.

LOOP AT t_iaug.

MOVE t_icoldata-gjahr TO t_icoldata-xgjhr.

LOOP AT t_ibsak.

CLEAR: t_icoldata, d_datad, d_datak.

READ TABLE t_bseg WITH KEY bukrs = t_ibsak-bukrs

belnr = t_ibsak-belnr

gjahr = t_ibsak-gjahr.

IF sy-subrc = 0.

IF t_icoldata-lifnr IS INITIAL.

MOVE t_bseg-lifnr TO t_icoldata-lifnr.

ENDIF.

CASE t_bseg-hkont.

WHEN '0005700022'. " NSL

IF t_bseg-shkzg = 'S'.

t_icoldata-nslbt = t_icoldata-nslbt + t_bseg-dmbtr.

ELSE.

t_icoldata-nslbt = t_icoldata-nslbt - t_bseg-dmbtr.

ENDIF.

WHEN '0005700025'. " GST

IF t_bseg-shkzg = 'S'.

t_icoldata-gstbt = t_icoldata-gstbt + t_bseg-dmbtr.

ELSE.

t_icoldata-gstbt = t_icoldata-gstbt - t_bseg-dmbtr.

ENDIF.

ENDCASE.

IF ( NOT t_bseg-kunnr IS INITIAL OR NOT t_bseg-lifnr IS INITIAL ).

if bseg-shkzg = 'S'.

d_datad = d_datad + t_bseg-dmbtr.

else.
d_datak = d_datak - bseg-dmbtr.

ENDIF.

endcase.

ENDIF.

IF NOT ( t_icoldata-nslbt IS INITIAL

AND t_icoldata-gstbt IS INITIAL ).

IF t_icoldata-nslbt LT 0 OR t_icoldata-gstbt LT 0.

t_icoldata-invbt = d_datad * -1.

ELSE.

t_icoldata-invbt = d_datad.

ENDIF.

SELECT SINGLE * FROM bkpf WHERE bukrs = t_ibsak-bukrs

AND belnr = t_ibsak-belnr

AND gjahr = t_ibsak-gjahr.

MOVE bkpf-xblnr TO t_icoldata-xblnr.

MOVE bkpf-budat TO t_icoldata-budat.

MOVE bkpf-belnr TO t_icoldata-belnr.

MOVE t_iaug-augdt TO t_icoldata-augdt.

MOVE t_iaug-augbl TO t_icoldata-augbl.

APPEND t_icoldata.

ENDIF.

ENDLOOP.