In previous blog posts, we showed you how you can access CDS view entities in an ABAP system using SQL via Open Database Connectivity (ODBC). The ODBC interface has the advantage that it allows unrestricted SQL access to all exposed ABAP CDS view entities. Data from different entities can be joined in an ad-hoc fashion and data can be aggregated for analytical queries. If you want to know the basics, check these blog posts: Consuming CDS View Entities Using ODBC-Based Client Tools | SAP Blogs , Using the ODBC driver for ABAP on Linux | SAP Blogs , Access Control for ABAP’s SQL Service (1/3) – Set the Scene | SAP Blogs

Now, in these previous blog posts, we used simple cases where you want to access those entities using standard tools with an ODBC client like MS Excel, Libreoffice, or HANA SDA. In those cases, the tools themselves assemble the SQL queries executed over the ODBC driver. The tools themselves will assume some kind of “ANSI SQL” compatibility and can derive details about the supported SQL dialect from ODBC SQLGetInfo() attributes.

However, there are other cases where you may want to develop your own SQL queries on objects exposed using an ABAP SQL service. In these cases, you need to know more details on the supported SQL Dialect of the ABAP SQL Service.  This is what I’ll cover in this blog post here.

You will see that the SQL dialect of the ABAP SQL service is much more similar to ANSI SQL or HANA SQL than to ABAP SQL because in ODBC, there are no ABAP host variables or internal tables and therefore many ABAP SQL features are not present. However, it’s important to understand that the SQL Dialect of the ABAP SQL Service is a modified ABAP SQL dialect executed by the ABAP SQL engine. It works on ABAP-managed entities and profits from many ABAP-server-specific features like automatic client handling, table buffering, and DCL handling. The result set of queries may be more ABAP-like and in some places ABAP SQL features still shine through. This blog will try to give you a brief overview about the supported SQL features, highlighting some of the differences and similarities to HANA SQL and ABAP SQL.

Currently, the SQL dialect of the ABAP SQL service is restricted to only read only SQL queries that do not set locks on database level. No INSERT/UPDATE/DELETE statements and no SELECT FOR UPDATE are allowed on objects exposed in an SQL service. Therefore, the SQL Service currently has no concept of database transactions and COMMIT or ROLLBACK statements are not supported/needed.

To keep this blog post simple, I won’t aim at a full coverage of the SQL dialect of the ABAP SQL service and won’t use any syntax diagrams., However, I will provide many examples and hope it will give you a good enough help to develop your own SQL queries.

Brief Recap

As a recap about the ODBC driver for ABAP and SQL services, let’s start with an overview picture taken from the SAP documentation:

SQL Service Overview

In the ABAP backend system, you can expose a set of CDS view entities contained in a service definition via a service binding of type SQL. In the service definition you can define external names for the exposed CDS view entities. In SQL Statements on the ABAP SQL Services both the SQL service binding name and the external names contained in the service definition will play a role.

The Schema Name <–> The Service Binding Name

In SQL statements, all accessed objects must be fully qualified using a schema name and an object name. The schema name is identical to a SQL service binding name and the object names are the external names of the exposed CDS objects listed in the corresponding service definition. So, a simple example related to the picture above is the following SQL statement:

Select * from "ZData"."OrderItems"
where "ZData"."OrderItems"."OrderId" = 42

Compared to “ABAP SQL” the schema name is a new thing and compared to “HANA SQL” the schema name has been given a different meaning. The name separator ‘.’ is more similar to HANA SQL. The ABAP SQL name separators ‘-‘ and ‘~’ are not supported.

Please note that object names included in double quotes are case-sensitive and unquoted names are not case-sensitive. Column names don’t need to be prefixed by a schema and object name as long as they can be uniquely determined in the objects used in the SQL statement. So the following SQL statements are equivalent to the one above.

Select * from "Zdata"."OrderItems" where "OrderId" = 42

Select * from ZDATA.ORDERITEMS where ORDERID = 42

It is also possible to use aliases for objects:

Select * from ZDATA.ORDERITEMS T1 where T1.ORDERID = 42

Prepared Statements with Parameter Markers

Like in HANA SQL, statement texts may contain unnamed parameter markers ( ‘?’ ). These statements can be prepared once and executed several times using different value bindings at execution time. An example statement with 4 input parameters is:

Select * from "ZData"."OrderItems"
where "OrderId" = ? or "OrderId" IN ( ?, ?, ? )

The data types of the parameter markers are currently restricted to simple DDIC/ODBC types and no table like input is supported.

System Tables

In ODBC, object meta data can be retrieved using ODBC functions like SQLTables() or SQLColumns() . However, in SQL services view entities with parameters can also be exposed and ODBC has no clear concept for those objects. Therefore, the SQL service also provides a set of system tables where meta data can be retrieved via SQL. The system tables are automatically exposed and contain information about the objects that can be accessed in the current ODBC connection to the ABAP backend. The objects visible in an ODBC connection depend on the ABAP user privileges and on the service path used to open the ODBC connection (technical or business user scenario).

The system table SYS.VIEWS works similar to the ODBC function SQLTables() and lists all CDS view entities visible in the current ODBC connection including the system tables themselves. The column HAS_PARAMETERS can be used to determine if a view has parameters.

SQL> select schema_name, view_name from SYS.VIEWS where schema_name = 'SYS'

+-------------------------------+-------------------------------+
| SCHEMA_NAME                   | VIEW_NAME                     |
+-------------------------------+-------------------------------+
| SYS                           | DUMMY                         |
| SYS                           | M_CONNECTION_INFO             |
| SYS                           | VIEWS                         |
| SYS                           | VIEW_COLUMNS                  |
| SYS                           | VIEW_PARAMETERS               |
+-------------------------------+-------------------------------+

The system table SYS.VIEW_COLUMNS lists column information for the exposed entities containing both ODBC and ABAP meta data:

SQL> select COLUMN_NAME, ODBC_DATA_TYPE OD,
            ODBC_BUFFER_LENGTH OBL, DDIC_TYPE_NAME, DDIC_LENGTH
     from SYS.VIEW_COLUMNS
     where schema_name = 'SYS' and view_name = 'VIEW_COLUMNS'
     order by column_position

+--------------------+-----+-----+---------------+------------
| COLUMN_NAME        | OD  | OBL | DDIC_TYPE_NAME| DDIC_LENGTH|
+--------------------+-----+-----+---------------+------------+
| SCHEMA_NAME        | -8  | 60  | CHAR          | 30         |
| VIEW_NAME          | -8  | 60  | CHAR          | 30         |
| COLUMN_NAME        | -8  | 60  | CHAR          | 30         |
| SCHEMA_NAME_UPPER  | -8  | 60  | CHAR          | 30         |
| VIEW_NAME_UPPER    | -8  | 60  | CHAR          | 30         |
| COLUMN_NAME_UPPER  | -8  | 60  | CHAR          | 30         |
| COLUMN_POSITION    | 4   | 4   | INT4          | 10         |
| DESCRIPTION        | -8  | 500 | CHAR          | 250        |
| IS_KEY             | -8  | 10  | CHAR          | 5          |
| ODBC_DATA_TYPE     | 5   | 2   | INT2          | 5          |
| ODBC_TYPE_NAME     | -8  | 8   | CHAR          | 4          |
| ODBC_COLUMN_SIZE   | 4   | 4   | INT4          | 10         |
| ODBC_DECIMAL_DIGITS| 5   | 2   | INT2          | 5          |
| ODBC_BUFFER_LENGTH | 4   | 4   | INT4          | 10         |
| ODBC_NUM_PREC_RADIX| 5   | 2   | INT2          | 5          |
| ODBC_NULLABLE      | 5   | 2   | INT2          | 5          |
| DDIC_TYPE_NAME     | -8  | 8   | CHAR          | 4          |
| DDIC_LENGTH        | 4   | 4   | INT4          | 10         |
| DDIC_DECIMALS      | 5   | 2   | INT2          | 5          |
+--------------------+-----+-----+---------------+------------+

The system table SYS.VIEW_PARAMETERS contains ODBC and ABAP meta data for view parameters ( HAS_PARAMETERS = ‘TRUE’ in SYS.VIEWS ).

SYS.DUMMY is just a dummy table with one row and works like the corresponding HANA table. Many of my SQL examples throughout this blog will use this dummy table.

The monitoring view SYS.M_CONNECTION_INFO contains information about the current ODBC connection (for example, ABAP user name, alias, client, SQL service name, EPP information).

Set Operations UNION, INTERSECT, EXCEPT

Like in ABAP SQL and HANA SQL you can use the standard set operations UNION, INTERSECT, EXCEPT to combine result sets of multiple queries in one query.

Examples:

select DUMMY FROM SYS.DUMMY A EXCEPT SELECT 'X' FROM SYS.DUMMY B

select DUMMY FROM SYS.DUMMY EXCEPT SELECT 'X' FROM SYS.DUMMY

In cases where the same table name is used in multiple UNION ALL branches, the ABAP SQL engine requires that those tables are given different alias names.

Supported JOIN Syntax

The supported JOIN syntax in the SQL Dialect of the ABAP SQL Service is very similar to HANA SQL. The common ANSI SQL clauses INNER JOJN, LEFT OUTER JOIN, RIGHT OUTER JOIN and CROSS JOIN are supported. For a LEFT OUTER JOIN it is also possible to add the HANA-specific LEFT OUTER MANY TO ONE JOIN clause.

Instead of using explicit JOIN clauses, you can also use a comma separated list of tables in the FROM clause to express an INNER JOIN or a CROSS JOIN. This feature is not supported in ABAP SQL.

Examples:

select A.DUMMY FROM SYS.DUMMY A 
CROSS JOIN SYS.DUMMY B

select A.DUMMY FROM SYS.DUMMY A , SYS.DUMMY B

select A.DUMMY FROM SYS.DUMMY A 
LEFT OUTER MANY TO ONE JOIN SYS.DUMMY B
ON A.DUMMY = B.DUMMY

Common Table Expressions and Derived Tables

Common Table Expressions (CTE) can be used to define temporary view objects with a WITH clause at the beginning of a query that can later be used multiple times in the full select part.

Example:

WITH A AS ( SELECT SCHEMA_NAME S, VIEW_NAME V FROM SYS.VIEWS )
SELECT * FROM A WHERE A.S = 'SYS'
UNION ALL
SELECT * FROM A WHERE A.S = 'ZData'

Recursive SQL in CTEs is currently not supported.

As an alternative to CTEs, derived table clauses can be directly used in the FROM clause of a query.

Example:

SELECT * FROM ( SELECT SCHEMA_NAME S, VIEW_NAME V FROM SYS.VIEWS ) A
WHERE A.S = 'SYS'

While CTEs are also supported in ABAP SQL, derived table clauses are only supported in the SQL dialect of the ABAP SQL service.

Both queries with CTEs and with derived tables work a little different regarding case sensitivity of object names. Whenever you define a name in double quotes for the CTE or derived table itself or for returned column names, you need to use those in double quotes throughout the whole SQL statement.

SELECT * FROM ( SELECT SCHEMA_NAME "s", VIEW_NAME V FROM SYS.VIEWS ) "a"
WHERE "a"."s" = 'SYS'

This will work fine, but the following query won’t work.

SELECT * FROM ( SELECT SCHEMA_NAME "s", VIEW_NAME V FROM SYS.VIEWS ) "a"
WHERE A.S = 'SYS'

Type Casts

You can always insert type casts in ABAP CDS style directly in the definition of your exposed CDS entities. However, sometimes you may want to insert type casts in your SQL queries, for example, to make some data types better consumable or readable in standard ODBC applications.

The currently supported type casts in the SQL dialect of the ABAP SQL service are more HANA SQL-like. It is only possible to cast to a limited number of ABAP types. The general form of a type cast is:

CAST( <expression> AS <data type> )

(where <expression> can be an SQL expression, a literal value, or a column. <data type> can be one of the data types from the second column of the following table)

A character string can be cast to an abap.string using the TO_CLOB( <expression> ) function.

A binary string can be cast to an abap.xstring using the TO_BLOB( <expression> ) function.

Currently there a no special CASTs to date, time or timestamp data types and also no type casts for some of the ABAP specific data types like abap.numc , abap.curr, abap.cuky  …

Example:

select      CAST( 4711 AS SMALLINT ) AS C_INT2,
            CAST( 4711 AS INTEGER ) AS C_INT4,
            CAST( 1E-1200 AS DECIMAL) AS C_DF34N,
            CAST( 1.234 AS DECIMAL(10,3))  AS C_DEC103,
            CAST( 2.5 AS DOUBLE) AS C_DOUBLE
FROM SYS.DUMMY

Literals and ODBC Escape Sequences

Literal values can be used in the SQL dialect of the ABAP SQL service very similar to HANA SQL. For numeric literals, you may want to add an explicit type cast to enforce a specific data type in the result set. I have already given some examples of numeric literals when I discussed type casts.

For character-like data types, you can enclose the literals in single quotes. A prefix ‘N’ is optional.

Raw (binary) literals must be enclosed in single quotes with prefix ‘x’ . In the single quotes, you provide the hex string representation of the raw value.

For DATE/TIME/TIMESTAMP ( abap.datn/abap.timn/abap.utcl ) literals, the prefixes ‘date’, ‘time’ or ‘timestamp’ can be used.

Example:

SQL> SELECT 'HELLO'                                  AS C_CHAR5,
            x'123456ABCDEF'                          AS C_RAW6,
            date'2015-01-02'                         AS C_DATN,
            time'11:55:00'                           AS C_TIMN,
            timestamp '2015-01-02 11:55:00.1234567'  AS C_UTCL
FROM SYS.DUMMY

+--------+-------------+---------+---------+----------------------------+
| C_CHAR5| C_RAW6      | C_DATN  | C_TIMN  | C_UTCL                     |
+--------+-------------+---------+---------+----------------------------+
| HELLO  | 123456ABCDEF| 20150102| 115500  | 2015-01-02T11:55:00,1234567|
+--------+-------------+---------+---------+----------------------------+

As an alternative for DATE/TIME/TIMESTAMP you can use ODBC escape sequences ( Date, Time, and Timestamp Literals via ODBC Escape Sequences ) which are also supported in HANA SQL.

SQL> SELECT {d '2015-01-02'}                    AS C_DATN,
            {t '11:55:00'}                      AS C_TIMN,
            {ts '2015-01-02 11:55:00.1234567'}  AS C_UTCL
FROM SYS.DUMMY

+---------+---------+----------------------------+
| C_DATN  | C_TIMN  | C_UTCL                     |
+---------+---------+----------------------------+
| 20150102| 115500  | 2015-01-02T11:55:00,1234567|
+---------+---------+----------------------------+

SQL Functions

Since the SQL dialect of the ABAP SQL service is finally processed by the ABAP SQL engine, most of the supported build in scalar ABAP SQL functions can be used (ABAP SQL build in functions). In some of the date time functions, ABAP data type names shine through in the function names.

SQL> SELECT date'20150102'                      AS C_DATN,
            ADD_DAYS( date'20150102', 5 )       AS C_DATN_P_5, 
            DATN_ADD_DAYS( date'20150102', 10 ) AS C_DATN_P_10,
            DATS_FROM_DATN( date'20150102')     AS C_DATS
FROM SYS.DUMMY

+----------+----------+----------+----------+
| C_DATN   | C_DATN_5 | C_DATN_10| C_DATS   |
+----------+----------+----------+----------+
| 20150102 | 20150107 | 20150120 | 20150102 |
+----------+----------+----------+----------+

The functions DATS_FROM_DATN, DATS_TO_DATN, BINTOHEX, HEXTOBIN can also be used for type casting purposes.

Apart for scalar functions, ABAP SQL aggregate functions and  ABAP SQL windowing functions  can also be used in in the SQL dialect of the ABAP SQL service .

Currently, it’s not possible to access associations using path expressions in the SQL dialect of the ABAP SQL service . Columns of associated entities can only be accessed if they are explicitly exposed using a column name in the CDS view entity.

Named Parameter Syntax on Views with Parameters

Let’s look at a very simple example with a CDS entity with one parameter.

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Test entity with parameter'

define view entity ZENTITYWITHPARAM with parameters
  im_int: abap.int4
as select from svers {
  key version as keyCol,
  $parameters.im_int as IntCol
}

Let’s also assume you have exposed this entity in your service definition with external name ZEntityWithParam with corresponding service binding ZData.

After activating all ABAP objects, the system views will now show the following entries:

SQL> select HAS_PARAMETERS from SYS.VIEWS
      WHERE schema_name = 'ZData'
        and view_name   = 'ZEntityWithParam'

+---------------+
| HAS_PARAMETERS|
+---------------+
| TRUE          |
+---------------+

SQL> select PARAMETER_NAME, ODBC_DATA_TYPE, DDIC_TYPE_NAME, DDIC_LENGTH
       from SYS.VIEW_PARAMETERS
      WHERE schema_name = 'ZData'
        and view_name   = 'ZEntityWithParam'

+----------------+---------------+---------------+------------+
| PARAMETER_NAME | ODBC_DATA_TYPE| DDIC_TYPE_NAME| DDIC_LENGTH|
+----------------+---------------+---------------+------------+
| im_int         | 4             | INT4          | 4          |
+----------------+---------------+---------------+------------+

SQL> select COLUMN_NAME, ODBC_DATA_TYPE, DDIC_TYPE_NAME, DDIC_LENGTH
       from SYS.VIEW_COLUMNS
      WHERE schema_name = 'ZData'
        and view_name   = 'ZEntityWithParam'

+----------------+---------------+---------------+------------+
| COLUMN_NAME    | ODBC_DATA_TYPE| DDIC_TYPE_NAME| DDIC_LENGTH|
+----------------+---------------+---------------+------------+
| keyCol         | -8            | CHAR          | 72         |
| IntCol         | 4             | INT4          | 4          |
+----------------+---------------+---------------+------------+

Now you can run SQL queries on the new entity with named parameter syntax.

SQL> select * from ZData.ZEntityWithParam ( im_int => 4711 )

+---------------------------------+
| keyCol         | IntCol         |
+----------------+----------------+
| 7xx            | 4711           |
+---------------------------------+

The named parameter syntax is also supported in HANA SQL. The SQL dialect of the ABAP SQL service  doesn’t support positioned parameter syntax.

ABAP -> ODBC Data Type Mappings

The ODBC driver for ABAP allows the user to configure the mapping of ABAP data types to ODBC SQL data types. To do this, the connection parameter “Typemap” can be used. Regardless of the ODBC SQL data type reported by the ODBC driver, the ODBC application is still free to use an ODBC C data type of its choice to retrieve a given ODBC SQL data type.

In most cases, the ODBC end user wants to retrieve the ABAP data in a way that is easy to consume. With the “Typemap=semantic” configuration, the ODBC driver can:

• map all date like data types like abap.dats and abap.datn to ODBC type SQL_DATE/SQL_TYPE_DATE
• map all time like data types like abap.tims and abap.timn to ODBC type SQL_DATE/SQL_TYPE_DATE
• map all decimal floating point like data types like abap.d16n, abap.d34n, abap.d16r, abap.d34r, abap.d16d, abap.d34d to ODBC type SQL_DECFLOAT
• perform an automatic decimal shift for type abap.curr and maps this data type also to ODBC type SQL_DECFLOAT
• perform an ISO conversion for type abap.lang and maps this data type to ODBC type SQL_WCHAR with length 2

We will look at the driver specific ODBC data type SQL_DECFLOAT later in more detail.

For some ODBC applications that want to safely access the ABAP data ,the “Typemap=semantic” configuration may have the problem that not all data in the old abap.dats and abap.tims columns can be mapped to a date. The ABAP system does not prevent that some applications store date/time literals in those columns (for example, the string ‘TODAY’ in an abap.dats column). To ensure that these use cases work without data loss, a slightly different “Typemap=semanticDatsTimsAsWchar” is available. This type mapping is similar to the semantic type mapping with the exception that the ABAP data type abap.dats is mapped to SQL_WCHAR with length 8 and ABAP data type abap.tims is mapped to SQL_WCHAR with length 6. -As a result, no conversions will happen and no potential conversion errors will occur.

Other applications may want to read data from an ABAP system as-is without any semantic conversion. With the “Typemap=native”, configuration the ODBC driver will:

• map data type abap.dats to ODBC type SQL_WCHAR with length 8
• map data type abap.datn to ODBC type SQL_DATE/SQL_TYPE_DATE
• map data type abap.tims to ODBC type SQL_WCHAR with length 6
• map data type abap.timn to ODBC type SQL_TIME/SQL_TYPE_TIME
• map data types abap.d16n and abap.d34n to ODBC type SQL_DECFLOAT
• map data types abap.d16r and abap.d34r to ODBC type SQL_BINARY
• map data types abap.d16d and abap.d34d to ODBC type SQL_DECIMAL
• not shift abap.curr data and maps this type to ODBC type SQL_DECIMAL
• return ABAP type abap.lang as is and and maps it to ODBC type SQL_WCHAR with length 1

All other ABAP data types are mapped to their corresponding ODBC SQL types independent of the Typemap setting ( INT1/INT2 -> SQL_SMALLINT, INT4 -> SQL_INTEGER, INT8 -> SQL_BIGINT, DEC -> SQL_NUMERIC, UTCL -> SQL_TIMESTAMP/SQL_TYPE_TIMESTAMP …).

Data Conversions for abap.lang and abap.curr Data Types

As mentioned in the last chapter, there is a special handling of ABAP data types LANG and CURR in the ODBC driver.

In ABAP tables, abap.lang data is stored as single-byte Unicode character (for example, ‘D’ for German or ‘E’ for English). However, in external interfaces (for example, the SAP logon screen), the language is typically shown as two-character string (for example, ‘DE’ for German or ‘EN’ for English). The table T002 in the ABAP backend system defines the mapping between these values.

If you are using one of the “Typemap=semantic*” settings, abap.lang values in the result set of a query will be automatically mapped to the 2 character strings. The other way around, if you are using using abap.lang values as input parameters, you need to provide them as 2 character strings. The same SQL queries will return different abap.lang values and will need different abap.lang input values if “Typemap=native” is used.

The abap.curr data type has been developed in times where no decimal floating point data type existed. For historic reasons, on database level, abap.curr data is stored in fixed-point decimal values. These values can only be interpreted correctly if the corresponding abap.cuky value is known. The fixed-point decimal values need to be shifted depending on its currency key. For an innocent or generic ODBC application that does not know the ABAP shifting algorithm, it comes very handy that the ODBC driver for ABAP directly returns the shifted abap.curr values. As a price, a fixed-point decimal data type can no longer hold values for all possible currencies and therefore the ODBC driver for ABAP returns shifted abap.curr values as decimal floating point (ODBC data type SQL_DECFLOAT) if one of the “Typemap=semantic*” settings is used.

Example with “Typemap=semantic”:

SQL> SELECT LANG, CUKY, CURR from ZData.TestView

+------+------+-------------------------------------------+
| LANG | CUKY | CURR                                      |
+------+------+-------------------------------------------+
| EN   | USD  | 9999999999999.99                          |
| DE   | EUR  | 0.01                                      |
| LT   | BHD  | 1234.5                                    |
+------+------+-------------------------------------------+

Example with “Typemap=native”:

SQL> SELECT LANG, CUKY, CURR from ZData.TestView

+------+------+------------------+
| LANG | CUKY | CURR             |
+------+------+------------------+
| E    | USD  | 9999999999999.99 |
| D    | EUR  | 0.01             |
| X    | BHD  | 12345.00         |
+------+------+------------------+

The examples above demonstrate different query results with and without active language conversion and currency shift. Please notice the changed values for column LANG, the change in data type for column CURR, and the shifted decimal point in the currency value for currency ‘BHD’. Currency values for currencies ‘USD’ and ‘EUR’ are stored unshifted in the ABAP database tables.

It’s also possible to use shifted abap.curr values as input values for SQL queries if they correspond to predicates on a CDS entity column. However, you can’t use shifted abap.curr values as input parameters for CDS entities with parameters. In this case, the corresponding abap.cuky value cannot be automatically determined.

The Driver-Specific SQL_DECFLOAT Type

The ODBC standard does not define a decimal floating point type (Decimal floating point – Wikipedia). However, there are databases like HANA, IBM Db2 and ABAP ( yes, the ABAP system itself can now also be viewed as a database )  that support such types. The main reason for using those types is to avoid rounding problems that would occur when using the double floating point type with radix 2. ABAP applications often use decimal floating point data for business data where such rounding could cause problems.

Please note the rounding artefacts in the following query result:

SQL> select cast (10000 as double) * cast (1.11 as double) as c_DOUBLE,
            cast (10000 as DECIMAL) * cast (1.11 as DECIMAL) as c_D34N
     from sys.dummy

+------------------------------------+----------------------------+
| C_DOUBLE                           | C_D34N                     |                   
+------------------------------------+----------------------------+
| 11100.000000000001818989403545856  | 11100.00                   |                         
+------------------------------------+----------------------------+

The ODBC standard allows that ODBC drivers define their own driver-specific data types if needed. The ODBC driver for ABAP has taken this way for the decimal floating point types and defined its own ODBC SQL data type SQL_DECFLOAT=-360 with two different octet lengths corresponding to the abap.d16n and abap.d34n data types. Other ODBC drivers like the IBM Db2 CLI driver have taken a similar approach (see SQL symbolic and default data types for CLI applications – IBM Documentation). ODBC applications that want to retrieve such SQL_DECFLOAT data should use default target data type SQL_C_CHAR or SQL_C_WCHAR to return the data in readable string format.

Unfortunately, not all standard ODBC applications handle driver-specific data types like SQL_DECFLOAT well. For example, MS Excel currently retrieves all unexpected driver-specific types as SQL_C_BINARY target data type. Later, MS Excel has no means to interpret the binary representation of decimal floating-point values. So, in applications like Excel, you may need to cast decimal floating point values (for example, to a VARCHAR string or a fixed-point decimal type that can hold he values) in the executed SQL query before loading them into a spread sheet.

Other ODBC applications like the simple unixODBC tool iusql will retrieve SQL_DECFLOAT values in readable form. HANA Cloud SDA can interpret the SQL_DECFLOAT data type correctly and map it to the corresponding HANA DECIMAL or SMALLDECIMAL decimal floating point data types in virtual tables.

Service Paths and DCL Handling

When you configure an ODBC connection for the ODBC driver for ABAP, you are asked to specify a service path. This can be done using the “ServicePath” setting in your ODBC DSN definition or in the ODBC connection string. The special service path “/sap/bc/sql/sql1/sap/S_PRIVILEGED” is typically used for technical users. This allows the logon user to access all schemas or service bindings and objects on which this user has been granted S_SQL_VIEW authorization in the ABAP backend systems. Objects from different schemas or service bindings can be used in one SQL statement. ABAP user specific DCLs are not applied when you use the “S_PRIVILEGED” service path. Typically, such DCLs do not exist for technical users.

All other service paths that are currently supported specify a single service binding name in the service path. In this case, the user can only access objects in this single schema. Typically, such service paths are used for ABAP business users. When such business user service paths are used, applicable ABAP-user-specific DCLs are mixed in by the ABAP SQL engine and therefore result sets of SQL queries may differ, depending on the ABAP connect user.

Client Handling and Table Buffering

Although the SQL dialect of the ABAP SQL service at first glance looks more like HANA SQL, it’s processed by the ABAP SQL engine. ABAP session variables like the session client are automatically set.  On CDS view entities, this ensures only data from the logon client of the current user is visible. The ABAP SQL engine can also make use of other ABAP application server-specific mechanisms like table buffering and not all SQL statements need to be routed to the underlying HANA database.

Related Links

Other blogs related to ABAP SQL services or the “ODBC driver for ABAP”:
Using the ODBC driver for ABAP on Linux | SAP Blogs ,
Consuming CDS View Entities Using ODBC-Based Client Tools | SAP Blogs ,
Access Control for ABAP’s SQL Service (1/3) – Set the Scene | SAP Blogs

Links to SAP documentation:

ODBC – ABAP Platform, On-Premise (Latest Version)
ODBC – SAP BTP, ABAP Platform (aka Steampunk)
ODBC – S/4HANA Cloud (Latest Version)

Conclusion

Based on examples, I have tried to explain the specifics of the SQL dialect that can be used on SQL services using the ODBC driver for ABAP. While this SQL dialect contains many elements that are also available in HANA SQL, you should be aware that the SQL statements are executed on an ABAP backend using the ABAP SQL engine. Therefore, in some places, ABAP specifics like ABAP data types or ABAP SQL function names shine through.

While this blog does not try to be a complete documentation of the supported SQL dialect, I hope that the given examples still help you if you want to execute SQL statements using the ODBC driver for ABAP. Please provide feedback and let me know if the blog has been helpful or if you miss anything in this blog.