topic Re: Select statements in Application Development and Automation Discussions

Select statements

Former Member — Thu, 30 Aug 2007 09:53:15 GMT

Hi there,

could anybody send me some material about using select statements, inner- and outer joins, etc...

My email address is: bstripes81@gmail.com

Thanks in advance.

Re: Select statements

kiran_k8 — Thu, 30 Aug 2007 09:58:01 GMT

Stripes,

SELECT result

FROM source

INTO|APPENDING target

[[FOR ALL ENTRIES IN itab] WHERE sql_cond]

...

Effect

SELECT is an Open-SQL-statement for reading data from one or several database tables into data objects.

The select statement reads a result set (whose structure is determined in result ) from the database tables specified in source, and assigns the data from the result set to the data objects specified in target. You can restrict the result set using the WHERE addition. The addition GROUP BY compresses several database rows into a single row of the result set. The addition HAVING restricts the compressed rows. The addition ORDER BY sorts the result set.

The data objects specified in target must match the result set result. This means that the result set is either assigned to the data objects in one step, or by row, or by packets of rows. In the second and third case, the SELECT statement opens a loop, which which must be closed using ENDSELECT. For every loop pass, the SELECT-statement assigns a row or a packet of rows to the data objects specified in target. If the last row was assigned or if the result set is empty, then SELECT branches to ENDSELECT . A database cursor is opened implicitly to process a SELECT-loop, and is closed again when the loop is ended. You can end the loop using the statements from section leave loops.

Up to the INTO resp. APPENDING addition, the entries in the SELECTstatement define which data should be read by the database in which form. This requirement is translated in the database interface for the database system´s programming interface and is then passed to the database system. The data are read in packets by the database and are transported to the application server by the database server. On the application server, the data are transferred to the ABAP program´s data objects in accordance with the data specified in the INTO and APPENDING additions.

System Fields

The SELECT statement sets the values of the system fields sy-subrc and sy-dbcnt.

sy-subrc Relevance

0 The SELECT statement sets sy-subrc to 0 for every pass by value to an ABAP data object. The ENDSELECT statement sets sy-subrc to 0 if at least one row was transferred in the SELECT loop.

4 The SELECT statement sets sy-subrc to 4 if the result set is empty, that is, if no data was found in the database.

8 The SELECT statement sets sy-subrc to 8 if the FOR UPDATE addition is used in result, without the primary key being specified fully after WHERE.

After every value that is transferred to an ABAP data object, the SELECT statement sets sy-dbcnt to the number of rows that were transferred. If the result set is empty, sy-dbcnt is set to 0.

Notes

Outside classes, you do not need to specify the target area with INTO or APPENDING if a single database table or a single view is specified statically after FROM, and a table work area dbtab was declared with the TABLES statement for the corresponding database table or view. In this case, the system supplements the SELECT-statement implicitly with the addition INTO dbtab.

Although the WHERE-condition is optional, you should always specify it for performance reasons, and the result set should not be restricted on the application server.

SELECT-loops can be nested. For performance reasons, you should check whether a join or a sub-query would be more effective.

Within a SELECT-loop you cannot execute any statements that lead to a database commit and consequently cause the corresponding database cursor to close.

SELECT - result

Syntax

... lines columns ... .

Effect

The data in result defines whether the resulting set consists of multiple rows (table-like structure) or a single row ( flat structure). It specifies the columns to be read and defines their names in the resulting set. Note that column names from the database table can be changed. For single columns, aggregate expressions can be used to specify aggregates. Identical rows in the resulting set can be excluded, and individual rows can be protected from parallel changes by another program.

The data in result consists of data for the rows lines and for the columns columns.

SELECT - lines

Syntax

... { SINGLE }

| { { } } ... .

Alternatives:

1. ... SINGLE

2. ... { }

Effect

The data in lines specifies that the resulting set has either multiple lines or a single line.

Alternative 1

... SINGLE

Effect

If SINGLE is specified, the resulting set has a single line. If the remaining additions to the SELECT command select more than one line from the database, the first line that is found is entered into the resulting set. The data objects specified after INTO may not be internal tables, and the APPENDING addition may not be used.

An exclusive lock can be set for this line using the FOR UPDATE addition when a single line is being read with SINGLE. The SELECT command is used in this case only if all primary key fields in logical expressions linked by AND are checked to make sure they are the same in the WHERE condition. Otherwise, the resulting set is empty and sy-subrc is set to 8. If the lock causes a deadlock, an exception occurs. If the FOR UPDATE addition is used, the SELECT command circumvents SAP buffering.

Note

When SINGLE is being specified, the lines to be read should be clearly specified in the WHERE condition, for the sake of efficiency. When the data is read from a database table, the system does this by specifying comparison values for the primary key.

Alternative 2

... { }

Effect

If SINGLE is not specified and if columns does not contain only aggregate expressions, the resulting set has multiple lines. All database lines that are selected by the remaining additions of the SELECT command are included in the resulting list. If the ORDER BY addition is not used, the order of the lines in the resulting list is not defined and, if the same SELECT command is executed multiple times, the order may be different each time. A data object specified after INTO can be an internal table and the APPENDING addition can be used. If no internal table is specified after INTO or APPENDING, the SELECT command triggers a loop that has to be closed using ENDSELECT.

If multiple lines are read without SINGLE, the DISTINCT addition can be used to exclude duplicate lines from the resulting list. If DISTINCT is used, the SELECT command circumvents SAP buffering. DISTINCT cannot be used in the following situations:

If a column specified in columns has the type STRING, RAWSTRING, LCHAR or LRAW

If the system tries to access pool or cluster tables and single columns are specified in columns.

Note

When specifying DISTINCT, note that you have to carry out sort operations in the database system for this.

SELECT - columns

Syntax

... *

| { {col1|aggregate( col1 )}

{col2|aggregate( col2 )} ... }

| (column_syntax) ... .

Alternatives:

1. ... *

2. ... {col1|aggregate( col1 )}

{col2|aggregate( col2 )} ...

3. ... (column_syntax)

Effect

The input in columns determines which columns are used to build the resulting set.

Alternative 1

... *

Effect

If * is specified, the resulting set is built based on all columns in the database tables or views specified after FROM, in the order given there. The columns in the resulting set take on the name and data type from the database tables or views. Only one data object can be specified after INTO.

Note

If multiple database tables are specified after FROM, you cannot prevent multiple columns from getting the same name when you specify *.

Alternative 2

... {col1|aggregate( col1 )}

{col2|aggregate( col2 )} ...

Effect

A list of column labels col1 col2 ... is specified in order to build the resulting list from individual columns. An individual column can be specified directly or as an argument of an aggregate function aggregate. The order in which the column labels are specified is up to you and defines the order of the columns in the resulting list. Only if a column of the type LCHAR or LRAW is listed does the corresponding length field also have to be specified directly before it. An individual column can be specified multiple times.

The addition AS can be used to define an alternative column name a1 a2 ... with a maximum of fourteen digits in the resulting set for every column label col1 col2 .... The system uses the alternative column name in the additions INTO|APPENDING CORRESPONDING FIELDS and ORDER BY. .

Column labels

The following column labels are possible:

If only a single database table or a single view is specified after FROM, the column labels in the database table - that is, the names of the components comp1 comp2... - can be specified directly for col1 col2 ... in the structure of the ABAP Dictionary.

If the name of the component occurs in multiple database tables of the FROM addition, but the desired database table or the view dbtab is only specified once after FROM, the names dbtab~comp1 dbtab~comp2 ... have to be specified for col1 col2 .... comp1 comp2 ... are the names of the components in the structure of the ABAP Dictionary.

If the desired database table or view occurs multiple times after FROM, the names tabalias~comp1 tabalias~comp2 ... have to be specified for col1 col2 .... tabalias is the alternative table name of the database table or view defined after FROM, and comp1 comp2 ... are the names of the components in the structure of the ABAP Dictionary.

The data type of a single column in the resulting list is the datatype of the corresponding component in the ABAP Dictionary. The corresponding data object after INTO or APPENDING has to be selected accordingly.

Note

If multiple database tables are specified after FROM, you can use alternative names when specifying single columns to avoid having multiple columns with the same name.

Example

Read specific columns of a single row.

DATA wa TYPE spfli.

SELECT SINGLE carrid connid cityfrom cityto

INTO CORRESPONDING FIELDS OF wa

FROM spfli

WHERE carrid EQ 'LH' AND connid EQ '0400'.

IF sy-subrc EQ 0.

WRITE: / wa-carrid, wa-connid, wa-cityfrom, wa-cityto.

ENDIF.

Alternative 3

... (column_syntax)

Effect

Instead of static data, a data object column_syntax in brackets can be specified, which, when the command is executed, either contains the syntax shown with the static data, or is initial. The data object column_syntax can be a character-type data object or an internal table with a character-type data type. The syntax in column_syntax, like in the ABAP editor, is not case-sensitive. When specifying an internal table, you can distribute the syntax over multiple rows.

If column_syntax is initial when the command is executed, columns is implicitly set to * and all columns are read.

If columns are specificied dynamically without the SINGLE addition, the resulting set is always regarded as having multiple rows.

Notes

Before Release 6.10, you could only specify an internal table with a flat character-type row type for column_syntax with a maximum of 72 characters. Also, before Release 6.10, if you used the DISTINCT addition for dynamic access to pool tables or cluster tables, this was ignored, but since release 6.10, this causes a known exception.

If column_syntax is an internal table with header line, the table body and not the header line is evaluated.

Example

Read out how many flights go to and from a city. The SELECT command is implemented only once in a sub-program. The column data, including aggregate function and the data after GROUP BY, is dynamic. Instead of adding the column data to an internal l_columns table, you could just as easily concatenate it in a character-type l_columns field.

PERFORM my_select USING `CITYFROM`.

ULINE.

PERFORM my_select USING `CITYTO`.

FORM my_select USING l_group TYPE string.

DATA: l_columns TYPE TABLE OF string,

l_container TYPE string,

l_count TYPE i.

APPEND l_group TO l_columns.

APPEND `count( * )` TO l_columns.

SELECT (l_columns)

FROM spfli

INTO (l_container, l_count)

GROUP BY (l_group).

WRITE: / l_count, l_container.

ENDSELECT.

ENDFORM.

SELECT - aggregate

Syntax

... { MAX( col )

| MIN( col )

| AVG( col )

| SUM( col )

| COUNT( DISTINCT col )

| COUNT( * )

| count(*) } ... .

Effect

As many of the specified column labels as you like can be listed in the SELECT command as arguments of the above aggregate expression. In aggregate expressions, a single value is calculated from the values of multiple rows in a column as follows (note that the addition DISTINCT excludes double values from the calculation):

MAX( col ) Determines the maximum value of the value in the column col in the resulting set or in the current group.

MIN( col ) Determines the minimum value of the content of the column col in the resulting set or in the current group.

AVG( col ) Determines the average value of the content of the column col in the resulting set or in the current group. The data type of the column has to be numerical.

SUM( col ) Determines the sum of the content of the column col in the resulting set or in the current group. The data type of the column has to be numerical.

COUNT( DISTINCT col ) Determines the number of different values in the column col in the resulting set or in the current group.

COUNT( * ) (or count(*)) Determines the number of rows in the resulting set or in the current group. No column label is specified in this case.

If you are using aggregate expressions, all column labels that are not listed as an argument of an aggregate function are listed after the addition GROUP BY. The aggregate functions evaluate the content of the groups defined by GROUP BY in the database system and transfer the result to the combined rows of the resulting set.

The data type of aggregate expressions with the function MAX, MIN or SUM is the data type of the corresponding column in the ABAP Dictionary. Aggregate expressions with the function AVG have the data type FLTP, and those with COUNT have the data type INT4. The corresponding data object after INTO or APPENDING has to be selected accordingly.

Note the following points when using aggregate expressions:

If the addition FOR ALL ENTRIES is used in front of WHERE, or if cluster or pool tables are listed after FROM, no other aggregate expressions apart from COUNT( * ) can be used.

Columns of the type STRING or RAWSTRING cannot be used with aggregate functions.

When aggregate expressions are used, the SELECT command makes it unnecessary to use SAP buffering.

Null values are not included in the calculation for the aggregate functions. The result is a null value only if all the rows in the column in question contain the null value.

If only aggregate expressions are used after SELECT, the results set has one row and the addition GROUP BY is not necessary. If a non-table type target area is specified after INTO, the command ENDSELECT cannot be used together with the addition SINGLE. If the aggregate expression count( * ) is not being used, an internal table can be specified after INTO, and the first row of this table is filled.

If aggregate functions are used without GROUP BY being specified at the same time, the resulting set also contains a row if no data is found in the database. If count( * ) is used, the column in question contains the value 0. The columns in the other aggregate functions contain initial values. This row is assigned to the data object specified after INTO, and unless count( * ) is being used exclusively, sy-subrc is set to 0 and sy-dbcnt is set to 1. If count( *) is used exclusively, the addition INTO can be omitted and if no data can be found in the database, sy-subrc is set to 4 and sy-dbcnt is set to 0.

K.Kiran.

Re: Select statements

Former Member — Thu, 30 Aug 2007 09:58:55 GMT

Re: Select statements

Former Member — Thu, 30 Aug 2007 09:59:17 GMT

Hi White,

Check these links.

SELECT statement

http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/select.htm

http://help.sap.com/saphelp_nw04/helpdata/en/62/10a423384746e8bf5f15ccdd36e8b1/content.htm

Inner/Outer Joins:

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

http://help.sap.com/saphelp_erp2004/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm

Thanks,

Vinay

Re: Select statements

Former Member — Thu, 30 Aug 2007 10:01:51 GMT

There are basically 3 types of SELECT statements :

1. To Read multiple rows from DB table into Work area (Row by row in a loop)

SELECT <FIELDS> FROM <DB TABLE> INTO <WA>.

ENDSELECT.

2. To read a single row from dB table into work area(Based on Primary key)

SELECT SINGLE <FIELDS> FROM <DBTABLE> into <WA> where <cond>.

3. To read a group of rows from DB table into Internal Table (Array fetch)

SELECT <fields> from <DBTABLE> into TABLE <ITAB>.

Both 2 and 3 are efficient performance wise.

SAP's OPEN SQL, syntax of the SELECT statement

Basic form:

SELECT result FROM source [INTO target] [WHERE condition] [GROUP BY fields] [ORDER BY order].

SELECT clause

Variants:

1. SELECT [SINGLE [FOR UPDATE] DISTINCT] *

2. SELECT [SINGLE [FOR UPDATE] DISTINCT] s1 ... sn

3. SELECT [SINGLE [FOR UPDATE] DISTINCT] (itab)

FROM clause

Variants:

1. ... FROM dbtab

Additions:

1. ... CLIENT SPECIFIED

2. ... BYPASSING BUFFER

3. ... UP TO n ROWS

2. ... FROM (dbtabname)

Additions:

1. ... CLIENT SPECIFIED

2. ... BYPASSING BUFFER

3. ... UP TO n ROWS

INTO target

(This form of the FROM clause works only in conjunction with the INTO clause.)

INTO clause

Variants:

1. ... INTO wa

2. ... INTO CORRESPONDING FIELDS OF wa

3. ... INTO (f1, ..., fn)

4. ... INTO TABLE itab

5. ... INTO CORRESPONDING FIELDS OF TABLE itab

6. ... APPENDING TABLE itab

7. ... APPENDING CORRESPONDING FIELDS OF TABLE itab

WHERE clause

Variants:

1. ... WHERE f op g

2. ... WHERE f [NOT] BETWEEN g1 AND g2

3. ... WHERE f [NOT] LIKE g

4. ... WHERE f [NOT] IN (g1, ..., gn)

5. ... WHERE f [NOT] IN itab

6. ... WHERE f IS [NOT] NULL

7. ... WHERE NOT cond

8. ... WHERE cond1 AND cond2

9. ... WHERE cond1 OR cond2

10. ... WHERE (itab)

11. ... WHERE cond AND (itab)

12. ... FOR ALL ENTRIES IN itab WHERE cond

Operator Meaning

EQ or = equal to

NE or < > not equal to

LT or < less than

LE or < = less than or equal to

GT or > greater than

GE or >= greater than or equal to

GROUP-BY clause

Variants:

1. ... GROUP BY f1 ... fn

2. ... GROUP BY (itab)

ORDER-BY clause

Variants:

1. ... ORDER BY PRIMARY KEY

2. ... ORDER BY f1 ... fn

3. ... ORDER BY (itab)

http://help.sap.com/saphelp_nw04/helpdata/en/62/10a423384746e8bf5f15ccdd36e8b1/content.htm

http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/abapindx.htm

http://www.erpgenie.com/abap/example_code.htm

_______________________________

Joins are used to fetch data fast from Database tables:

Tables are joined with the proper key fields to fetch the data properly.

If there are no proper key fields between tables don't use Joins;

Important thing is that don't USE JOINS FOR CLUSTER tableslike BSEG and KONV.

Only use for Transparenmt tables.

You can also use joins for the database VIews to fetch the data.

JOINS

... FROM tabref1 [INNER] JOIN tabref2 ON cond

Effect

The data is to be selected from transparent database tables and/or views determined by tabref1 and tabref2. tabref1 and tabref2 each have the same form as in variant 1 or are themselves Join expressions. The keyword INNER does not have to be specified. The database tables or views determined by tabref1 and tabref2 must be recognized by the ABAP Dictionary.

In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help the process of standardization (see relational databases). To regroup this information into a database query, you can link tables using the join command. This formulates conditions for the columns in the tables involved. The inner join contains all combinations of lines from the database table determined by tabref1 with lines from the table determined by tabref2, whose values together meet the logical condition (join condition) specified using ON>cond.

Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:

Table 1 Table 2


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	3	e2	f2	g2	h2
a3	b3	c3	2	4	e3	f3	g3	h3
a4	b4	c4	3

\ /

Inner Join


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	1	e1	f1	g1	h1
a4	b4	c4	3	3	e2	f2	g2	h2

Example

Output a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or a table alias.

Note

In order to determine the result of a SELECT command where the FROM clause contains a join, the database system first creates a temporary table containing the lines that meet the ON condition. The WHERE condition is then applied to the temporary table. It does not matter in an inner join whether the condition is in the ON or WHEREclause. The following example returns the same solution as the previous one.

Example

Output of a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID

WHERE F_{CONNID = P}CONNID

AND P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

Note

Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins that produce the same results on all of the supported database systems:

Only a table or view may appear to the right of the JOIN operator, not another join expression.

Only AND is possible in the ON condition as a logical operator.

Each comparison in the ON condition must contain a field from the right-hand table.

If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2.

Note

In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below:

Variant 3

... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond

Effect

Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary.

In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table.

Left outer join between table 1 and table 2 where column D in both tables set the join condition:

Table 1 Table 2


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	3	e2	f2	g2	h2
a3	b3	c3	2	4	e3	f3	g3	h3
a4	b4	c4	3

\ /

Left Outer Join


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	1	e1	f1	g1	h1
a3	b3	c3	2	NULL	NULL	NULL	NULL	NULL
a4	b4	c4	3	3	e2	f2	g2	h2

Example

Output a list of all custimers with their bookings for October 15th, 2001:

DATA: CUSTOMER TYPE SCUSTOM,

BOOKING TYPE SBOOK.

SELECT SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

SBOOK_{FLDATE SBOOK}CARRID SBOOK_{CONNID SBOOK}BOOKID

INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK

ON SCUSTOM_{ID = SBOOK}CUSTOMID AND

SBOOK~FLDATE = '20011015'

ORDER BY SCUSTOM_{NAME SBOOK}FLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or using an alias.

Note

For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

Only a table or view may come after the JOIN operator, not another join statement.

The only logical operator allowed in the ON condition is AND.

Each comparison in the ON condition must contain a field from the right-hand table.

Comparisons in the WHERE condition must not contain a field from the right-hand table.

The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).

Note

In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the llen in der FROM clause, according to the structure of each table work area. There can be gaps between the table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, as in the following example (not simply by counting the total number of fields).

Example

Example of a JOIN with more than two tables: Select all flights from Frankfurt to New York between September 10th and 20th, 2001 where there are available places, and display the name of the airline.

DATA: BEGIN OF WA,

FLIGHT TYPE SFLIGHT,

PFLI TYPE SPFLI,

CARR TYPE SCARR,

END OF WA.

SELECT * INTO WA

FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID )

INNER JOIN SCARR AS C

ON F_{CARRID = C}CARRID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,

WA-FLIGHT-CONNID.

ENDSELECT.

regards,

srinivas

Re: Select statements

Former Member — Thu, 30 Aug 2007 10:03:46 GMT

hi,

type select in abap editor and press F1 -> help there u can get a lot of info for select statement and follow same for inner join,..............

if helpful reward some points.

with regards,

Suresh Aluri.

Re: Select statements

Former Member — Thu, 30 Aug 2007 12:56:32 GMT

Thanks for the help.

Re: Select statements

Former Member — Thu, 30 Aug 2007 13:22:08 GMT

Hi,

Select statements

select * from mara

where spart = 'PA'

and not ( zatacd = space )

order by matnr.

Gets information from Confirmed schedule file (EKES)

Illustrates the usage of group by:

select ebeln eindt vbeln

into (po_ebeln, po_eindt, po_vbeln)

from ekes

where ebeln = wa_vbfa-vbeln

group by ebeln eindt vbeln.

Joining the vendor files (LFA1 and LFB1)

This outputs the fields into a table named TAB

select a1_{sortl a1}name1 a1_{name2 a1}pstlz a1_{ort01 a1}regio a1_{pfach a1}land1 a1_{lifnr a1}stras from lfa1 as a1 join lfb1 as b1 on a1_{lifnr = b1}lifnr into tab where a1_{lifnr in s_lifnr and a1}loevm <> 'X' and b1_{bukrs in s_bukrs and b1}loevm <> 'X'.

Joining MARA and MBEW

the material master and the accounting view by plant (gets plant cost for an item)

s_werks for mvke-dwerk obligatory. " Plant

select marc_{matnr marc}werks marc_{disgr mbew}salk3

into table t_marc

from marc join mbew on ( mbew_{bwkey = marc}werks and

mbew_{matnr = marc}matnr )

where werks in s_werks.

Joining VBAP and VBUP

This produces a list of open line items

loop at itab_vbak.

select

vbap_{vbeln vbap}posnr vbap_{matnr vbap}kwmeng vbap_{werks vbap}arktx

into bolines

from vbap join vbup

on ( vbap_{vbeln = vbup}vbeln and vbap_{posnr = vbup}posnr )

where vbap~vbeln = itab_vbak-vbeln

and lfsta = 'C'.

endselect.

Table ITAB_VBAK contains a list of open orders.

*Code to demonstrate select command

*Code to demonstrate select into internal table command

TYPES: BEGIN OF t_bkpf,

include structure bkpf.

bukrs LIKE bkpf-bukrs,

belnr LIKE bkpf-belnr,

gjahr LIKE bkpf-gjahr,

bldat LIKE bkpf-bldat,

monat LIKE bkpf-monat,

budat LIKE bkpf-budat,

xblnr LIKE bkpf-xblnr,

awtyp LIKE bkpf-awtyp,

awkey LIKE bkpf-awkey,

END OF t_bkpf.

DATA: it_bkpf TYPE STANDARD TABLE OF t_bkpf INITIAL SIZE 0,

wa_bkpf TYPE t_bkpf.

TYPES: BEGIN OF t_bseg,

*include structure bseg.

bukrs LIKE bseg-bukrs,

belnr LIKE bseg-belnr,

gjahr LIKE bseg-gjahr,

buzei LIKE bseg-buzei,

mwskz LIKE bseg-mwskz, "Tax code

umsks LIKE bseg-umsks, "Special G/L transaction type

prctr LIKE bseg-prctr, "Profit Centre

hkont LIKE bseg-hkont, "G/L account

xauto LIKE bseg-xauto,

koart LIKE bseg-koart,

dmbtr LIKE bseg-dmbtr,

mwart LIKE bseg-mwart,

hwbas LIKE bseg-hwbas,

aufnr LIKE bseg-aufnr,

projk LIKE bseg-projk,

shkzg LIKE bseg-shkzg,

kokrs LIKE bseg-kokrs,

END OF t_bseg.

DATA: it_bseg TYPE STANDARD TABLE OF t_bseg INITIAL SIZE 0,

wa_bseg TYPE t_bseg.

*Select directly into an internal table

SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

FROM bseg

INTO TABLE it_bseg.

Select directly into an internal table where fields are in a
different order or not all fields are specified

SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

FROM bseg

INTO CORRESPONDING FIELDS OF TABLE it_bseg.

*Select... endselect command

SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

FROM bseg

INTO wa_bseg.

APPEND wa_bseg TO it_bseg.

ENDSELECT.

*Select FOR ALL ENTRIES command

SELECT bukrs belnr gjahr bldat monat budat xblnr awtyp awkey

UP TO 100 ROWS

FROM bkpf

INTO TABLE it_bkpf.

IF sy-subrc EQ 0.

The FOR ALL ENTRIES comand only retrieves data which matches
entries within a particular internal table.

SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

FROM bseg

INTO TABLE it_bseg

FOR ALL ENTRIES IN it_bkpf

WHERE bukrs EQ it_bkpf-bukrs AND

belnr EQ it_bkpf-belnr AND

gjahr EQ it_bkpf-gjahr.

ENDIF.

Regards,

Kumar.


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	3	e2	f2	g2	h2
a3	b3	c3	2	4	e3	f3	g3	h3
a4	b4	c4	3


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	3	e2	f2	g2	h2
a3	b3	c3	2	4	e3	f3	g3	h3
a4	b4	c4	3


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	3	e2	f2	g2	h2
a3	b3	c3	2	4	e3	f3	g3	h3
a4	b4	c4	3


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	3	e2	f2	g2	h2
a3	b3	c3	2	4	e3	f3	g3	h3
a4	b4	c4	3


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	3	e2	f2	g2	h2
a3	b3	c3	2	4	e3	f3	g3	h3
a4	b4	c4	3


A	B	C	D	D	E	F	G	H

a1	b1	c1	1	1	e1	f1	g1	h1
a2	b2	c2	1	3	e2	f2	g2	h2
a3	b3	c3	2	4	e3	f3	g3	h3
a4	b4	c4	3