<p> </p><p>Access your Tables directly using SQL in your routines instead of ABAP Open SQL.<br />Many a times you will have situations where you will have to do a lookup on very large tables but then not too sure if the same would work in the best way possible.</p><p>And hit a solid wall when trying to find out what exactly happens in the backend database system. And your Basis or DBA will not be of help here since they do not understand ABAP and talk in a language totally different called SQL written and tested using editors like SQLPLUS through the Operating system command prompt or use tools like TOAD etc.</p><p>In this case if you are more comfortable with SQL as opposed to ABAP SQL ( Open SQL ) you have a way of doing the select statements that we have a love hate relationship with in a different way.</p><p>What is the difference between Native and Open SQL?</p><p>Databases evolved over time and each database has an SQL Library which differs ever so slightly between databases. For example an SQL statement written for an Oracle Database need not always work in an SQL Server environment. There are numerous parameters like Hints, Indices etc which sometimes can become specific to databases.</p><p>How does SAP handle it?</p><p>SAP being database neutral supports another standard called Open SQL which is the select statements that we see regularly. There is an SAP kernel and a set of DB Shared libraries that do the task of translating the Open SQL into database specific SQL. As a result in many cases if you want to tune your query to the UBER level which your DBA might suggest - you will not have all the options to do so within the confines of OPEN SQL.</p><p><br />A typical example might be:</p><p>Let’s say you have Sales order line items for the last 5 years amounting to about 12 million records. You would like to look this up and do an update to the same through a program for various reasons - but then it becomes tough because you do not know how long this will take and the DBA for your system suggests that there are SQL scripts that they can give you which will make it easier and faster for your process to run , but then do you convert that SQL script into Open SQL or use it as it is - because the original SQL script might also have hints which make it faster on that specific database...</p><p><br />How do you do it....?<br />
<textarea cols="55" rows="20" name="native SQL"> EXEC SQL.
INSERT /*+ APPEND */ INTO "<table name>"
(
"<Field 1>",
"<Field 2>",
"<Field 3>",
)
SELECT
DECODE( RI.ROW_INDEX, 1, FC."<Field 1>", FC."<field 2>" ) AS "<field 1>",
FC."<field 2>",
FROM
(
SELECT /*+ ORDERED INDEX( FC "<Table Name>" ) USE_HASH( AM FC ) USE_HASH( MT ) */
FC."<field 1>",
AM."<Field 2>",
SUM( <Field> ) / 100 AS "<field>",
FROM
"<Table name>" AM,
"<Table Name>" FC,
WHERE
FC."<Field Name>" = :parameter
GROUP BY
FC."<Field 1>",
) FC,
(
SELECT
ROWNUM AS ROW_INDEX
FROM
"<Table Name>"
WHERE
ROWNUM <= 2
) RI
ORDER BY
FC."<Field Name>",
ENDEXEC.
</textarea><br />
<br />The initial program for this was about 300 lines of code with indices etc and still we got the program to run in about 45 minutes or so - with the native SQL - we first validated and tuned the code with the DBA and then the program ran in about 30 minutes. The resultant code was also only about 50 lines of code in all...<br />Also I have removed the fields and their descriptions to mask client specific information. <br /><br />Advantages:- Maximum usage of all the features that a Database has to provide
- All databases have specific features to enable select statements and insert updates etc to run faster - but then these features being database specific - cannot be utilized easily within the constraints of Open SQL.
Disadvantages:- The statement within the EXEC SQL cannot be debugged or checked for syntax - if it is wrong you will get a short dump and that’s all...
- the code cannot be ported easily when porting across multiple systems with different databases - code written is specific for the database - do not expect it to work outright when copying code from an Oracle system to SQL Server or DB2 system.
This is intended to part of a blog series talking about usage of SQL in a datawarehousing environment and where and how SQL can be used instead of Open SQL and possibly tuned further. Please let me know your views on the same...
Also this does not necessarily lend itself to SAP BW Alone but have highlighted a case where it was used in an SAP BW Environment.
