topic Re: How to avoid select single inside Loop in Application Development and Automation Discussions

How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 12:05:43 GMT

Hi,

This is Pavan. I need to use select single inside a loop. Can anyone help me out inavoiding usage of select single inside Loop. for reference see the code below.

FORM data_ssn .

*> if program is in test run mode then display

LOOP AT i_hr INTO wa_hr.

*> Read the Personnel number from pa0002

SELECT SINGLE pernr FROM pa0002

INTO wa_hr1-pernr

WHERE perid = wa_hr-retiree_ssn.

READ TABLE pa0002 INTO wa_hr1-pernr WITH TABLE KEY perid = wa_hr-retiree_ssn.

*> Check sy-subrc

IF sy-subrc = 0.

*> Read infotype 0000

CALL FUNCTION 'HR_READ_INFOTYPE'

EXPORTING

pernr = wa_hr1-pernr

infty = c_0000

TABLES

infty_tab = i_0000

EXCEPTIONS

infty_not_found = 1

OTHERS = 2.

IF sy-subrc <> 0.

ENDIF.

*> Sort Begda by Descending

SORT i_0000 BY begda DESCENDING.

*> Read Internal table

READ TABLE i_0000 INTO wa_0000 INDEX 1.

*> Check if stat2 EQ 2

IF wa_0000-stat2 EQ c_2.

*> Read Infotype 0001

CALL FUNCTION 'HR_READ_INFOTYPE'

EXPORTING

pernr = wa_hr1-pernr

infty = c_0001

begda = wa_0000-begda

endda = wa_0000-endda

TABLES

infty_tab = i_0001

EXCEPTIONS

infty_not_found = 1

OTHERS = 2.

IF sy-subrc <> 0.

ENDIF.

*> Read record form infotype 0001

READ TABLE i_0001 INTO wa_0001 INDEX 1.

*> Check Employee Group

IF wa_0001-persg EQ c_4 OR wa_0001-persg EQ c_6.

*> Read infotype 0105

CALL FUNCTION 'HR_READ_INFOTYPE'

EXPORTING

pernr = wa_hr1-pernr

infty = c_0105

TABLES

infty_tab = i_0105

EXCEPTIONS

infty_not_found = 1

OTHERS = 2.

IF sy-subrc <> 0.

ENDIF.

*> Read the record form infotype 0105

READ TABLE i_0105 INTO wa_0105 WITH KEY usrty = c_9005.

*> Check sy-subrc

IF sy-subrc EQ 0.

*> Move Communication ID/Number to Workcomp

wa_hr1-workcomp = wa_0105-usrid .

ENDIF.

*> Move the records SSN NAME MEDICAL_DED LIFE_DED PENSION_PLAN to Final

*> internal table

MOVE : wa_hr-retiree_ssn TO wa_hr1-ssn,

wa_hr-retiree_name TO wa_hr1-name,

wa_hr-medical_ded TO wa_hr1-health_pre,

wa_hr-life_ded TO wa_hr1-life_pre,

wa_hr-pension_plan TO wa_hr1-pension_plan.

CLEAR wa_hr.

ENDIF.

*> Append Work area to Internal table

APPEND wa_hr1 TO i_hr1.

CLEAR wa_hr1.

ENDLOOP.

Re: How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 12:14:11 GMT

I think you yourself have given the answer:

Instead of using SELECT SINGLE retrieve all the personal number from P0002 in one shot outside the loop using FOR ALL ENTRIES of the internal tab.

Then use

READ TABLE pa0002 INTO wa_hr1-pernr WITH TABLE KEY perid = wa_hr-retiree_ssn.

within the loop.

Re: How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 12:14:15 GMT

HI,

U can use FOR ALL ENTRIES for that table on which are you looping and later on u can delete adjacent duplicates to remove multiple values.

Re: How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 12:18:16 GMT

create an internal table it_pernr with the field pernr,

Use the below code and fill the internal table

select pernr from pa0002 into table it_pernr for all entries in i_hr
where perid = i_hr-retiree_ssn.

Now inside the loop use the read statment instead of select single

Hope this helps U

Thanks and regards

Tamilselvan.K

Re: How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 12:22:32 GMT

hiiii

i think you should use READ statement only in that for first go you need to have values in some other variable.so can use if sy-subrc EQ 0..& exit from it...So that you can get only one value like SELECT SINGLE statement..

reward if useful

thx

twinkal

Re: How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 12:34:23 GMT

Hi,

you can use the select statement with all entries clause .

and then use read statement in a loop. This will avoid hitting the database for each loop pass.

you can write your code as :

select pernr from pa002 into table pa002 for all entries in i_hr

where perid = i_hr-retiree_ssn.

and then you can do your all operation by putting a loop on the internal table pa002.

I hope this will help you out.

Re: How to avoid select single inside Loop

christine_evans — Thu, 19 Jun 2008 13:28:22 GMT

And what is so wrong about using SELECT SINGLE inside a LOOP, so long as it is an efficient and properly indexed SELECT SINGLE? Doing this is sometimes a lot better than the cumbersome 'select everything you could ever possibly need into an itab and then read the itab' solution that is often proposed.

Re: How to avoid select single inside Loop

ThomasZloch — Thu, 19 Jun 2008 13:42:48 GMT

I agree with you as long as the table is buffered. If it's not buffered according to technical settings, and it's not too large, I'd prefer buffering it myself in an internal, sorted table.

Cheers

Thomas

Re: How to avoid select single inside Loop

christine_evans — Thu, 19 Jun 2008 13:49:01 GMT

Even if the table is not buffered, a properly indexed SELECT SINGLE can be very, very quick. And you get only the single record you need at the time you need it rather than filling up your memory with an itab full of records all of which you don't need at the time and some of which you may not need at all. It just feels cleaner somehow ....

Re: How to avoid select single inside Loop

ThomasZloch — Thu, 19 Jun 2008 13:55:55 GMT

True, it does not make sense to buffer 1,000 records if you only need 10 distinct ones along the way. There has been a suggestion in this thread to check whether the entry exists in the internal buffer table, if not SELECT SINGLE that one from the DB and insert it into the internal table according to sort key. I like that approach in this specific case. Again, this only applies to non-buffered tables.

I personally keep an eye on minimizing the trips to the database as much as possible, within a reasonable frame, of course.

Greetings

Thomas

Edit: All this depends very much on the number of outer loops. If it's a few hundred, not worth bothering. If it's many thousands or even millions, it makes a difference.

Re: How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 14:10:09 GMT

I'd be more concerned about the logic. Why SELECT from PA0002 based only on PERNR?

Rob

Re: How to avoid select single inside Loop

ThomasZloch — Thu, 19 Jun 2008 14:16:37 GMT

dunno, I'm having a general discussion here

Isn't he selecting on PERID?

Re: How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 14:19:09 GMT

Good catch Thomas, but I think that makes it worse.

Rob

Re: How to avoid select single inside Loop

ThomasZloch — Thu, 19 Jun 2008 14:25:42 GMT

well, there is a secondary index "MCC" on PERID and PERMO...but it's non-unique...so SELECT SINGLE is not clean, you're right.

Re: How to avoid select single inside Loop

christine_evans — Thu, 19 Jun 2008 14:26:45 GMT

I'm astonished that anyone has actually bothered to read the code, considering the mess of the layout.

Re: How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 14:29:27 GMT

Thomas - that was my point. Without specifying begin and end date, the results will not be predictable.

Christime - what can I say? It just jumped out at me.

Rob

Re: How to avoid select single inside Loop

ThomasZloch — Thu, 19 Jun 2008 14:29:42 GMT

yeah, I don't know what's so difficult to put "" before and after those snippets...but for Rob it's early in the day, so he's fresh enough to wade through it

Re: How to avoid select single inside Loop

Former Member — Thu, 19 Jun 2008 14:35:04 GMT

Wading not required. It's right at the top. No idea though what you'd find further down. Probably not worth wasting more time on though.

Rob