The most elementary requirement of SQLScript Code is that it works correctly. That already given, we must ensure readability, transparency and maintainability of our code. This is crucial, as existing reporting solutions often have to be subsequently corrected or expanded. The reasons for this can be manifold: new regulatory requirements, new user requests or simply errors in the implementation.
For everything to run smoothly we have to maintain a clean and legible codebase as a basic requirement. Well written SQL code can be read as easily as a book, even without much context. It must always be clear what is happening in the source code and for what reason. If necessary, even an uninvolved programmer must be able to quickly identify the appropriate part of the coding and execute necessary adjustments.
This holds true for the original developer as well. Even with your own developments, it can happen that after a while you no longer find your way around. With comprehensible and well-structured code, troubleshooting is easier and faster. So how do you keep your code clean? In this article we will provide you with a few ideas.
Formatting
One of the simplest approaches to make SQLScript more readable is using appropriate formatting.
Line breaks
From a technical perspective each SQL query can be written in a single line. However, that would cause many queries to become too long and difficult to read. Therefore you should always use only one line per field and condition. For example:
SELECT
CustomerName,
City
FROM
Customers
WHERE
Country='Germany';
Blank lines
The correct use of blank lines can also improve readability. Avoid blank lines within a statement and instead use blank lines to separate the individual blocks of your program, e.g. variable declaration.
DO BEGIN
lt_customers_in = SELECT
CustomerID
FROM
Customers
WHERE
Country = 'Germany';
lt_employees_in = SELECT
EmployeeID,
Amount
FROM
Orders
WHERE
CustomerID in (SELECT * FROM :lt_customers_in);
SELECT
EmployeeID,
SUM(Amount) AS Amount
FROM
:lt_employees_in
GROUP BY
EmployeeID
ORDER BY
Amount DESC;
END;
Indentation
To make your code even more readable, indent fields and conditions after commands. Use indentation for subqueries as well.
So instead of using a left-aligned format, as in the example below...
SELECT
CustomerName,
City
FROM
Customers;
... your code looks like this:
SELECT
CustomerName,
City
FROM
Customers;
Capitalization
To make it easy for everyone (and also your future self) to figure out what operations are being performed, you should capitalize all SQL statements.
This includes keywords such as SELECT, FROM, WHERE, GROUP BY, ORDER BY, but also statements used within clauses, such as JOIN, AS, IN, as well as ON. Functions like SUM() or COUNT() should also be capitalized to better distinguish them from column names.
Speaking Names
Use meaningful, i.e. self-explanatory, names for variables. The purpose of the variable should be clear from the name. For example the variable for family names should be called lv_familyname, not var1. The name of a function should also directly communicate what it is used for. Accordingly, a variable name will usually result in a noun, while a function name will be a verb.
Increase the performance of your BW with SQLScript!
Click here for the whitepaper!
Comments
Developer comments help immensely when reading a program. Especially in spots where the coding is unintuitive and hard to understand. However, it is also important not to overdo it. Too many comments can hinder the reading flow, so only write comments when it is necessary. If you follow our advice, use meaningful names and write modular code, you should not have to write too many comments.
For example, the following comments are completely superfluous:
/* Show addresses for German customers */
SELECT
ContactName, --Name
Address, --Address
City, -- City
PostalCode --Postal code
FROM
/* Table with customer data */
Customers
WHERE
/* Selection criteria */
Country = 'Germany' --Select Germany only
On the other hand, comments are necessary when it is not obvious what you are trying to accomplish just from reading the code by itself. A good rule of thumb is that comments should answer the question "why" you are doing something, rather than "what" you are doing.
Apart from comments in the code itself, header comments providing a summary or high level overview of the individual steps are also helpful.
/*
Purpose: Lists salesmen that have registered more than 10 orders
Executed steps:
1.Format name
2.Enhance with respective team
Author: Sebastian Uhlig
Change History: Creation 14.12.2022
*/
Split queries into multiple steps
Normally, a block of code in SQL is always executed at once. By dividing your coding into several steps, you can find causes of errors more easily afterwards. The separation allows you to analyze the individual steps in the debugger and check whether the expected result matches the actual result.
We recommend the following order:
- Input selections
- Aggregations
- Calculations
- Joins
- Output selection
By the way, ORDER BY is often used in all of these steps, but it is only relevant in the output selection. By eliminating superfluous statements, your code will be less cluttered.
In SQLScript you can use table variables for subdivision. This makes the queries easier to read. It is obvious at first glance which sequence has been defined and which steps are executed before the final result is displayed.
For example:
DO BEGIN
-- Select german customers
lt_customers_in = SELECT
CustomerID
FROM
Customers
WHERE
Country = 'Germany';
-- Identify respective salesmen
lt_employees_in = SELECT
EmployeeID,
Amount
FROM
Orders
WHERE
CustomerID in (SELECT * FROM :lt_customers_in);
-- Show total sales per salesman in Germany
SELECT
EmployeeID,
SUM(Amount) AS Amount
FROM
:lt_employees_in
GROUP BY
EmployeeID
ORDER BY
Amount DESC;
END;
Use UDFs
As mentioned earlier, modularization helps us find bugs in the code faster. Besides separating the query into several steps, you can also use User Defined Functions (UDFs). They can help to reduce complexity and increase readability. The functions can also be tested individually and provide individual, verifiable results, which is helpful with larger queries.
Another advantage of UDFs is their reusability. You can write the function once and use it in multiple queries. For example, you want to perform a concatenation of surnames and first names:
SELECT
FirstName || ' ' || LastName AS Name
FROM
Customer;
Instead of using this piece of code in many individual queries and thus overloading the code, you can create a UDF for it and reuse multiple times:
CREATE FUNCTION udf_concatenate_name(
lv_firstname NVARCHAR(30),
lv_lastname NVARCHAR(30)
)
RETURNS lv_name NVARCHAR(61)
AS BEGIN
lv_name = lv_firstname || ' ' || lv_lastname;
END;
SELECT
CustomerID,
udf_concatenate_name(FirstName, LastName) AS Name
FROM
Customer;
SQLScript Code - Our Summary
Now you have learned several approaches to make your SQLScript code more readable and facilitate subsequent maintenance. Always remember: good SQL code is not that which computers understand, but that which humans understand.
Do you have questions about SQLScript? Or do you want to convert your transformation routines to SQLScript and are looking for experienced developers with SQLScript know-how? Please do not hesitate to contact us.
Further information can be found in the book "SQLScript for SAP HANA" (SAP PRESS, ISBN 978-3-8362-7408-1) by Jörg Brandeis.
