Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Monday, 03 June 2019 01:11

Limitations of Open SQL Compared to SQL Script in HANA

Written by  https://sapyard.com/limitations-of-open-sql-compared-to-sql-script-in-hana/
Rate this item
(0 votes)

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. ?

Also Take:Free Video Course on New Features and Syntax in SAP ABAP 7.4

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.

RequirementOpen SQLSQL 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

Also Read:Difference between Open SQL and Native SQL

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

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

Also Read:Open SQL, CDS or AMDP, which Code to Data Technique to use?

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).

As I mentioned in the beginning, being a non-ABAP developer all my life might influence my opinions. But I am not one of those guys who are not open to change. I do not crib about what SAP cannot do. I am happy to be an SAP Consultant and Developer. I have embraced SAP and ABAP with open heart and I love SAP. I just wish, SAP had the flexibility of all the tools and scripts I have been using before I moved on to SAP. You never know, SAP Product Owner might be reading this and they might get some feedback and idea to improve the product. ?

Please let us know if you know of any other limitations or advantages which we can update in this post.

Please write your comments below.

We have a very active Telegram (App) SAP Technical Group with more than 4930+ SAP Technical Practitioners from 6 Continents of the SAP World. Please join it using below link.
Telegram SAP Technical Discuss Group. You need to install the Telegram App first on your mobile device. Once you have it on your mobile, you can join the group and also access it from the Web on your computer and laptop.

Please SUBSCRIBE to SAPYard’s Youtube Channel for Free End to End SAP Video Course and Training.

Check HANA-ABAP Tutorials

Read 98 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.