SQL Support for SAP
This section describes TDV SQL support for SAP—SAP resource types (functions, tables, and ABAP queries) and their behavior within TDV SQL queries.
| • | TDV SQL Support for SAP Functions |
| • | TDV SQL Support for SAP Tables |
| • | TDV SQL Support for SAP ABAP Queries |
| • | TDV SQL Support for SAP Dates |
TDV SQL Support for SAP Functions
The following section describes how SAP functions work within TDV. It is divided into five parts:
| • | Introspection and SAP Functions |
| • | Capabilities and SAP Functions |
| • | Parameter Mapping and SAP Functions |
| • | Result Mapping and SAP Functions |
| • | Joins and SAP Functions |
Introspection and SAP Functions
Introspection examines SAP for its list of functions, allows the user to select functions, and translates the function metadata to relational tables that TDV understands.
Datasource Re-Introspection
In order to sync data in TDV with any RFC changes made in the SAP database, click on the Clear Metadata Cache button that is found in the Datasource properties under Re-Introspection tab. Use this to clear the cache and then re-introspect the data source to apply the RFC changes.
The two sections below explain:
| • | Resource Hierarchy |
| • | Metadata Mapping |
Resource Hierarchy
The introspector contains two folders for SAP functions: BAPIs and RFCs. Each folder shows the same set of functions at the leaf nodes, but the hierarchy differs. BAPI hierarchy consists of Applications, Object Types, and Object Methods, which are the functions themselves. RFC hierarchy contains Development Classes, Function Groups, and the functions themselves.
To use an SAP function in TDV it must be RFC-enabled, and the user specified in the SAP data source configuration must be authorized to invoke RFCs. SAP transaction SE37 can be used to determine if a function is RFC-enabled (a “Remote-Enabled Module”).
Metadata Mapping
To call an SAP function, the following information is needed:
| • | Function name |
| • | Import parameters(collection of named input fields) |
| • | Export parameters(collection of named output fields) |
| • | Table parameters (collection of named tabular structures) |
In TDV, all import, export, and table parameters become columns in a single, named virtual table that represents the SAP function. The following naming convention is used to map fields of an SAP function to columns of a TDV table:
<Class> <Structure> <Field>
| — | Class—Import or Export. Omitted if the field is a table parameter. |
| — | Structure—Name of the structure containing the value. Omitted if the value is scalar. Table structure name if the field is a table parameter. |
| — | Field—Required name of the field. |
Each data type in SAP is mapped to a TDV SQL-based type. TDV is limited to types supported in the underlying SAP JCo library.
The following table lists the SAP data types, whether they are supported in TDV, and their corresponding TDV type.
|
SAP Data Type |
ABAP Type |
Supported |
TDV Data Type |
|
CHAR, UNIT, CUKY, CLNT, LANG, LCHR, VARC |
C |
Yes |
VARCHAR |
|
DATS |
D |
Yes |
DATE |
|
FLTP |
F |
Yes |
DOUBLE |
|
STRING |
g |
Yes |
VARCHAR |
|
INT4, INT2, INT1 |
I |
Yes |
BIGINT |
|
NUMC |
N |
Yes |
VARCHAR |
|
CURR, QUAN, DEC, PREC |
P |
Yes |
DECIMAL, NUMERIC |
|
TIMS |
T |
Yes |
TIME |
|
LRAW, RAW |
X |
Yes |
VARCHAR |
|
XSTRING |
y |
Not supported. Cannot be used in RFC-callable functions. |
|
Documentation of SAP function and its columns is retrieved from SAP and stored in the Annotation field in TDV.
Capabilities and SAP Functions
Data-source-specific capabilitiesdetermine how a SQL statement is to be divided between SAP, TDV, and other data sources that might be referenced in the query. Where a capability is lacking in a data source but required to run a query, TDV typically compensates by performing the work itself.
The following table lists commonly used capabilities and whether they are supported in queries against SAP functions. Pushed indicates whether the capability is supported directly on SAP. For efficient queries, it is best to minimize use of capabilities that cannot be pushed.
|
Capability |
Supported? |
Pushed? |
Notes |
|
CASE |
Yes |
No |
|
|
DELETE |
No |
No |
Every SAP function handles deleting data differently. Consult SAP documentation before constructing SELECT statements with DELETE. |
|
DISTINCT |
Yes |
No |
|
|
Filter |
Yes, with special usage |
Yes |
All filters are mapped to parameters. (See Parameter Mapping and SAP Functions.) SAP does not filter output parameters, but by default they are pushed anyway. Workaround: wrap queries to SAP functions. |
|
Filter–BETWEEN |
Yes, with special usage |
No |
See Notes on Filter. |
|
Filter–IN |
Yes, with special behavior |
Yes |
|
|
Filter–LIKE |
Yes, with special usage |
No |
See Notes on Filter. |
|
Functions–aggregate |
Yes |
No |
|
|
Functions–CAST |
Yes |
Yes |
|
|
Functions–others |
Yes |
No |
|
|
GROUP BY |
Yes |
No |
|
|
INSERT |
No |
No |
Every SAP function handles inserting data differently. Consult SAP documentation before constructing SELECT statements with INSERT. |
|
Join |
Yes |
No |
|
|
ORDER BY |
Yes |
No |
|
|
Subquery |
Yes |
No |
|
|
Transactions |
No |
No |
INSERT/UPDATE/DELETE not supported. |
|
UPDATE |
No |
No |
Every SAP function handles updating data differently. Consult SAP documentation before constructing SELECT statements with UPDATE. |
Parameter Mapping and SAP Functions
Parameter mapping is the process of applying SQL filter expressions to the SAP function call. The SQL filter becomes a series of arguments to the SAP function. How this is done depends on the number and types of parameters:
| • | No Parameters |
| • | Import Parameters |
| • | Table Parameters |
| • | Multiple Parameter Sets |
No Parameters
The following is a valid SQL statement:
SELECT * FROM /Shared/DataServicesForSAP_4_6/Sources/SAP/BAPI_CUSTOMER_GETLIST
This invokes the SAP function BAPI_CUSTOMER_GETLIST, and all of the values in its import parameters, export parameters, and table parameters are returned. The SAP system on which to execute the function is specified by the data source named SAP in the resource tree folder:
Shared > DataServicesForSAP_4_6 > Sources
Import Parameters
Import parameters are scalar or structure values that represent function input. Import parameters may or may not be required for the function to succeed, depending on the implementation of the function in SAP.
Include a WHERE clause that assigns a value to each import parameter. For example:
SELECT * FROM BAPI_COMPANYCODE_GETDETAIL WHERE "Import COMPCODE" = '1000'
Values must be provided using the “=” operator. Comparison operators such as “<” and “>” have no meaning and are ignored.
The IN operator is supported, but its behavior is special. See Multiple Parameter Sets for more information.
Note: Import parameters are often used to capture object identifiers, for example Customer ID. Some SAP functions require that identifiers be padded to their full length with leading zeros. For example, SAP might not recognize the value ‘1000’ for a field of type VARCHAR(10) unless it is padded to ‘0000001000’.
Table Parameters
To provide a table parameter with a single row of data, follow the instructions described in Import Parameters. For example, this SQL invokes the function BAPI_CUSTOMER_GETLIST with one row of data in the table CUSTOMERRANGE:
SELECT * FROM BAPI_CUSTOMER_GETCONTACTLIST WHERE "CUSTOMERRANGE SIGN" = 'I' AND "CUSTOMERRANGE OPTION" = 'BT' AND "CUSTOMERRANGE LOW" = '0000000000' AND "CUSTOMERRANGE HIGH" = '9999999999'
To provide multiple rows of data, use an IN clause for each column. An IN clause on a table parameter is interpreted as a set of input rows for a given column.
Multiple Parameter Sets
An SAP function can be invoked multiple times with a single SQL statement. This can be helpful when a function returns detail on a single object but the query must return detail data for multiple customers in a single result set.
To support this, parameters passed in a WHERE clause are decomposed into a set of invocations of the SAP function, where each invocation is a set of input values. The behavior of the WHERE clause with SAP functions is described below.
| • | OR designates a new invocation of the function. |
| • | NOT is ignored. |
| • | An IN clause on an import parameter is expanded and treated as a series of OR expressions. |
| • | NOT IN is treated like IN. |
| • | An IN clause on a table parameter is preserved as a set of input rows for a given column. This enables table parameters to be populated with many rows of data, using an IN clause for each column. |
The four examples below illustrate different combinations of the rules and their results. The SQL statement is listed first, and then a breakdown of the function invocations that would result.
SELECT * FROM FOO WHERE "Imports A" = 1 AND "Imports B" = 2
Results in an invocation to FOO with Imports A=1, B=2.
| 1. | Multiple invocations, imports (2 fields) |
SELECT * FROM FOO WHERE "Imports A" = 1 AND "Imports B" = 2 OR
"Imports B" = 3 AND "Imports A" IN (3, 4)
Results in three invocations of FOO with the following values:
Imports A=1, Imports B=2
Imports A=3, Imports B=3
Imports A=4, Imports B=3
| 2. | Single invocation, imports and a table (1 column, 1 row) |
SELECT * FROM FOO WHERE "Imports A" = 1 AND "FOO_TABLE B" = 2
Results in one invocation of FOO with the following values:
Imports A=1, FOO_TABLE (row 1): B=2
| 3. | Multiple invocations, imports, and a table (2 columns, 2 rows) |
SELECT * FROM FOO WHERE "Imports A" = 1 AND "FOO_TABLE B" IN (3, 4) AND "FOO_TABLE C" IN (5, 6) OR "Imports A" = 2 AND "FOO_TABLE B" IN (7, 8) AND "FOO_TABLE C" IN (9, 10)
Results in two invocations of FOO, with the following values:
Imports A=1, FOO_TABLE (row 1): B=3, C=5 (row 2): B=4, C=6
Imports A=2, FOO_TABLE (row 1): B=7, C=9 (row 2): B=8, C=10
Result Mapping and SAP Functions
Result mapping is the process of translating data returned by an SAP function call into a standard database result set with rows and columns. Because data structures within SAP functions are more complex than rows and columns, this is not always a one-to-one mapping.
| • | Import Parameters |
| • | Export Parameters |
| • | Table Parameters |
Import Parameters
Import parameters provided in filters are returned unmodified in each row of the result set.
Export Parameters
Export parameters are returned unmodified in each row of the result set.
Table Parameters
An SAP function can contain many table parameters, which are like export parameters with multiple rows. Every table parameter belongs to a named table structure. An SAP function can contain table parameters from multiple, independent table structures, without a common key to relate them. You can simultaneously select table parameters from more than one of these independent table structures.
When a query selects table parameters from more than one table structure, the table structure containing the greatest number of rows is designated as the primary table structure. Values from other table structures are iterated over in lock-step with the primary table structure. When a table structure has no further rows but the primary table structure contains more rows, null values are output.
For example, table structure A contains table parameters A1 and A2 with three rows, and table structure B contains table parameters B1 with one row:
SELECT "A A1", "A A2", "B B1"
This statement generates the following results:
"A A1" "A A2" "B B1"
------ ------ ------
row1-1 row1-2 row1-1
row2-1 row2-2 <NULL>
row3-1 row3-2 <NULL>
The behavior is different when table structures and single-row collections (import and export parameters) are combined. These collections always return a single row, so they are repeated over the number of rows returned by the primary table structure. For example, you can have a statement with export parameters E E1 and E E2 and a table structure T containing table parameter T1 with three rows:
SELECT "Export E E1", "Export E E2", "T T1"
This query returns the following results:
"Export E E1" "Export E E2" "T T1"
------------- ------------- ------val-e1 val-e2 row1
val-e1 val-e2 row2
val-e1 val-e2 row3
Joins and SAP Functions
This sectiondescribes how to join SAP functions with each other and with other resources. You can use a semijoin to combine the output of an SAP function with another SAP function or any TDV resource. Semijoin uses the results of one query as inputs to another query. As described Multiple Parameter Sets, every unique input set provided to the SAP function resource causes a function call that adds one or more rows to the result. It may be desirable to control the ordering of the function invocation, and thus the rows in the result, by adding NESTEDLOOP as a query hint; for example, {OPTION SEMIJOIN, NESTEDLOOP}.
If SAP function invocation fails, export parameter values are typically available to diagnose the problem. But from the perspective of the TDV join, the right-side resource still has rows to return, and so NULL values may be injected into the result despite the failure of the function.
With a relational data source, an extra condition would be added to the JOIN expression, so that rows containing an error message would be omitted. Because all filters are pushed to SAP, and SAP only allows filtering function by function, and only via specific table and import parameters, you may need to have TDV take over filtering the results to omit error rows. One way to do this is by writing a SQL script to contain the query against the SAP function, and then a view with which to filter out the NULL rows.
A good idea is to test the SAP function in SAP GUI to understand how it operates, and then test it in isolation in a simple TDV view.