Skip to Content
author's profile photo Former Member
Former Member

Optimizing a Select Statement

Hi,

I have recently been asked to generate a program that reports of payroll postings to FI. This involves creating a giant select statement from the ppoix table to gather all the postings. My select statement is as follows:

SELECT pernr "EE Number

seqno "Sequential number

actsign "Indicator: Status of record

runid "Number of posting run

postnum "Number

tslin "Line number of data transfer

lgart "Wage Type

betrg "Amount

  • waers "Currency

  • anzhl "Number

  • meins "Base unit of measure

spprc "Special processing of posting items

  • momag "Transfer to FI/CO:EE grouping for acct determi

  • komok "Transfer to FI/CO: Symbolic account

  • mcode "Matchcode search term

  • koart "Account assignment type

  • auart "Expenditure type

  • nofin "Indicator: Expenditure type is not funded

INTO CORRESPONDING FIELDS OF TABLE i_ppoix

FROM ppoix

FOR ALL ENTRIES IN run_doc_xref

WHERE runid = run_doc_xref-runid

AND tslin = run_doc_xref-linum

AND spprc <> 'A'

AND lgart IN s_lgart

AND pernr in s_pernr.

where s_pernr is a select option that holds personnel nummbers and s_lgart is a select option that holds wagetypes. This statement works fine for a certain amount of personnel numbers and a certain amount of wagetypes, but once you exceed a certain limit the Database does not allow you to perform a select statement this large. Is there a better way to perform such a large select such as this one) ie: FM, or some other method I am not aware of. This select statement comes from the standard SAP delivered cost center admin report and this report dumps as well when too much data is passed to it.

any ideas would be much appreciated.

thanks.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 19, 2005 at 09:42 PM

    You can also try using CURSOR option (OPEN/FETCH/ CLOSE)... It will help...

    Regards

    Anjaiah

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Here is something for the CURSOR method.

      You will still have the same problem since there are too many entries in s_pernr.

      DATA:
        V_CURSOR TYPE CURSOR.
      
      OPEN CURSOR WITH HOLD V_CURSOR FOR
            <Your Select statement here>.
      
      DO.
        FETCH NEXT CURSOR S_CURSOR
          <INTO or APPENDING INTERNAL TABLE as you want it>
      * INTO will delete existing entries in targer ITAB
          PACKAGE SIZE 10000.  "This will bring 10,000 recs at
                               "a time
        IF SY-SUBRC NE 0.
          CLOSE CURSOR V_CURSOR.
          EXIT.   " Exit the DO loop
        ENDIF.
      * Processing . . . .
      * Don't use statement that can do a COMMIT or ROLLBACK.
      * A COMMIT or ROLLBACK will close the cursor
      ENDDO.
      

      Good luck.

      Cheers,

      -Ramesh

  • author's profile photo Former Member
    Former Member
    Posted on Oct 19, 2005 at 09:28 PM

    Try make 2 queries by spliting the select option values into half,

    ex.

    say s_pernr has low 1000 and high 5000

    so process the query for the first 2500 (1000 to 2500) records and write a second query for the remaining 2500 records (2501 till 5000 ).

    Second option is to put more conditions in the where clause

    Hope this helps

    Thanks

    Advait.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 19, 2005 at 09:36 PM

    Did you try with the logical database approach ?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 19, 2005 at 10:23 PM

    Jamie,

    Hope these pointers help:

    1.) Please don't use INTO CORRESPONDING FIELDS OF TABLE

    DEFINE the table with the exact fields and use

    INTO TABLE

    2.) Always check if internal table "run_doc_xref" has

    any values in it before the select. If this internal

    table is blank, then all the records from table PPOIX

    will be read.

    Can you please post:

    1.) How many records do you have in table PPOIX in your

    production system?

    2.) How are the select options being entered?

    As ranges or as individual values?

    Thanks,

    -Ramesh

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 21, 2005 at 05:20 PM

    Hi,

    Is your problem resolved?

    If any of the suggestions helped, please award points and close the question.

    Regards,

    -Ramesh

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 21, 2005 at 07:36 PM

    This is not efficient, but it should work:

    REPORT ztest NO STANDARD PAGE HEADING LINE-SIZE 255.
    
    TABLES:         ppoix.
    SELECT-OPTIONS: s_pernr FOR ppoix-pernr.
    RANGES:         r_pernr FOR ppoix-pernr.
    DATA:           no_lines LIKE sy-tabix.
    
    DESCRIBE TABLE s_pernr LINES no_lines.
    
    * Use 1 for testing. Actual number depends on system.
    IF no_lines > 1.
      r_pernr[] = s_pernr[].
      REFRESH s_pernr.
      CLEAR   s_pernr.
    ELSE.
      REFRESH r_pernr.
      CLEAR   r_pernr.
    ENDIF.
    
    SELECT * FROM ppoix
      WHERE pernr IN s_pernr.
      CHECK ppoix-pernr IN r_pernr.
    ENDSELECT.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.