Select query to fetch multiple related position codes against objid

[udaykumar_kanike]

Hi All,

          I have a custom table in which against each person's  ATO number, I have multiple position codes of his reporting structure such as reporting manager, functional head, business head etc. I am trying to fetch names of a person and his reporting manager, functional head, business head in one single select query. I want to know if this is possible or I have to write individual select queries for each of them. I have tried using Select Single statement on HRP1001 to get  within a Loop which is nearly killing my program. Please find the code below which I return. Any inputs would be much appreciated.

   SELECT * FROM zhrato_hdr CLIENT SPECIFIED INTO TABLE it_zhrato_hdr
         WHERE mandt = sy-mandt
              AND plvar = '01'
              AND otype = 'AO'
              AND objid IN ato_no
              AND zbusiness IN orgl1.
  SORT it_zhrato_hdr BY objid.
  DELETE ADJACENT DUPLICATES FROM it_zhrato_hdr COMPARING objid.

   SELECT objid sobid FROM hrp1001 CLIENT SPECIFIED INTO TABLE it_1001
    FOR ALL ENTRIES IN it_zhrato_hdr
    WHERE mandt = sy-mandt
      AND otype = 'AO'
      AND objid = it_zhrato_hdr-objid
      AND plvar = '01'
      AND rsign = 'B'
      AND relat = '790'
      AND begda LE sy-datum
      AND endda GE sy-datum.
  SORT  it_1001[] BY objid.
  DELETE ADJACENT DUPLICATES FROM  it_1001 COMPARING objid.

   SELECT objid sobid FROM hrp1001 CLIENT SPECIFIED INTO TABLE it_1001_ne
    FOR ALL ENTRIES IN it_temp_role
    WHERE mandt = sy-mandt
      AND otype = 'NE'
      AND objid = it_temp_role-sobid
      AND plvar = '01'
      AND rsign = 'A'
      AND relat = '654'
      AND begda LE sy-datum
      AND endda GE sy-datum.
  SORT it_1001_ne[] BY objid.
  DELETE ADJACENT DUPLICATES FROM it_1001_ne COMPARING objid.

   SELECT objid sobid FROM hrp1001 CLIENT SPECIFIED INTO TABLE it_1001_nb
    FOR ALL ENTRIES IN it_temp_ne
    WHERE mandt = sy-mandt
      AND otype = 'NB'
      AND objid = it_temp_ne-sobid
      AND plvar = '01'
      AND rsign = 'A'
      AND relat = '789'
      AND begda LE sy-datum
      AND endda GE sy-datum.
  SORT it_1001_nb[] BY objid.
  DELETE ADJACENT DUPLICATES FROM it_1001_nb COMPARING objid.

   SELECT objid sobid FROM hrp1001 CLIENT SPECIFIED INTO TABLE it_1001_s
  FOR ALL ENTRIES IN it_temp_nb
  WHERE mandt = sy-mandt
    AND otype = 'A1'
    AND objid = it_temp_nb-sobid
    AND plvar = '01'
    AND rsign = 'B'
    AND relat = '787'
    AND begda LE sy-datum
    AND endda GE sy-datum.
SORT it_1001_s[] BY objid.
DELETE ADJACENT DUPLICATES FROM it_1001_s COMPARING objid.

   SELECT objid sobid FROM hrp1001 CLIENT SPECIFIED INTO TABLE it_1001_c
  FOR ALL ENTRIES IN it_temp_s
  WHERE mandt = sy-mandt
    AND otype = 'S'
    AND objid = it_temp_s-sobid
    AND plvar = '01'
    AND rsign = 'B'
    AND relat = '007'
    AND begda LE sy-datum
    AND endda GE sy-datum.
SORT it_1001_c[] BY objid.
DELETE ADJACENT DUPLICATES FROM it_1001_c COMPARING objid.

  v_plans_rmmgr  = wa_zhrato_hdr-plans_rmmgr.

  v_plans_fnhd   = wa_zhrato_hdr-plans_fnhd.

  v_plans_rgmgr  = wa_zhrato_hdr-plans_rgmgr.

  IF wa_zhrato_hdr-zbusiness IS NOT INITIAL.
    SELECT SINGLE stext FROM hrp1000 CLIENT SPECIFIED INTO wa_final-business
         WHERE mandt = sy-mandt
           AND objid = wa_zhrato_hdr-zbusiness.

  ENDIF.

********************Additing HUBHRHD Name, Hub HR, Corporate Hr
  IF v_plans_rmmgr IS NOT INITIAL.

    SELECT SINGLE sobid FROM hrp1001 CLIENT SPECIFIED INTO v_pernr_hub_buhr
              WHERE mandt = sy-mandt
                AND objid = v_plans_rmmgr
                AND sclas = 'P' .
    v_hubhr_pernr = v_pernr_hub_buhr.
    SELECT SINGLE ename FROM pa0001 CLIENT SPECIFIED INTO wa_final-hub_buhr
           WHERE mandt = sy-mandt
             AND pernr = v_hubhr_pernr
             AND begda LE sy-datum
             AND endda GE sy-datum.
    CLEAR : v_hubhr_pernr,  v_plans_rmmgr,v_pernr_hub_buhr .
  ENDIF.
  IF v_plans_fnhd IS NOT INITIAL.

    SELECT SINGLE sobid FROM hrp1001 CLIENT SPECIFIED INTO v_pernr_hubhrhd
             WHERE mandt = sy-mandt
               AND objid = v_plans_fnhd
               AND sclas = 'P' .
    v_hubhrhd_pernr   = v_pernr_hubhrhd.
    SELECT SINGLE  ename FROM pa0001 CLIENT SPECIFIED INTO wa_final-hubhrhd
         WHERE mandt = sy-mandt
           AND pernr =  v_hubhrhd_pernr
           AND begda LE sy-datum
           AND endda GE sy-datum.

    CLEAR: v_hubhrhd_pernr, v_plans_fnhd,  v_pernr_hubhrhd.
  ENDIF.


  IF v_plans_rgmgr IS NOT INITIAL.

    SELECT SINGLE sobid FROM hrp1001 CLIENT SPECIFIED INTO v_pernr_buhrhd
        WHERE mandt = sy-mandt
          AND objid = v_plans_rgmgr
          AND sclas = 'P' .
    v_buhrhd_pernr   = v_pernr_buhrhd.

    SELECT SINGLE  ename FROM pa0001  CLIENT SPECIFIED INTO wa_final-buhrhd
         WHERE mandt = sy-mandt
           AND pernr = v_buhrhd_pernr
           AND begda LE sy-datum
           AND endda GE sy-datum.

    CLEAR: v_buhrhd_pernr,  v_plans_rgmgr, v_pernr_buhrhd.

  ENDIF.

These above few lines of code is taking really long time. Anyway of merging these 3+3 select queries into one single one.

Thanks in advance

Uday Kanike

[kakshat]

Hi Uday,

My assumption is that you are talking about the SELECT statements marked in bold. Have you already tried a join on HRP1001 and PA0001? Something like below?

SELECT SINGLE b~ename

  FROM hrp1001 as a INNER JOIN pa0001 as b

  INTO it_ename    

  ON a~sobid = b~pernr

  WHERE a~objid IN it_new    " it_new is a table of OBJIDs

    a~sclas = 'P'       AND

        b~begda <= sy-datum AND

    b~endda >= sy-datum.

[udaykumar_kanike]

Hi Akshat,

           I dont think join statement is recommended by SAP anymore because it only decreases the program performance. I am looking to improve my program's performance. As per latest netweaver ABAP programming techniques, usage of join, corresponding fields etc are least recommended.

Any other help would be much appreciated.

Thanks

Uday

[kakshat]

Really? I am not sure if it would have lower performance vis-a-vis so many selects.

[volker_borowski2]

Hi,

selecting HRP1001 without OTYPE in WHERE will result in a skip-scan or worse for DB execution.

Include

OTYPE =

to WHERE  clause

and it will do primary KEY range scan. -> Mission completed.

See the very same objid selects above that perform fine. OTYPE is specified.

Why on earth do you think a join is not recommended (any more)?

Volker

[udaykumar_kanike]

Hi Volker,

         I do agree that not mentioning OTYPE in select single queries was my mistake which was taking time. And, I really don't know if join statement was good or bad but once when I used, it didn't improve my program's performance. So, if you guys recommend then I will definitely try once in this scenario.

For now I have replaced with few more select queries instead of select single inside loop. That has considerably improved my program's performance.

Thanks

Uday