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

Select Statement

Hello

iam using a select statement in my report and its taking a long time to fetch the data and thus ending up giving a short dump..The code is as below..Could you please help with solving this problem..

<b>SELECT VBELV VBTYP_V INTO (ITAB_VBAK-VBELV, ITAB_VBAK-VBTYP_V)

FROM VBFA UP TO 1 ROWS

WHERE VBELN = ITAB_OPEN-DOCNO.

IF ITAB_VBAK-VBTYP_V <> 'C'.

SELECT SINGLE VBELV INTO (ITAB_VBAK-VBELV)

FROM VBFA "UP TO 1 ROWS

WHERE VBELN = ITAB_VBAK-VBELV.

  • ENDSELECT. "select VBFA

ENDIF. "IF itab

SELECT VBELN BSTNK KUNNR INTO (ITAB_VBAK-VBELN, ITAB_VBAK-BSTNK,

ITAB_VBAK-SOLDTO)

FROM VBAK UP TO 1 ROWS

WHERE VBELN = ITAB_VBAK-VBELV.

IF SY-SUBRC = '0'.

MOVE ITAB_VBAK-VBELN TO ITAB_OPEN-ORDNO. "Order Number

MOVE ITAB_VBAK-BSTNK TO ITAB_OPEN-BSTNK. "Purchase Order

MOVE ITAB_VBAK-SOLDTO TO ITAB_OPEN-SOLDTO. "SoldTo

  • Load first Benteler Material Number - PBE

MOVE ' ' TO ITAB_OPEN-MATNR.

SELECT MATNR INTO (ITAB_OPEN-MATNR)

FROM VBRP UP TO 1 ROWS

  • where vbeln = itab_vbak-vbelv.

WHERE VBELN = ITAB_OPEN-DOCNO.

ENDSELECT.

PERFORM TEXT_RETRIEVAL."Get first line PO Text

IF SY-SUBRC = '0'.

LOOP AT ITAB.

MOVE ITAB-TDLINE TO ITAB_OPEN-TDLINE. "First Line of Text

EXIT.

ENDLOOP. "Loop at ITAB

ENDIF. "IF sy-subrc

ENDIF. "IF sy-subrc

ENDSELECT. "select VBAK

ENDSELECT. "select VBFA</b>

Thanks

Vicky

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

7 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 06, 2006 at 02:18 PM

    Try SELECT SINGLE instead of using UP TO 1 ROWS.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Select single vs select up to 1 rows shouldn't make much of a difference, except select single without specifying the entire key will show up on an extended program check.

      Move your "IF SY-SUBRC = 0" after the ENDSELECT for starters. By the way, you don't need the single quote around the 0.

  • Posted on Sep 06, 2006 at 02:24 PM

    Hi Vicky!

    Would be nice to know, how you fill itab_open-docno. Because the document index VBFA should be used to search subsequent documents (having the first, e.g. VBAK, and searching the second, e.g. VBRK).

    But the other way around is often much easier: in most documents the preceding document number is stored (e.g. VBRP-VGBEL and VBRP-VGPOS). Then direct access to VBAK is possible.

    Currently you have a non-index access to VBFA, this will never get fast (select single or up to 1 rows won't change performance, just readability).

    Regards,

    Christian

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 06, 2006 at 02:25 PM

    VBFA is a large table and you are selecting it without using the key fields. Since you know VBELN, I suggest you go to the original table to get the preceding document number.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Vicky - I think you're going about this the wrong way. Table VBFA is the sales document flow table. If you give it a preceding document (such as an order), you will get the subsequent documents (such as a delivery or debit memo request). If you try to do it the other way (get the preceding document from the subsequent document), you cannot use the index, because there are no secondary indexes on this table.

      Having the flow work this way makes sense. If you have an order, it may have many deliveries and each delivery may have many accounting documents, so you cannot attach all of these subsequent documents directly to the order. So you need a table like VBFA to hold these relationships.

      But if you have a subsequent document like a delivery item, it can be for only one sales order item, so the preceding document can be put directly on that item.

      So with this in mind, I think you can do something like:

      Using BSID-VBELN (the billing document for the accounting document) go to the billing table VBRP-VBELN and get the sales document VBRP-AUBEL.

      Depending on the document type of this document, you may have to go to one or more other tables to get the remaining preceding documents.

      This is quite a bit more complicated that going to VBFA, but I think it's the correct (SAP) way.

      Rob

  • author's profile photo Former Member
    Former Member
    Posted on Sep 06, 2006 at 02:27 PM

    hi,

    The following points improves performance a lot.

    1) Do not use SELECT .. ENDSELECT. Instead make of INTO TABLE ITAB.

    2) Use select single ... instead of select..up to 1 rows.

    Regards,

    Sailaja.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 06, 2006 at 02:36 PM

    What type of document number is in ITAB_OPEN-DOCNO?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 06, 2006 at 02:42 PM

    hi,

    We can force the select to execute with the specified secondary index. It can be done using HINTS statement. But HINTS vary from one backend to other backend.

    Here is an example where oracle is the backend system.

    please refer to Note 129385 in OSS.

    SELECT carrid connid cityfrom

    FROM spfli INTO (xcarrid, xconnid, xcityfrom)

    WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'

    %_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

    WRITE: / xcarrid, xconnid, xcityfrom.

    ENDSELECT.

    More than 4 fields should not be specified in secondary index. Creation of multiple secondary indexes increases the load on database.

    You can search a table for data records that satisfy certain search criteria faster using an index. An index can be considered a copy of a database table that has been reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example using a binary search.

    The index also contains a pointer to the corresponding

    record of the actual table so that the fields not contained in the index can also be read. The primary index is distinguished from the secondary indexes of a table. The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

    You can also create further indexes on a table in the ABAP Dictionary. These are called secondary indexes. This is necessary if the table is frequently accessed in a way that does not take advantage of the sorting of the primary index for the access.

    Points to keep in mind while creating indexes:

    1) The order of the fields in the index is very important for the accessing speed.

    2) The first fields

    should be those which have constant values for a large number of selections. During selection, an index is only of use up to the first unspecified field.

    3) Only those fields that significantly restrict the set of results in a selection make sense for an index.

    4) Additional indexes can also place a load on the system since they must be adjusted each time the table contents change. Each additional index therefore slows down the insertion of records in the table. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.

    5) A unique index for a client-dependent table must contain the client field.

    6) Several indexes on the same table are distinguished by a three-place index identifier. The index identifier may only contain letters and digits. The ID 0 is reserved for the primary index. The index name on the database adheres to the convention <Table name>~<Index ID>.

    Ex: TEST~A is the name of the corresponding database index in the database for table TEST and the secondary index with ID A.

    Regards,

    Sailaja.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 06, 2006 at 08:21 PM

    hi

    SELECT VBELV VBTYP_V INTO (ITAB_VBAK-VBELV, ITAB_VBAK-VBTYP_V)
    FROM VBFA UP TO 1 ROWS
    WHERE VBELN = ITAB_OPEN-DOCNO.

    <b>endselect.</b> " This is misssing

    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.