ABAP - Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Reads →  SELECT clauses →  SELECT - FROM → 

SELECT - CLIENT

Quick Reference

Syntax

...  {USING CLIENT clnt}
   | {CLIENT SPECIFIED {[entity1~clnt] [, entity2~clnt] ...}|(clnt_syntax)} ...

Alternatives:

1. ... USING CLIENT clnt

2. ... CLIENT SPECIFIED {[entity1~clnt] [, entity2~clnt] ...}|(clnt_syntax)

Effect

These optional additions in the FROM clause of a query change automatic client handling or switch it off. When a single data source is specified, the addition must be inserted directly after the data source in the join condition. When a join expression is specified, the addition must be inserted after the last addition ON of the join condition. They are not allowed when accessing global temporary tables.

Note

The fact that the addition USING CLIENT changes automatic client handling and the addition CLIENT SPECIFIED switches it off is distinction that is particularly significant when accessing client-specific CDS entities. Here, CLIENT SPECIFIED modifies the structure of the results set.

Alternative 1

... USING CLIENT clnt


Effect

This addition modifies automatic client handling in Open SQL for the current query so that the client ID from clnt is used instead of the current client ID. The client specified in clnt is used in all places where, in implicit SELECTs, the current client is used in automatic client handling. If known statically that the data sources data_source are not client-specific, the addition USING CLIENT cannot be specified.

clnt expects a data object of the type c with length 3 and containing a client ID. A literal or a host variable can be specified, prefixed by an escape character @. If specified, the system field sy-mandt would be ignored and cannot be specified directly for clnt.

When a single data source is specified, the addition must be inserted directly after data_source in the join condition. When a join expression is specified, the addition must be inserted after the last addition ON of the join condition. This addition cannot be used with the addition CLIENT SPECIFIED.

Client handling can be switched precisely once for each query and is applied to all client-specific data sources specified there. These include:

Client handling can, however, be switched in subqueries defined using WITH and in queries joined using UNION.

If the data source is specified statically, the addition can be specified only if at least one client-specific data source is used; otherwise a syntax error occurs. The addition cannot be specified for a subquery in the WHERE condition.

When accessing CDS views, the session variable client is set to the client ID specified in clnt when the current SELECT statement is executed. The addition USING CLIENT cannot be used to access a CDS entity associated with a CDS role and that is subject to CDS access control:

Notes

Example

Reads all customers in client "800" from a client-specific database table.

SELECT *
       FROM scustom USING CLIENT '800'
       INTO TABLE @DATA(customers).

Alternative 2

... CLIENT SPECIFIED {[entity1~clnt] [, entity2~clnt] ...}|(clnt_syntax)


Extras:

1. ... [entity1~clnt] [, entity2~clnt] ...

2. ... (clnt_syntax)

Effect

This addition deactivates automatic client handling in Open SQL for the current query. If known statically that the data sources data_source are not client-specific, the addition CLIENT SPECIFIED cannot be specified.

When using the addition CLIENT SPECIFIED, the client column of client-specific data sources can be specified in the WHERE condition to determine the client identifier. In the addition ORDER BY, the column can be sorted explicitly according to client identifier.

The addition CLIENT SPECIFIED is applied only to the current Query. If multiple queries are used in a SELECT statement or WITH statement, client handling must be viewed separately for each query. More specifically, client handling in a main query is deactivated separately from client handling in the subqueries used there. Unlike USING CLIENT, CLIENT SPECIFIED can also be used in the subqueries of conditions sql_cond to specify the same client handling as in the main query.

If CLIENT SPECIFIED is specified for a client-specific CDS entity, the client field is read from the database and added to the results set. The structure of a client-specific CDS view or a client-specific CDS table function does not have a component for the client field, which means that a client column is added to the results set here implicitly. If the client field is accessed explicitly or implicitly in the SELECT statement, an addition entity~clnt must be used to give the field a name that can be used in the current statement.

The addition CLIENT SPECIFIED cannot be used when the following CDS entities are accessed:

The addition CLIENT SPECIFIED cannot be used together with the following path expressions:

Notes

Example

Like the example for USING CLIENT, this example reads all customers in client 800 from a client-specific database table, but needs an explicit WHERE condition to do this.

SELECT *
       FROM scustom CLIENT SPECIFIED
       WHERE mandt = '800'
       INTO TABLE @DATA(customers).

Example

Accesses a client-specific CDS view with the addition CLIENT SPECIFIED. To do this, the row type of the internal table used as a target range is defined using the addition CLIENT SPECIFIED of the statement TYPES. If the addition CLIENT SPECIFIED of the statement TYPES is not used, the column clnt would not exist in the table scarr_spfli_clnt and could not be used as a target range.

TYPES scarr_spfli_clnt TYPE demo_cds_scarr_spfli CLIENT SPECIFIED clnt.

DATA scarr_spfli_clnt TYPE TABLE OF scarr_spfli_clnt WITH EMPTY KEY.

SELECT *
       FROM demo_cds_scarr_spfli CLIENT SPECIFIED
       INTO TABLE @scarr_spfli_clnt.

Addition 1

... [entity1~clnt] [, entity2~clnt] ...

Effect

Declares the names clnt of the client fields of the client-specific CDS entities. If CLIENT SPECIFIED is used, the result set for a client-specific CDS entity has a client field, although the structure of the entity does not have components of this type. The declaration of a name with entity~clnt is required accessing this type of client field explicitly or implicitly in the SELECT statement:

Here entity is the name of a client-specific CDS entity used as a data source and clnt is a freely definable unique name for its client column, which is valid throughout the current SELECT statement.

Note

A name defined with entity~clnt has absolutely no dependency on the actual name of a client column in a data source of a CDS entity. It is used, for example, in a WHERE condition or ON condition to select specific clients in a CDS entity.

Example

Similar to the previous example for accessing a CDS view with addition CLIENT SPECIFIED. In this case, a WHERE condition is specified for the client column. This requires a name to be defined after CLIENT SPECIFIED.

TYPES scarr_spfli_clnt TYPE demo_cds_scarr_spfli CLIENT SPECIFIED clnt.

DATA scarr_spfli_clnt TYPE TABLE OF scarr_spfli_clnt WITH EMPTY KEY.

SELECT *
       FROM demo_cds_scarr_spfli
            CLIENT SPECIFIED demo_cds_scarr_spfli~client
       WHERE client = '800'
       INTO TABLE @scarr_spfli_clnt.

Executable Example

CDS Views, Client Handling

Addition 2

... (clnt_syntax)

Effect

If (source_syntax) is specified dynamically after FROM, a parenthesized data object clnt_syntax can be specified instead of a static [entity1~clnt] [, entity2~clnt] ... This data object must contain the static syntax when the statement is executed. The data object clnt_syntax can be a character-like data object or a standard table with a character-like row type. The syntax in clnt_syntax is, as in ABAP Editor, not case-sensitive. When an internal table is specified, the syntax can span multiple rows.

Example

As in the previous example for accessing a CDS view using the addition CLIENT SPECIFIED, but dynamic.

TYPES scarr_spfli_clnt TYPE demo_cds_scarr_spfli CLIENT SPECIFIED clnt.

DATA scarr_spfli_clnt TYPE TABLE OF scarr_spfli_clnt WITH EMPTY KEY.

DATA(source) = `demo_cds_scarr_spfli`.
DATA(client) = `client`.

DATA(clnt_syntax) = source && `~` && client.
DATA(cond) = client && ` = '800'`.

SELECT *
       FROM (source)
            CLIENT SPECIFIED (clnt_syntax)
       WHERE (cond)
       INTO TABLE @scarr_spfli_clnt.