Limitations of Open SQL Compared to SQL Script in HANA

Before you start perceiving an opinion on me, let me tell you something about my background. I have been a Computer Programmer and Analyst for around two decades. As a Business Analyst I spent considerable amount of my time developing Stored Procedures and Functions in MS/SQL and generating Crystal Reports over these. Last year my organization decided to change the Software Solutions and transitioned me to SAP and ABAP. Sadly, they dropped Crystal Reports and they would not adopt the world of SAP Stored Procedures because these do not get assigned to Transport Management System and this goes against the recommended SAP Way. You may consider me as a beginner in SAP and I am enjoying learning about it.

Since joining SAP, I have been challenged by the limitations of Open SQL and to a lesser extent SQL Script (at release 7.50) compared to MS/SQL. These limitations can lead to poorly designed CDS Views or overly complex views, built on views to get result that should ideally have been built in a single view.

I have also noticed issues with the database design of ECC and CRM which work against people embracing SQL on SAP in many places. This is particularly noticeable for CDS views due to Open SQL being even more limited in functionality compared to SQL script.

Some of the practical limitations which I found are:

  1. GUIDs stored as RAW in one table and as CHAR in another are incompatible as join fields (a major issue when GUID is a primary key).
  2. Data stored in a base format in one table and in a converted format in another table cannot be used in join criteria, even though it is essentially the same value.
  3. Text stored in encrypted form (i.e. field CLUSTD in table STXL), cannot be decrypted by SQL, so cannot be included in SQL output.
  4. The value of the COALESCE function is significantly reduced because SAP do not store unknown values in tables as Null, but rather as blank or zero.
  5. Complex fields that need to be built with functions, such as ADDRESS_INTO_PRINTLINE, cannot be used in SQL. Easy to overcome if SAP provides a formatted address table to compliment the existing address fields.

One of the first things I was given to do on joining the SAP team was to review various reporting tools for operational reporting. My recommendation was Crystal Reports at the end of the day due to it being able to leverage off Stored Procedures and the greater functionality of SQL Script. Having now worked with ABAP for around a year and written a few direct reports in ABAP, I still maintain my view that Crystal Reports should be adopted, as development of a report using SQL stored procedures and Crystal is simply much quicker than with ABAP, although that may be influenced by my being a beginner.

Query language

In order to evaluate the reporting tools it is important to understand the differences between the two query languages that are used to extract data from HANA; these are Open SQL and SQL Script.

Open SQL is used to create CDS views which are used to generate data for tools such as KPI modeller, APF and Web Intelligence.
SQL Script is used to generate functions and stored procedures as used for reporting tools such as Crystal Reports.

SQL Script is considerably better for extracting data than Open SQL, being feature rich and including capabilities that are lacking in Open SQL.

Some of these features (of SQL Script) are particular significant for operational reporting, such as the ability to convert dates from UTC time to NZ standard time (NZST) or any time for that matter, and the ability to do working day calculations on dates. Many other features also make SQL Script favorable as the reporting language of choice.

I have tried to highlight the main differences between Open SQL and SQL Script in the below table.

Requirement Open SQL   SQL Script 
Cast a field as a new data type Being able to cast fields as different data types is an important part of reporting, for example casting a character field to a datetime field. ABAP CDS views use a different method of casting allowing casting to ABAP data types only. Yes with restrictions Yes 
Join a RAW(16) GUID to a char(32) GUID Required to join key tables together to get information. Without this capability we cannot create a query that brings together core elements of the data for reporting, such as obtaining an employee name, processing group, or reporting doc flow.  No – but can be worked around with intermediary Dictionary View. Yes
Can include a sub-select query in the main query Nested selects are an essential part of reporting in order to reduce records down to just the one record of interest. This occurs when records have a sequence of entry or are date time dependent and you want the record with the latest date and time only. Nested selects also allow for visibility of code all in one place, rather than having to create multiple views to serve the purpose of the nested query.  No Yes 
Use SAP specific Working days functions For example ADD_WORKDAYS and WORKDAYS_BETWEEN. These functions are essential for date specific calculations as they take into account the calendar.  No  Yes 
Ability to sum the results of a calculation It is sometimes necessary to get the sum of a calculation, for example SUM(fielda + fieldb). To achieve this with CDS requires two CDS views, one to perform the inner sum, then one to do the group summation.  No  Yes 
Link tables to functions Functions provide for reusable code that takes advantages of the higher SQL functionality not available to CDS views. A function is similar to a view, except it places no overhead on the system until it is used by an active query. A view on the other hand is an active database object that changes as records are changed in the database.  No Yes 
Provide parameters Parameters are a way of reducing the overhead of executing the SQL by reducing it to a limited set of output rows.  Yes with limitations Yes 
Transport using standard transport The standard transport process makes it easy to track code from one environment to the next.  Yes  No 
Meaningful naming Providing a meaningful name makes it easier to see what you have in your repository and associate related objects together. CDS views have a 16 character limitation on the view name, which is a bit restrictive.  Restrictive  Yes 
Convert dates to NZ standard time Many of the datetime fields in SAP need to be converted to NZ standard time. SQL function utctolocalprovides this conversion but this cannot be used in views.  No  Yes 
Use of temporary tables, or table variables Temporary tables or table variables provide a way of pre-querying some data, then using this data for further selection. This can lead to significant performance improvements of the overall SQL code, compared with joining to multiple views or multiple functions. It also allows for the code to be fully contained in one source, broken down into simplified sections, so it is easier to understand.  No  Yes 
Use of TRIM function The TRIM(<field>) function allows removal of superfluous spaces from a field.  No  Yes 
Functions available in join condition Certain field functions are required to enable join logic to work, but the function is not always available in CDS views at the join level, making the join impossible. An example is that of using the LIKE function, where the join needs to join based on a field being like a partial value i.e. LIKE ‘ABC%’. Another example is use of the CASE statement, where the join needs to be conditional.  No  Yes 
Use of Union At times we have a requirement to union results from different tables and present as a single output. Data types can be an issue with ABAP CDS views, for example a RAW(16) GUID cannot be in a union with a CHAR(32) GUID, and a UNIT(3) field cannot be in a union with a char(12) field.   Limited due to data type mismatches, can lead to unpredictable results and inability to union certain data types.  No issues 
Use of variables  No  Yes 
Use of conditional logic IF/ELSE  No  Yes 
Reusability The ability to reuse code is important in that a piece of logic only need be written once and used in many reports. CDS views can be combined for this purpose, and can be the building blocks for many reports, however the more complex the report, the more likely that a final CDS view will be tailored for the single report. This is not that different from a stored procedure making use of multiple re-useable CDS views and functions. A final report Stored Procedure like a final CDS view can be used by many reports that share the common input requirements. SAP recommends providing “well-tailored” views instead of the one view for all purposes, which can cause performance hits.  Yes  Yes 
Performance Performance is an important consideration as users want their reports to execute quickly. Stacking multiple CDS views to obtain a result can cause a performance hit. Stored procedures are better able to optimise code to reduce performance hits.  Good  Better
Reporting tool CDS views are particularly designed for Analytic reporting, however this is HANA CDS Views as opposed to ABAP CDS views. These are for use with tools like KPI modeller, Analyse Path Framework (APF), and Lumira. HANA CDS views are not recommended for use at our current release level due to limitations. Stored procedures are consumed primarily by Crystal Reports which is not designed as an Analytic Reporting tool, although analytic reports can be written based on a set design. Stored procedures cannot be consumed by KPI Modeller, APF, or Lumira. They can be consumed by a universe, but it is not recommended.  Universes, KPI modeller, APF, Lumira, Web Intelligence, Crystal Reports, ABAP  Universes, Crystal Reports, ABAP
Users can self-develop code
Code for CDS views and Stored Procedures must be developed via a developer. The same applies to universes. Only when the underlying view, procedure, or universe has been created can users develop reports over them. 
Some  No 

In the below table I have tried to compile the available SQL functions in Open SQL and SQL Script (7.40).

Function Type  Open SQL  SQL Script 
AVG/AVERAGE Aggregate 1
COUNT  Aggregate 
MAX/MAXIMUM   Aggregate 
MEDIUM  Aggregate 
MIN/MINIMUM   Aggregate 
MODE  Aggregate 
SUM  Aggregate 
BITAND   Binary 
BITCOUNT  Binary 
BITNOT   Binary 
BITOR   Binary 
BITSET Binary 
BITUNIT  Binary 
BITXOR  Binary 
COALESCE Comparison
IS NULL Comparison 
LIKE Comparison 
BINTOHEX Comparison 
BINTOSTR  Comparison 
CAST  Comparison  1
CURRENCY_CONVERSION  Comparison  0
DECIMAL_SHIFT  Comparison 
HEXTOBIN  Comparison 
STRTOBIN  Comparison 
TO_ALPHANUM  Comparison 
TO_BIGINT  Comparison 
TO_BINARY  Comparison 
TO_BLOB  Comparison 
TO_CLOB  Comparison 
TO_DATE   Comparison 
TO_DATS  Comparison 
TO_DECIMAL  Comparison 
TO_DOUBLE Comparison
TO_FIXEDCHAR  Comparison 
TO_INT Comparison 
TO_INTEGER   Comparison 
TO_NCLOB  Comparison 
TO_NVARCHAR  Comparison 
TO_REAL  Comparison
TO_SECONDDATE  Comparison 
TO_SMALLDECIMAL  Comparison 
TO_SMALLINT  Comparison 
TO_TIME Comparison 
TO_TIMESTAMP  Comparison 
TO_TINYINT  Comparison 
TO_VARCHAR  Comparison
ADD_WORKDAYS Date time
CURRENT_DATE  Date time 
CURRENT_TIME  Date time  0
CURRENT_TIMESTAMP Date time  0
CURRENT_UTCDATE  Date time 
CURRENT_UTCTIME  Date time 
DATS_ADD_DAYS/ADD_DAYS Date time 
DATS_ADD_MONTHS / ADD_MONTHS Date time 
DATS_DAYS_BETWEEN / DAYS_BETWEEN Date time 
DATS_IS_VALID Date time  0
DAYNAME  Date time  0
DAYOFMONTH  Date time 
DAYOFYEAR  Date time 
EXTRACT  Date time 
HOUR  Date time 
ISOWEEK Date time 
LAST_DAY Date time
LOCALTOUTC Date time 
MINUTE  Date time 
MONTH  Date time 
MONTHNAME  Date time 
NANO100_BETWEEN  Date time 
NEXT_DAYDate time Date time
NOW  Date time 
QUARTER  Date time 
SECOND  Date time 
TIMS_IS_VALID  Date time  1 0
TSTMP_ADD_SECONDS / ADD_SECONDS Date time 
TSTMP_CURRENT_UTC_TIME / CURRENT_UTCTIMESTAMP Date time  1
TSTMP_IS_VALID Date time  0
TSTMP_SECONDS_BETWEEN / SECONDS_BETWEEN Date time 
UTCTOLOCAL Date time 
WEEK  Date time 
WEEKDAY  Date time 
WORKDAYS_BETWEEN  Date time 
YEAR  Date time
ABS  Numerical 
ACOS  Numerical  0
ASIN  Numerical 
ATAN  Numerical 
ATAN2  Numerical 
CEIL  Numerical 
COS  Numerical 
COSH  Numerical 
COT  Numerical 
DIV  Numerical  0
DIVISION  Numerical 
EXP  Numerical 
FLOOR  Numerical 
LN  Numerical 
LOG Numerical 
MOD  Numerical 
ROUND  Numerical 
UNIT_CONVERSION Numerical  0
CURRENT_SCHEMA  String
SESSION_USER  String  0
CHAR  String 
CONCAT  String 
CONCAT_WITH_SPACE  String  0
INSTR  String
LEFT String  1
LOCATE String  1
LOWER/LCASE  String  0
LPAD  String 
LTRIM  String 
NCHAR  String 
REPLACE  String 
RIGHT  String 
RPAD  String  1
RPAD  String 
RTRIM  String 
SUBSTRING String  1
SUBSTRING_AFTER String  1
SUBSTRING_BEFORE  String 
TRIM  String 
UPPER/UCASE  String 
ASCII  
UNICODE   0

On top of the above differences between Open SQL and SQL script, the table below shows additional limitations of Open SQL compared to SQL Script that I have identified.

IN  IN logic in not permitted in Join conditions or Where conditions cannot say “where form_type in (‘YAMN’, ‘YBCA’)” Have to instead say “where form_type = ‘YAMN’ or form_type = ‘YBCA’”
CASE  Case logic not permitted in Join conditions or Where conditions 
CAST  Cannot us cast in Join conditions or Where conditions Cannot cast certain ABAP field types to standard field types, such as RAW, STRING, and UNIT 
NULL  Cannot set output fields to NULL 
UNION  Cannot union type mismatched fields, for example GUIDS 

While we were reviewing this article, one ABAP developer pointed out that we can perform IN and CASE operations in Open SQL as shown below.

I understand the above is possible. I wanted the IN in the JOIN condition like below:

In MS/SQL this is possible. But in SAP, I think we might need to write like below. But I think it is less elegant coding.

Okay, I tried the above snippet in ABAP CDS view definition. I tried to define join criteria using the CASE statement and it says “Unexpected word “Case””, so this is not permitted.

Also tried defining join criteria using IN, as shown in above image and this also returned the “Unexpected word “In”” message.

Next tried the CASE and IN statements on the WHERE clause and both also failed. We are at 7.50 now, and these findings relate to ABAP CDS views using Open SQL.

These are just few of my findings. There might be more which I am not aware. In the coming article series, I will try to write something about the SAP HANA Operational Reporting. We will try to compare the different Reporting Tools (KPI Modeller, APF (Analysis Path Framework), Lumira, MS/Excel, Crystal Reports, Web Intelligence and ABAP reporting). We will also check the different Methods of Data Retrieval (Direct access, Views, Stored Procedures, Functions, Data Warehouse etc).