Skip to Content
0
Former Member
Oct 19, 2005 at 09:10 PM

Optimizing a Select Statement

171 Views

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.