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:
- 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).
- 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.
- Text stored in encrypted form (i.e. field CLUSTD in table STXL), cannot be decrypted by SQL, so cannot be included in SQL output.
- 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.
- 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 | 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 | Comparison | 1 | 1 |
| BINTOSTR | Comparison | 0 | 1 |
| CAST | Comparison | 1 | 1 |
| CURRENCY_CONVERSION | Comparison | 1 | 0 |
| DECIMAL_SHIFT | Comparison | 1 | 0 |
| HEXTOBIN | Comparison | 1 | 1 |
| STRTOBIN | Comparison | 0 | 1 |
| TO_ALPHANUM | Comparison | 0 | 1 |
| TO_BIGINT | Comparison | 0 | 1 |
| TO_BINARY | Comparison | 0 | 1 |
| TO_BLOB | Comparison | 0 | 1 |
| TO_CLOB | Comparison | 0 | 1 |
| TO_DATE | Comparison | 0 | 1 |
| TO_DATS | Comparison | 0 | 1 |
| TO_DECIMAL | Comparison | 0 | 1 |
| TO_DOUBLE | Comparison | 0 | 1 |
| TO_FIXEDCHAR | Comparison | 0 | 1 |
| TO_INT | Comparison | 0 | 1 |
| TO_INTEGER | Comparison | 0 | 1 |
| TO_NCLOB | Comparison | 0 | 1 |
| TO_NVARCHAR | Comparison | 0 | 1 |
| TO_REAL | Comparison | 0 | 1 |
| TO_SECONDDATE | Comparison | 0 | 1 |
| TO_SMALLDECIMAL | Comparison | 0 | 1 |
| TO_SMALLINT | Comparison | 0 | 1 |
| TO_TIME | Comparison | 0 | 1 |
| TO_TIMESTAMP | Comparison | 0 | 1 |
| TO_TINYINT | Comparison | 0 | 1 |
| TO_VARCHAR | Comparison | 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_DAYDate time | 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 | String | 0 | 1 |
| SESSION_USER | String | 0 | 1 |
| CHAR | String | 0 | 1 |
| CONCAT | String | 1 | 1 |
| CONCAT_WITH_SPACE | String | 1 | 0 |
| INSTR | String | 1 | 1 |
| LEFT | String | 1 | 1 |
| LOCATE | String | 1 | 1 |
| LOWER/LCASE | String | 0 | 1 |
| LPAD | String | 1 | 1 |
| LTRIM | String | 1 | 1 |
| NCHAR | String | 1 | 1 |
| REPLACE | String | 0 | 1 |
| RIGHT | String | 1 | 1 |
| RPAD | 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 |
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).
