SELECT examples

Internal Table Definitions

To reserve space for an internal table to be used in a select statement a header line must be defined. To define an internal table with a header line, you must include either "begin of":

data: begin of t_bkpf occurs 0,
	bukrs like bkpf-bukrs,
	belnr like bkpf-belnr,
	gjahr like bkpf-gjahr,
	blart like bkpf-blart,
	budat like bkpf-budat,
end of t_bkpf.

or "with header line":

data: t_bkpf like bkpf occurs 0 with header line.

in the definition. You can leave the header line off:

data: t_coss like coss occurs 0.

if you read the internal table into the table work area:

loop at t_coss into coss.

after having selected using the keyword, table (see below).

The first option is preferable because it takes up less memory to specify only the columns of interest and it improves database performance for non-buffered Transparent tables.

select bukrs belnr gjahr blart budat from bkpf into table t_bkpf
where bukrs = c_bukrs
and bstat = space
and budat in s_budat

works because the first five fields in t_bkpf match exactly with the five columns in the select statement. This select option does a single assignment for the entire row. If the keyword, table, is there the entire row is placed into the body of the internal table. If you leave off the keyword, table, the row goes into the header line of t_bkpf and you have to append the row to the table.

Use corresponding fields:

select belnr gjahr blart budat from bkpf into corresponding fields of table t_bkpf
where bukrs = c_bukrs
and bstat = space
and budat in s_budat

when there is not an exact correspondence. There is overhead with "corresponding fields" because it performs one assignment per field instead of a single assignment per row, so try to use the exact match.

First choice:
select bukrs belnr gjahr blart budat from bkpf into table t_bkpf

Second choice:
select belnr gjahr blart budat from bkpf into corresponding fields of table t_bkpf

Select Single

select single name1 ktokk from lfa1 into (w_vname1, w_ktokk)

or

select single name1 from kna1 into w_cname1

or

* internal structure (no occurs - not a table) for parent WBS master data
data: begin of t_parent,
        pspnr like prps-pspnr,         " WBS element internal rsn
        posid like prps-posid,         " WBS element external number
        stufe like prps-stufe,         " hierarchy level (1 is top)
        fakkz like prps-fakkz,         " billing indicator (X=yes)
        zbillt like prps-zbillt,       " billing type (01=cost reimburs)
        zspon like prps-zspon,         " customer ID
        psphi like prps-psphi,         " number of project def. internal
        zend   like prps-zend,         " expiration date
        ztermcd like prps-ztermcd,     " term code
      end of t_parent.

        select single
            pspnr posid stufe fakkz zbillt zspon psphi zend ztermcd
            from prps into t_parent
            where pspnr eq t_prps-up.

Where Clause and Indexes

You should ensure that fields in a where clause are always supported by an index, either a primary index or a secondary index. Secondary indexes are not available for cluster tables such as BSEG or pooled tables such as configuration tables & match code ids.

The columns belonging to a primary index have an 'X' under "Key" in the Dictionary: Table/Strucutre: Display Fields screen (SE11). They will always be the initial columns in a table.

Secondary indexes can be found from:

Dictionary: Table/Strucutre: Display Fields screen (SE11)->Indexes...Indexes for Table->choose an index and press enter- this will list the fields involved with a specific secondary index.

SE11 (Dictionary: Table/Strucutre: Display Fields screen)->Utilities->Database Utility->Extras->Database Object->Display will list all the fields and indexes for a table. Navigate to the bottom for the indexes.

xxxx_____0 is the primary unique index. xxxx______1, etc are the secondary indexes.

"To choose an index, the optimizer looks at the field names in the where clause and then it looks for an index having the same field names in the same order as they were specified in the where clause. Therefore, to ensure the system chooses the index you intend, specify the fields in the where clause in the same order as they appear in the index.... Commonly accesses SAP tables with their indexes.

If a table begins with MANDT and an index doesn't, the optimizer might not use the index." (from Teach Yourself ABAP/4 in 21 Days by Ken Greenwood)

So why does the secondary index in COBK not contain MANDT?