topic Re: How to avoid SELECT within LOOP? in Application Development and Automation Discussions

How to avoid SELECT within LOOP?

Former Member — Tue, 19 Mar 2013 10:29:07 GMT

Dear all,

I have a requirement:

I have 3 tables it_man_course , it_offerings and it_persons.

it_man_course has 2 fields: Employee Number(pernr) and Course_ID.

it_offerings has 2 fields : Course_ID and Offering.

it_persons has 2 fields: Offering and Employee no(pernr).

I select all the Employees and the mandatory courses against them from a Z table in the table it_man_course.

Now my requirement is, I need to find whether that employee has attended the course or not.

Here, the course (D) has no of offerings (E) found from table HRP1001 (relationship B 020 otype D-> sclas E)

I find this in table it_offerings.

In turn, the offerings (E) and the respective employees who have undertaken that offering is maintained as relationship A 025 in table HRP1001 again ( Otype E -> sclas P).

I take this in table it_persons.

I have to find which all pernrs in table it_courses have attended the corresponding course against them.

Eg : The first entry in table it_courses is Emp no : 001 and course_id = CourseA

Now we find entries corresponding to CourseA in table it_offerings i.e. eg:

CourseA off1

CourseA off2

CourseA off3

Note: The table it_offerings can have duplicate entries for courses.

Now in table it_persons we take entries corresponding to table it_offerings. Eg:

Off1 001

Off1 002

Off1 003

Off2 004

Off3 005

If the pernr in it_courses has attended the course that is if its entry exists in table it_persons then we need to delete that entry from table it_courses.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT position_id

man_course_id

man_course_name

man_course_sdate

man_course_edate

FROM zcourses

CLIENT SPECIFIED

INTO CORRESPONDING FIELDS OF TABLE it_man_course

WHERE mandt EQ sy-mandt.

IF sy-subrc = 0.

SORT it_man_course BY position_id.

ENDIF.

it_man_course_dup[ ] = it_man_course[ ].

LOOP AT it_man_course INTO wa_man_course.

SELECT objid

sobid

begda

endda

aedtm

FROM hrp1001 CLIENT SPECIFIED

INTO TABLE it_offerings

WHERE otype EQ 'D'

AND objid EQ wa_man_course-man_course_id

AND plvar EQ '01'

AND rsign EQ 'B'

AND relat EQ '020'

AND sclas EQ 'E'.

IF sy-subrc = 0.

SORT it_offerings BY objid.

* endif.

* This gives the number of participants for every offering

IF NOT it_offerings[ ] IS INITIAL.

SELECT objid

sobid

begda

endda

aedtm

FROM hrp1001 CLIENT SPECIFIED

INTO CORRESPONDING FIELDS OF TABLE it_persons

FOR ALL ENTRIES IN it_offerings

WHERE otype EQ 'E'

AND objid EQ it_offerings-temp_sobid

AND plvar EQ '01'

AND rsign EQ 'A'

AND relat EQ '025'

AND sclas EQ 'P'.

IF sy-subrc EQ 0.

SORT it_persons BY sobid.

CLEAR: wa_persons.

lv_pernr = wa_man_course-l_pernr.

READ TABLE it_persons INTO wa_persons WITH KEY sobid = lv_pernr BINARY SEARCH.

IF sy-subrc = 0.

DELETE TABLE it_man_course_dup FROM wa_man_course.

ENDIF.

CLEAR : wa_man_course.

ENDIF.

ENDLOOP.

Is there any way to avoid using SELECT within LOOP?

Re: How to avoid SELECT within LOOP?

Former Member — Tue, 19 Mar 2013 10:56:27 GMT

Use FOR ALL ENTRIES instead of using a select within a loop. Finally Loop at the main table reading other sub tables to arive at the final data. Or use Inner Join for fetching the data from the tables.

for eg.

IF NOT it_man_course[ ] IS INITIAL.

SELECT objid

sobid

begda

endda

aedtm

FROM hrp1001 CLIENT SPECIFIED

INTO TABLE it_offerings

FOR ALL ENTRIES IN TABLE it_man_course

WHERE otype EQ 'D'

AND objid EQ it_man_course-man_course_id

AND plvar EQ '01'

AND rsign EQ 'B'

AND relat EQ '020'

AND sclas EQ 'E'.

IF sy-subrc = 0 AND NOT it_offerings[ ] IS INITIAL.

SORT it_offerings BY objid.

SELECT objid

sobid

begda

endda

aedtm

FROM hrp1001 CLIENT SPECIFIED

INTO CORRESPONDING FIELDS OF TABLE it_persons

FOR ALL ENTRIES IN it_offerings

WHERE otype EQ 'E'

AND objid EQ it_offerings-temp_sobid

AND plvar EQ '01'

AND rsign EQ 'A'

AND relat EQ '025'

AND sclas EQ 'P'.

IF sy-subrc EQ 0.

SORT it_persons BY sobid.

ENDIF.

* Then loop at the it_man_courses and read the rest of teh tables ot get the final data.

it_man_course_dup[ ] = it_man_course[ ].

LOOP AT it_man_course INTO wa_man_course.

READ TABLE it_persons INTO wa_persons WITH KEY sobid = wa_man_course-I_pernr BINARY SEARCH.

IF sy-subrc = 0.

DELETE TABLE it_man_course_dup FROM wa_man_course.

ENDIF.

CLEAR : wa_man_course.

ENDLOOP.

Regards,

Susmitha Pritam

Re: How to avoid SELECT within LOOP?

Former Member — Tue, 19 Mar 2013 11:06:10 GMT

Hi Susmitha,

Thanks for the response.

But I need to check for every entry in the Z table whether the Person has attended the course or not.

Suppose the pernr does exist in the it_persons table but against some other course id, still it will get deleted.

Eg : 001 CourseA ---->it_course

002 CourseB

courseA Off1 ----> it_offerings

CourseB off2

Off1 002 -----> it_persons

Off2 001

Now the Employee 001 exists in it_persons but for different course offering (not for CourseA).

I need to check whether he has attended CourseA.

So I cannot delete the pernr from table it_course assuming that he has attended courseA.

Regards,

Pranita

Re: How to avoid SELECT within LOOP?

Former Member — Tue, 19 Mar 2013 11:16:47 GMT

Hi,

You can use For all entries statement to avoid select statement within loop.

Try this.

IF it_man_course IS NOT INITIAL.

SELECT objid

sobid

begda

endda

aedtm

FROM hrp1001 CLIENT SPECIFIED

INTO TABLE it_offerings

FOR ALL ENTRIES IN it_man_course

WHERE objid = it_man_course-man_course_id

AND otype EQ 'D'

AND plvar EQ '01'

AND rsign EQ 'B'

AND relat EQ '020'

AND sclas EQ 'E'.

IF sy-subrc = 0.

SORT it_offerings BY objid.

endif.

ENDIF.

Fetch all the data from yout Z tabe and load into internal table.

pernr Course id offerings

0001 COURSE A off1

0001 COURSE A off3

0002 COURSE B off1

0003 COURSE A off2

0004 COURSE B off1

Then u can delete the data as per ur requirement. It will be easy to you.

Hope It helps you.

Regards,

J. Rajbarath

Re: How to avoid SELECT within LOOP?

Former Member — Tue, 19 Mar 2013 11:34:13 GMT

Hi,

To reduce the execution frequency, there are 3 ways - Creating DDIC views, or implementing SQL joins or using FOR ALL ENTRIES.

I would recommend something similar to what Susmitha Pritham has replied above. However, I'd like to add to it by saying that before using the FOR ALL ENTRIES, make sure that there are no duplicates in the driving table (i.e. the internal table used after FOR ALL ENTRIES IN clause). This will improve the performance. So it would be:

**create it_man_course_copy like it_man_course. Also create it_offerings_copy like it_offerings.

it_man_course_copy[ ] = it_man_course[ ].

SORT it_man_course_copy BY man_course_id.

DELETE ADJACENT DUPLICATES FROM it_man_course_copy COMPARING man_course_id.

IF NOT it_man_course[ ] IS INITIAL.

SELECT objid

sobid

begda

endda

aedtm

FROM hrp1001 CLIENT SPECIFIED

INTO TABLE it_offerings

FOR ALL ENTRIES IN TABLE it_man_course

WHERE otype EQ 'D'

AND objid EQ it_man_course-man_course_id

AND plvar EQ '01'

AND rsign EQ 'B'

AND relat EQ '020'

AND sclas EQ 'E'.

IF sy-subrc = 0 AND NOT it_offerings[ ] IS INITIAL.

it_offerings_copy[ ] = it_offerings[ ].

SORT it_offerings_copy BY temp_sobid.

**Again delete the duplicates in it_offerings.

DELETE ADJACENT DUPLICATES IN it_offerings_copy COMPARING temp_sobid.

SELECT objid

sobid

begda

endda

aedtm

FROM hrp1001 CLIENT SPECIFIED

INTO CORRESPONDING FIELDS OF TABLE it_persons

FOR ALL ENTRIES IN it_offerings

WHERE otype EQ 'E'

AND objid EQ it_offerings-temp_sobid

AND plvar EQ '01'

AND rsign EQ 'A'

AND relat EQ '025'

AND sclas EQ 'P'.

IF sy-subrc EQ 0.

SORT it_persons BY sobid.

ENDIF.

The creation of the copy tables would be better because, as you can see above, the duplicates are being deleted from it. So you may skip the creation of the copy tables if it_offerings and it_man_course will not be used again any further in the code .

Re: How to avoid SELECT within LOOP?

matt — Tue, 19 Mar 2013 12:02:04 GMT

Don't use FOR ALL ENTRIES, use an INNER JOIN (or actually in this case an OUTER JOIN). Most of the time that will be better performing that FOR ALL ENTRIES.

Re: How to avoid SELECT within LOOP?

ravi_lanjewar — Thu, 21 Mar 2013 10:20:39 GMT

Hi Paranita,

This is the HR development.

Why are you using this way to access the data ?

Following are the logical databases which we are used in HR development.

1) PNP - HR Master Data

2) PAP - Application Master Data

3) PCH - Personnel planning ( For you case )

4) PTRVT - Travel Management

Generally follows the practice while doing the HR development.

Use Logical data base completly if possible

First used Logical data base and filter data with you custom logic to display required data.

If 1 and 2 is not possible then create you program without logical database.

If possible try to used the logical database, It will give better performance over without logical database.

Rgds

Ravishankar