Select based on period

[Former Member]

Hi all,

I have a select option for period and I need to select all SO document which fall whithin the period on the selection screen.

For example : if user key in period 07, program need to filter and select only for SO with month July.

I write as this :

select * into corresponding fields of table itb

from ekko.

check ekko-bukrs eq p_bukrs and

ekko-bedat+4(2) in s_monat.

append itb.

As there's a lot of data in the table, so it's running slow.

Is there any other way to code ?

How can i write my code for the selection?

Please kindly help me on this.

Thanks in advance.

Pompoug

[Former Member]

Hi all,

I have a select option for period and I need to select all SO document which fall whithin the period on the selection screen.

For example : if user key in period 07, program need to filter and select only for SO with month July.

I write as this :

select * into corresponding fields of table itb

from ekko.

check ekko-bukrs eq p_bukrs and

ekko-bedat+4(2) in s_monat.

append itb.

As there's a lot of data in the table, so it's running slow.

Is there any other way to code ?

How can i write my code for the selection?

Please kindly help me on this.

Thanks in advance.

Pompoug

[gopi_narendra]

Hi Pompougnia,

You can include all the validations on one select statement

select * from EKKO
            into corresponding fields of table itab
            where bukrs = p_bukrs
               and bedat+4(2) in so_monat.

Not necessary to use the append statement as data is already in the itab.

Regards

Gopi

[Former Member]

Instead of using INTO CORRESPONDING TABLE, declare an internal table with all the fields you need and simply use

SELECT ebeln bukrs bstyp from ekko INTO table itab

where bukrs eq p_bukrs

and ekko-bedat+4(2) in s_monat.

Also this is much better than SELECT *.

Also as told earlier you neednt append it again.

Sri

[Former Member]

It would be nice if people answering questions actually checked their answers before posting.

You cannot use BEDAT4(2) in the where block of a select statement - it will simply give a syntax error. Field "BEDAT4(2)" unknown.

To get what you want you need to program some additional logic before the select statement and then select from the table using an appropriate set of values in the WHERE block.

You should NEVER code a select without any where conditions as you have done. This form of select will read every entry in the table and has very bad performance implications.

So what you need to do to write an efficient select is:

1. Work out what is the primary key or index on the table that is most appropriate for the selection you want to do. (Look in SE11 to see this)

2. See if you have all the relevant field values to perform the select using the chosen (or any available) index / key. If not you may have to get these values - either as constants, as values input by the user on the selection screen, by preliminary reads from other tables, or by calculation / manipulation in your program.

3. Build the program logic to perform the select.

4. Test the selection out - use SE30 to see how much database time the program uses and use ST05 to see the details of the database access and the SQL access path. Make sure it is using some form of key or index read or range scan based on reasonable fields, not a full table scan.

In your case you need to convert the period you have into two dates before the select is called. Do this using simple CASE style logic (when '01'. datel=firstday. dateh = lastday. when '02'. etc.), by calculation based on the dates (concatenate sy-datum+0(4) p_monat '01' into datel. etc), or by calling an appropriate function module to get first/last days in the period.

Once you have these two dates you can SELECT ... WHERE bedat BETWEEN datel AND dateh. You will probably need additional field in this WHERE block to get an efficient match with the table indexes. I dont have a system where I can see this detail at present.

Hope this helps improve the program

Andrew

[Former Member]

Hi Pompoug,

Two big things slowing you down.

First, Select * into corresponding fields of table. Using * brings back alot of unnecessary data as well as the use of "corresponding fields of". Specify the fields you want and create the table with the same fields in the same order.

Without a WHERE clause, you are doing a full table scan, so you must have a where clause. This is your biggest issue with performance.

You should calculate your period into a begin and end date. I'm not logged on so I don't know for sure, there may be functions that given a period, will return the first of the month and last of the month. You can create a routine yourself or you might find that someone has already done it at your site.

So your statement should look like:

"perform routine to calculate begindat and enddat based on monat

select fld1 fld2 fld3 into table itab

from ekko

where ekko-bukrs eq p_bukrs

and ekko-bedat >= begindat

and ekko-bedat <= enddat.

Hope this helps.

Filler

[Former Member]

Hi pompougnia,

1)You have to avoid using of "into corresponding fields of table" as this will take much time.

Soln: Declare the itab with the required fields.

2)Modify your query like:

select ~required fields into table itab

from ekko where bukrs eq p_bukrs and

(bedat ge s_monat-low and

bedat le s_monat-high).

Regards,

Sheron

[Former Member]

Even if you can convert the posting period (monat) into a date (bedat), you will still not be able to use the index because bedat is the second field of the index. To use the index effectively, you also have to include the purchasing document category (BSTYP) in the WHERE. You can do this even if you don't want to restrict the SELECT based on this by including all possible values.

REPORT ztest LINE-SIZE 80 MESSAGE-ID 00.

TABLES: ekko, bkpf.

DATA: itb TYPE STANDARD TABLE OF ekko WITH HEADER LINE.

PARAMETERS:     p_bukrs LIKE ekko-bukrs.
SELECT-OPTIONS: s_monat FOR bkpf-monat.

RANGES:         r_bedat FOR ekko-bedat.

PERFORM convert_periods_to_dates
  TABLES s_monat
         r_bedat.

SELECT * FROM ekko
  INTO CORRESPONDING FIELDS OF TABLE itb
  WHERE bstyp IN ('A', 'F', 'K', 'L')
    AND bedat IN s_monat
    AND bukrs EQ p_bukrs.

*&---------------------------------------------------------------------*
*&      Form  convert_periods_to_dates
*&---------------------------------------------------------------------*
*       Here you have to put in some logic to convert the posting
*       period SELECT-OPTION to a range of dates. This can be simplified
*       if only one is allowed. You will also have to consider the year.
*----------------------------------------------------------------------*
FORM convert_periods_to_dates
  TABLES   monat STRUCTURE s_monat
           bedat STRUCTURE r_bedat.

ENDFORM.                    " convert_periods_to_dates

Rob

[Former Member]

Building on Rob's code, you can get the dates using the following BAPI:

FUNCTION BAPI_CCODE_GET_FIRSTDAY_PERIOD.
*"----------------------------------------------------------------------
*"*"Lokale Schnittstelle:
*"       IMPORTING
*"             VALUE(COMPANYCODEID) LIKE  BAPI0002_2-COMP_CODE
*"             VALUE(FISCAL_PERIOD) LIKE  BAPI0002_4-FISCAL_PERIOD
*"             VALUE(FISCAL_YEAR) LIKE  BAPI0002_4-FISCAL_YEAR
*"       EXPORTING
*"             VALUE(FIRST_DAY_OF_PERIOD)
*"                             LIKE  BAPI0002_4-POSTING_DATE
*"             VALUE(RETURN) LIKE  BAPIRETURN1 STRUCTURE  BAPIRETURN1

With this you can get the starting date of the period you are interested in and the starting date of the following period. To do this properly, you will also need to supply the fiscal year, which should probably be part of your selections in any case.

You can then adjust the starting date of the following period to get the ending date of the current period by subtracting 1 from the date.

Good luck

Brian