topic Re: select inside a loop in Application Development and Automation Discussions

select inside a loop

vijy_mukunthan — Tue, 05 May 2009 12:17:40 GMT

Hi friends

I have written a code to find the manager. but am using select statement inside the loop. I tried to do other ways but its in vain. Can any give some idea to change it. Here is my code.

DATA : LV_PERNR LIKE HRP1001-SOBID .
  DATA : RECLIST LIKE SOMLRECI1 OCCURS 1 WITH HEADER LINE.
*  DATA : V_RULE_NO LIKE HRP1240-EXT_OBJID.

*  V_RULE_NO = '95000018'. "CUSTOM RULE NUMBER FOR TRAVEL EXPENSE MANAGER.

  DATA : LV_EXP_PERNR TYPE SOBID.

  DATA : BEGIN OF LT_OBJID OCCURS 0,
         OBJID LIKE HRP1240-EXT_OBJID,
         END OF LT_OBJID.

  DATA : BEGIN OF T_WERKS OCCURS 0,
         V_WERKS_OBJID LIKE HRV1218-OBJID,
         END OF T_WERKS.

  DATA : BEGIN OF T_BTRTL OCCURS 0,
           V_BTRTL_OBJID LIKE HRV1218-OBJID,
           END OF T_BTRTL.

  DATA : V_OBJID LIKE HRV1218-OBJID.

  SELECT OBJID FROM HRP1240 INTO TABLE LT_OBJID WHERE EXT_OBJID = V_RULE_NO.

  LOOP AT LT_OBJID.

    SELECT OBJID FROM HRV1218 INTO TABLE T_WERKS WHERE PLVAR = '01' AND OTYPE = 'RY' AND ENDDA = '99991231'
         AND REFFIELD = 'WERKS' AND EXPR_LOW = LV_PA AND OBJID = LT_OBJID-OBJID.

    IF SY-SUBRC = 0.
      SELECT OBJID INTO TABLE T_BTRTL FROM HRV1218 FOR ALL ENTRIES IN T_WERKS WHERE OBJID = T_WERKS-V_WERKS_OBJID AND
      REFFIELD = 'BTRTL' AND EXPR_LOW = LV_PSA AND PLVAR = '01' AND OTYPE = 'RY' AND ENDDA = '99991231'.

      IF SY-SUBRC = 0.

        SELECT OBJID INTO V_OBJID UP TO 1 ROWS FROM HRV1218 FOR ALL ENTRIES IN T_BTRTL WHERE PLVAR = '01' AND OTYPE = 'RY' AND
        OBJID = T_BTRTL-V_BTRTL_OBJID AND ENDDA = '99991231' AND REFFIELD = 'ORGEH' AND EXPR_LOW LE LV_ORGID AND
        EXPR_HIGH GE LV_ORGID.
        ENDSELECT.

        IF V_OBJID IS INITIAL.
          CONTINUE.
        ELSE.
          EXIT.
        ENDIF.
      ENDIF.
    ENDIF.
  ENDLOOP.

Till the V_OBJID is not initial i am executing the loop. Is there any other way to do this. The V_OBJID contains the personal number. How to modify the code without using select statement inside the loop.

Regards

vijay

Re: select inside a loop

Former Member — Tue, 05 May 2009 12:23:13 GMT

Hi,

write this format...


 SELECT objid FROM hrv1218 INTO TABLE t_werks
         for ALL entries of lt_objid "write this
         WHERE plvar = '01' AND otype = 'RY' AND endda = '99991231'
         AND reffield = 'WERKS' AND expr_low = lv_pa 
         AND objid = lt_objid-objid.
IF sy-subrc  = 0.
  SELECT objid INTO TABLE t_btrtl FROM hrv1218 
          FOR ALL ENTRIES IN t_werks
          WHERE objid = t_werks-v_werks_objid AND
          reffield = 'BTRTL' AND expr_low = lv_psa AND plvar = '01'
          AND otype = 'RY' AND endda = '99991231'.
  IF sy-subrc  = 0.
  ENDIF.
ENDIF.



LOOP AT lt_objid.

  READ TABLE t_werks WITH KEY objid = lt_objid-objid.
  IF sy-subrc = 0.
    READ TABLE with key table t_btrtl objid = t_werks-v_werks_objid .
    IF sy-subrc = 0.
      SELECT objid INTO v_objid UP TO 1 ROWS FROM hrv1218
          FOR ALL ENTRIES IN t_btrtl WHERE plvar = '01'
          AND otype = 'RY' AND objid = t_btrtl-v_btrtl_objid 
          AND endda = '99991231' AND reffield = 'ORGEH' 
           AND expr_low LE lv_orgid AND expr_high GE lv_orgid.
      ENDSELECT.
      IF v_objid IS INITIAL.
        CONTINUE.
      ELSE.
        EXIT.
      ENDIF.
    ENDIF.
  ENDIF.
ENDLOOP.

Regards,

Prabhudas

Re: select inside a loop

Former Member — Tue, 05 May 2009 12:26:06 GMT

DATA : LV_PERNR LIKE HRP1001-SOBID .
  DATA : RECLIST LIKE SOMLRECI1 OCCURS 1 WITH HEADER LINE.
*  DATA : V_RULE_NO LIKE HRP1240-EXT_OBJID.
 
*  V_RULE_NO = '95000018'. "CUSTOM RULE NUMBER FOR TRAVEL EXPENSE MANAGER.
 
  DATA : LV_EXP_PERNR TYPE SOBID.
 
  DATA : BEGIN OF LT_OBJID OCCURS 0,
         OBJID LIKE HRP1240-EXT_OBJID,
         END OF LT_OBJID.
 
  DATA : BEGIN OF T_WERKS OCCURS 0,
         V_WERKS_OBJID LIKE HRV1218-OBJID,
         END OF T_WERKS.
 
  DATA : BEGIN OF T_BTRTL OCCURS 0,
           V_BTRTL_OBJID LIKE HRV1218-OBJID,
           END OF T_BTRTL.
 
  DATA : V_OBJID LIKE HRV1218-OBJID.
 

      SELECT OBJID INTO TABLE T_BTRTL FROM HRV1218 FOR ALL ENTRIES IN T_WERKS WHERE OBJID = T_WERKS-V_WERKS_OBJID AND
      REFFIELD = 'BTRTL' AND EXPR_LOW = LV_PSA AND PLVAR = '01' AND OTYPE = 'RY' AND ENDDA = '99991231'.
 
        SELECT PLVAR OTYPE OBJID ENDDA REFFIELD EXPR_LOW EXPR_HIGH INTO table IT_OBJID FROM HRV1218 FOR ALL ENTRIES IN T_BTRTL WHERE PLVAR = '01' AND OTYPE = 'RY' AND
        OBJID = T_BTRTL-V_BTRTL_OBJID AND ENDDA = '99991231' AND REFFIELD = 'ORGEH' AND EXPR_LOW LE LV_ORGID AND
        EXPR_HIGH GE LV_ORGID.

  SELECT OBJID FROM HRP1240 INTO TABLE LT_OBJID WHERE EXT_OBJID = V_RULE_NO.
 
*  LOOP AT LT_OBJID.
 if not lt_objid[] is initial.
    SELECT OBJID FROM HRV1218 INTO TABLE T_WERKS 
    for all entries in lt_objid
WHERE PLVAR = '01' AND OTYPE = 'RY' AND ENDDA = '99991231'
         AND REFFIELD = 'WERKS' AND EXPR_LOW = LV_PA AND OBJID = LT_OBJID-OBJID.
 
    IF SY-SUBRC = 0.

 read table lt_objid with key PLVAR = '01'  OTYPE = 'RY' 
        OBJID = T_BTRTL-V_BTRTL_OBJID ENDDA = '99991231'  REFFIELD = 'ORGEH'  EXPR_LOW LE LV_ORGID 
        EXPR_HIGH GE LV_ORGID.
v_objid = lt_objid-objid.
        IF V_OBJID IS INITIAL.
          CONTINUE.
        ELSE.
          EXIT.
        ENDIF.
      ENDIF.

*  ENDLOOP.
endif.

Re: select inside a loop

Former Member — Tue, 05 May 2009 13:22:03 GMT


   SELECT OBJID 
                FROM HRV1218
                INTO TABLE T_WERKS 
                WHERE PLVAR = '01' 
                AND OTYPE = 'RY' 
                AND ENDDA = '99991231'
                AND REFFIELD = 'WERKS' 
                AND EXPR_LOW = LV_PA 
                AND OBJID = LT_OBJID-OBJID.
 
    IF SY-SUBRC = 0.
      SELECT OBJID 
                   INTO TABLE T_BTRTL 
                   FROM HRV1218 
                   FOR ALL ENTRIES IN T_WERKS 
                   WHERE OBJID = T_WERKS-V_WERKS_OBJID 
                    AND REFFIELD = 'BTRTL' 
                   AND EXPR_LOW = LV_PSA 
                  AND PLVAR = '01' 
                  AND OTYPE = 'RY' 
                  AND ENDDA = '99991231'.
 
      IF SY-SUBRC = 0.
 
        SELECT OBJID 
                     INTO V_OBJID UP TO 1 ROWS 
                     FROM HRV1218 
                     FOR ALL ENTRIES IN T_BTRTL 
                     WHERE PLVAR = '01' 
                     AND OTYPE = 'RY' 
                     AND OBJID = T_BTRTL-V_BTRTL_OBJID 
                     AND ENDDA = '99991231' 
                     AND REFFIELD = 'ORGEH' 
                     AND EXPR_LOW LE LV_ORGID 
                     AND EXPR_HIGH GE LV_ORGID.
        ENDSELECT.

The FOR ALL ENTRIES calls are incorrect.

The rest is a typical task for a self-join.

Siegfried

Re: select inside a loop

vijy_mukunthan — Wed, 06 May 2009 06:44:30 GMT

Can i use many read statement inside a loop. will it affect the performance.

Regards

vijay

Re: select inside a loop

Former Member — Wed, 06 May 2009 07:55:04 GMT

> Can i use many read statement inside a loop. will it affect the performance.

yes you can, BUT it must be a binary search (standard table with with BS or sorted table)

or a direct read (hashed table or index read)

Re: select inside a loop

Former Member — Fri, 15 May 2009 04:53:50 GMT

Use a read statement....tat will help....but use them on the keys.