topic Re: Optimizing Select statement in Application Development and Automation Discussions

Optimizing Select statement

Former Member — Fri, 30 Jan 2009 02:25:14 GMT

Hi,

Any ideas on how to use 'select for all entries' (or equivalent) syntax to get single record even if i have multiple records in the db.

Scenario:

internal table has multiple entries and each entry in turn might have multiple records which are relevant. However, single hit/one db record would give me the required details.

Thanks,

Gopal

Internal table

Group1

Group2

ZMAPTABLE

Group1___area1___code1

Group1___area2___code1

Group2___area1___code2

Group3___area2___code2

Result after doing select

Group1__code1

Group2__code2

Re: Optimizing Select statement

Former Member — Fri, 30 Jan 2009 02:39:45 GMT

Hi gopal,

use the select single statement.

Thanks and Regards,

Sai

Re: Optimizing Select statement

Former Member — Fri, 30 Jan 2009 02:51:49 GMT

can i retrieve all records i wanted with out multiple iterations?

Re: Optimizing Select statement

Former Member — Fri, 30 Jan 2009 03:01:42 GMT

For your requirement I think SELECT SINGLE should work.

SELECT SINGLE <DB TABLE FIELDS>

FROM <DB TABLE>

INTO <INTERNAL TABLE FIELDS>

WHERE <CONDITION>.

No need to have ENDSELECT for SELECT SINGLE.

If you want to fetch one entry for a given condition with one SELECT statement, use this,

SELECT <DB TABLE FIELDS>

FROM <DB TABLE>

INTO TABLE <INTERNAL TABLE NAME>

WHERE <CONDITION>

Above statement will fetch all the records in one query from the db table.

But you should give condition such that it will select only one record of each type.

For SELECT .. ALL ENTRIES, check syntax in ABAP help.

Let me know if your requirement is different.

Mubeen

Edited by: Mubeen Ahmed on Jan 30, 2009 4:03 AM

Re: Optimizing Select statement

Former Member — Fri, 30 Jan 2009 03:06:24 GMT

use like this


select single field1 field3 from zmaptable into itab1
for all entries in itab
where field1 = itab-field1.

кu03B1ятu03B9к

Edited by: kartik tarla on Jan 30, 2009 8:38 AM

Re: Optimizing Select statement

Former Member — Fri, 30 Jan 2009 03:12:38 GMT

HI Gopal,

Sure u can..

Try this..

Select single * from <DB> into itab for all entries in <Itab1>

where <field1> = <itab1-field1>.

Regards,

Sai

Edited by: Saikumar on Jan 30, 2009 8:44 AM

Re: Optimizing Select statement

Former Member — Fri, 30 Jan 2009 04:22:50 GMT

Hello Gopal,

concept of for all entries is.

if you have a three table in data base and want to fetch record from them using for all entries

than at first check the field combinaltion in all table .you will find the combination from the table that

one table is there that field is using in both the table .make the base table to it and pass the select queries

like:


select single  field name from data base table name into internal table
for all entries in it_temp
where field1 = it_temp-field1.

it_temp is the table where record has come from the select queries in which you have find the comman field that is in both the table.

For all entries always use when we have more than two table.




*&---------------------------------------------------------------------*
*& Report  ZTG_JOINS
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  ZTG_JOINS.

*&--------------------JOIN PERFORMANCE---------------------------------*
TABLES : zvbak, zvbap, zmara.

TYPES : BEGIN OF itab,
        vbeln TYPE zvbak-vbeln,
        vkorg TYPE zvbak-vkorg,
        vtweg TYPE zvbak-vtweg,
        spart TYPE zvbak-spart,
        posnr TYPE zvbap-posnr,
        matnr TYPE zvbap-matnr,
        matkl TYPE zvbap-matkl,
        mbrsh TYPE zmara-mbrsh,
        meins TYPE zmara-meins,
        END OF itab.

DATA : it_sales TYPE STANDARD TABLE OF itab,
       wa_sales TYPE itab.

START-OF-SELECTION.

  SELECT a~vbeln a~vkorg a~vtweg a~spart b~posnr b~matnr b~matkl c~mbrsh c~meins
  INTO TABLE it_sales
  FROM zvbak AS a
  INNER JOIN zvbap AS b
  ON a~vbeln = b~vbeln
  INNER JOIN zmara AS c
  ON c~matnr = b~matnr.

END-OF-SELECTION.
  WRITE : /1 'VBELN',10 'VKORG',19 'VTWEG',29 'SPART',40 'POSNR',50 'MATNR',59 'MATKL',69 'MBRSH',79 'MEINS'.
  ULINE.
  LOOP AT it_sales INTO wa_sales.
    WRITE : /1 wa_sales-vbeln, 10 wa_sales-vkorg,20 wa_sales-vtweg,30 wa_sales-spart,40 wa_sales-posnr,50 wa_sales-matnr,60 wa_sales-matkl,70 wa_sales-mbrsh,80 wa_sales-meins.
  ENDLOOP.

Hope this code will help you.

Thanks

Arun Kayal

Re: Optimizing Select statement

Former Member — Fri, 30 Jan 2009 04:45:38 GMT

Check this link for details about [Select|http://help.sap.com/abapdocu/en/ABAPSELECT.htm] statement.

[Optimising Select|http://www.erpgenie.com/component/content/article/566].

Hope this helps

Regards,

Jayanthi.K

Re: Optimizing Select statement

Former Member — Fri, 30 Jan 2009 04:50:56 GMT

Hi Gopal,

Use For all entries of table which fetches unique entries from the table

select group code from table zmaptable for all entries of table <table internal table name>

where condition

Regards,

Janakiram.

Re: Optimizing Select statement

Former Member — Fri, 30 Jan 2009 07:00:36 GMT

Hi,

Refer to this following,

[http://www.sap-basis-abap.com/abap/abap-4-development-code-efficiency-guidelines.htm]

SELECT persnr FROM pers INTO TABLE ipers WHERE cond. u2026u2026u2026.

SELECT * FROM persproj FOR ALL ENTRIES IN ipers

WHERE person = ipers-persnr

u2026u2026u2026... process .u2026u2026u2026u2026u2026

ENDSELECT.

Regards,

Annevit.