There are two types of SQL Statements in SAP System.
- Open SQL
- Native SQL
We prefer working with the Open SQL statement because of its simplicity and it is database independent. On other hand Native SQL are the commands that we directly execute on the database itself and the query you write differs according to the type of database used.
Native SQL has more commands and is more powerful, yet we cannot use them because if the company choose to migrate to another database then there is again going to be a rework of every call made to the database to adapt to new database SQL syntax.
The only situation that force us to think of Native SQL is that the query which we cannot execute with Open SQL.
With ABAP, 7.40 release, SAP Has introduce a lot of features in Open SQL to extend the query possibility.
New SQL Commands in Open SQL:
- Case Statement
- Calculation within SQL Statements
- Buffering Improvements
- Create while reading
- Inner Join Improvements
Before we write the New Open SQL Statements keep in mind the following syntax has to be used as part of new “Strict” syntax.
- Data Variables has to be suffixed with the symbol @ for the compiler to differentiate the variables from application layer.
- Fields list has to be separated by Comma.
Old SQL Statement:
select matnr mtart mbrsh from mara into table lt_mara.
New SQL Statement:
select matnr, mtart, mbrsh from mara into table @lt_mara.
Note: The Strict syntax comes into play when the compiler sees you have used one of the new features. Say for example if you use case keyword( new feature) and if you don’t follow strict syntax you will get an hard error.
One of the new features in ABAP 7.4 is the ability to add the case statement in the select statement. If you have scenarios where you wanted the certain set of fields to be calculated based on different conditions then you can add it in the case statement and calculate within the database layer as part of the code push down rather then loading the data in Application server and calculate it.
Another new ability with release 7.40 is that you can perform the calculation within the database layer itself rather than bringing it to application server and play with it.
Reference Link: https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abensql_expr_arith_abexa.htm
Example: Arithmetic Expression ( Subtract ).
Buffering has been improved as of release 740. The Transaction code ST05 is often used in a situation where we are going to track the database access and reduce the access in order to improve the performance by using buffer access.
Though buffering has been available for long time there has been some problem around it.
Let say for an example you have an table ( zt_employees ). You have field EMP_ID which is the key field of the table and employee_name which is non key field. Let’s assume this is a master table and the records are relatively small and hardly changed, hence you set the table buffering mechanism to fully buffered.
Now in some case you wanted to access the table with emp_id which is the keyfield and in some cases you wanted to read the entry with emp_name which is non key field.
Now when you execute the query with the EMP_ID and perform SQL trace in ST05 then you will see that no database access happens at all because this is a fully buffered table and all the table entries are buffered during the previous fetch and hence system reads the data from buffer itself.
Now execute a query with the emp_name and perform the SQL trace in ST05 and you will notice that the database query made to database layer to fetch the data. This is because you are using an non keyfield value and the buffer is bypassed. One common method to avoid this before release 7.40 is read all the record from the table store it in internal table sort it by emp_name and read it with binary search.
With ABAP 740, you don’t have to do this, now the call to the database even without the key fields can be fetched from buffer.
FOR ALL ENTRIES:
Up until now you can perform for all entries in the buffered table even after specifying the primary keys, this is also now a thing in the past.
Creating data while reading
In Older version of ABAP, you need to declare the types for the internal tables first before fetching the data from the database if you want to read only certain amount of fields.
If you wanted to add additional fields then you have add the field in the type declaration as well as the select condition. You can avoid these changes and have an compatible internal table by declaring the internal table at the type of reading the data from database.
Depending on the list of fields that you read from the database table, the internal table with created with those fields
You can avoid type declarations as above.
Inner Join Improvements
Mostly while we work on real time scenarios where the data is split across multiple table we have the need to perform inner join and read the data from multiple table in a single database access.
Previously we have to individually list the set of fields that are to be fetched from each table. This can be now eliminated with the use of ~ symbol.
In Addition to the above now we can also provide conditions in the Join using the keyword “LIKE” or “IN”.
I am an SAP Netweaver Certified developer associate. I work in UI5 , Fiori, ABAP and HANA topics. I do freelance projects and also takes training in SAP UI5 and ABAP. For Training/ Freelance requirements reach out to me through Contact page