Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Select query in case of Multiple line items

Former Member
0 Kudos

Hi Gurus ,

I've a doubt in general SQL select query. I want to know , if suppose I've an internal table - itab . I've fetched G/L Account numbers 1st, based on the input selections . Next , I want to loop on those G/L accounts. However, if the G/L account has multiple line items, then I personally use this select query -- >

loop at itab.
select <field> from <table> appending corresponding fields of  <itab1> where hkont eq itab-hkont.
endloop.

Now, the execution time for this query is longer than expected. The biggest problem here is, i've to sum up the totals as well. So totaling is an added load. I want to reduce the execution time of this. Kindly suggest me some good method in case u have any.

I've pasted the code which I've written , for u ppl to understand--

SELECT DISTINCT HKONT BELNR
  FROM BSIS
   INTO CORRESPONDING FIELDS OF TABLE OTAB
    WHERE HKONT IN S_RACCT
*      AND PRCTR IN P_PRCTR
      AND MONAT IN S_POPER
      AND BUKRS EQ P_BUKRS
      AND GJAHR EQ P_GJAHR
      AND PRCTR IN S_PRCTR.

***The code below takes a lot of time to execute.***
LOOP AT OTAB .

  SELECT DMBTR HKONT
  FROM BSIS APPENDING CORRESPONDING FIELDS OF TABLE CREDITS
    WHERE HKONT EQ OTAB-HKONT
      AND BELNR EQ OTAB-BELNR
      AND MONAT IN S_POPER
      AND BUKRS EQ P_BUKRS
      AND GJAHR EQ P_GJAHR
      AND PRCTR IN S_PRCTR
      AND SHKZG EQ 'H'.

  COLLECT CREDITS.

ENDLOOP.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi Sridhar,

Select inside a Loop leads to performance issue. So better go with a READ statement inside a Loop.

First you get all the required records into an internal table and then sum up the fields. If you directly do the operations on the select statement, it again leads to performance issue. So, to avoid this try to get everything into internal tables and then operate on them.

Regards,

Swapna.

Edited by: NagaSwapna Thota on May 27, 2009 12:32 PM

13 REPLIES 13

agnihotro_sinha2
Active Contributor
0 Kudos

hi,

try using OPEN CURSOR and CLOSE CURSOR statements if select is taking too long.

Do a F1 on OPEN CURSOR to get detailed sysntax.

ags.

Former Member
0 Kudos

Hi Sridhar,

Select inside a Loop leads to performance issue. So better go with a READ statement inside a Loop.

First you get all the required records into an internal table and then sum up the fields. If you directly do the operations on the select statement, it again leads to performance issue. So, to avoid this try to get everything into internal tables and then operate on them.

Regards,

Swapna.

Edited by: NagaSwapna Thota on May 27, 2009 12:32 PM

0 Kudos

Hi Swapna,

Yeah, I tried the Read statement. I have amounts to be added up as well. In the code that I'd pasted, I'd used the collect statement within the loop , so the amounts got added up as well. Now , using read statement , how do I perform the same ?? Could u please help me out ?

Former Member
0 Kudos

Dear Sridhar,

avaoid select statements inside the loop. it will increase lot of performance.

first select data from BSIS into some internal table and then read this data with in the loop.

SELECT DMBTR HKONT FROM BSIS APPENDING CORRESPONDING FIELDS OF TABLE lt_CREDITS

WHERE MONAT IN S_POPER

AND BUKRS EQ P_BUKRS

AND GJAHR EQ P_GJAHR

AND PRCTR IN S_PRCTR

AND SHKZG EQ 'H'.

now with in the loop

read it_credits with conditions like WHERE HKONT IN S_RACCT.

I used this and works well. Hope this will helps u.

Rgds,

Kiran

0 Kudos

Kiran ,

In case, I've already fetched RACCT from and placed it in an Intenal Table - ITAB . I want to loop over ITAB , using the fetched RACCT from ITAB , to get amounts, from a different DB Table , and Place it in 2nd internal table (say - Credits) then how do i use the read statement ?

Former Member
0 Kudos

Hi,

First of all try to avoid doing select into corresponding fields. THis would improve the performance of the program.

Try to do a single fetch from the BSIS table . fetch the hkont, belnr, dmbtr fields in to a master internal table. Manipulate and play with the data as required. Don't hit the data base table more than once (unless it is required) . This would improve the performance of your code.

Try to code this way.

types: begin of ty_bsis,

hkont type hkont,

belnr type belnr_d,

dmbtr type dmbtr,

end of ty_bsis.

data: it_bsis type standard table of ty_bsis,

wa_bsis type ty_bsis,

select hkont belnr dmbtr

from bsis

into table it_bsis

WHERE HKONT IN S_RACCT

  • AND PRCTR IN P_PRCTR

AND MONAT IN S_POPER

AND BUKRS EQ P_BUKRS

AND GJAHR EQ P_GJAHR

AND PRCTR IN S_PRCTR.

Using the data availabe in the it_bsis, you can manipulate as required.

Hope this would be helpful

Regards

Ramesh Sundaram

ThomasZloch
Active Contributor
0 Kudos

No need to have two selects on BSIS, imo, check if this works:


SELECT HKONT SUM( DMBTR ) INTO CORRESPONDING FIELDS OF TABLE CREDITS
  FROM BSIS

   WHERE HKONT IN S_RACCT
      AND MONAT IN S_POPER
      AND BUKRS EQ P_BUKRS
      AND GJAHR EQ P_GJAHR
      AND PRCTR IN S_PRCTR
      AND SHKZG EQ 'H'
  GROUP BY HKONT.
 

Make sure S_RACCT contains a small selection of G/L accounts.

Thomas

0 Kudos

Hey all,

I tried this - And it worked somehow !!

SELECT HKONT  DMBTR APPENDING CORRESPONDING FIELDS OF TABLE DEBITS
  FROM BSIS FOR ALL ENTRIES IN OTAB
    WHERE HKONT EQ OTAB-HKONT
*      AND BELNR EQ OTAB-BELNR
     AND  MONAT IN S_POPER
      AND BUKRS EQ P_BUKRS
      AND GJAHR EQ P_GJAHR
      AND PRCTR IN S_PRCTR
      AND SHKZG EQ 'S'.

The catch here is - For all Entries in OTAB. Note that there is no use of loop either !

jayanthi_jayaraman
Active Contributor
0 Kudos

Hi,

SELECT HKONT BELNR DMBTR

FROM BSIS

INTO TABLE OTAB

WHERE HKONT IN S_RACCT

AND PRCTR IN S_PRCTR

AND MONAT IN S_POPER

AND BUKRS EQ P_BUKRS

AND GJAHR EQ P_GJAHR

AND PRCTR IN S_PRCTR

AND SHKZG EQ 'H'.

LOOP AT OTAB .

...Do the manipulations here for table credit

ENDLOOP.

Former Member
0 Kudos

Hi,

But check for the performance.

Again you are using 2 select query for the same table.

Ramesh

former_member194613
Active Contributor
0 Kudos

> Select inside a Loop leads to performance issue. So better go with a READ statement inside a Loop.

I don'tknow where this nonsense comes from, I can read it here very often, READ => 1 line, LOOP => several lines, they are different and can not be exchanged.

0 Kudos

^ ^ ^

Spot on !

former_member194613
Active Contributor
0 Kudos

> For all Entries in OTAB. Note that there is no use of loop either !

there is of course a LOOP, you still must execute the COLLECT inside a LOOP.

Better try the aggregate which Thomas proposed, that is the only really useful proposal in this thread.