Example refactoring series taking a complex SQL and making it easier one step at a time.
On 15.02.2020, a rather rain-drenched stormy day in Melbourne, Australia, the SAP Inside Tracek #sitMEL – Full Day Event was held.
I was honored to get a speaker slot and the chance to talk about "Humane DB design and programming".
A main point of this presentation is to refactor SQL code towards understanding. In the presentation I walk through two examples of code refactoring.
This repository contains the code for the examples.
This example I found in the SAP Community Platform forum and wrote about it before in my blog post SQL Refactoring Example.
This code however, goes a bit farther with the refactoring.
Another example from the SAP Community Platform Q&A forum.
A rather classic report of outstanding payments by customers over time-periods.
This example covers more different refactoring steps and highlights newly gained insights about the statement along the process.
The examples covered here are for educational/entertainent purposes only.
I do not claim that this is production-ready code, or a reference implementation of solutions or anything beyond being an example for how refactoring code can lead to easier maintainable code and a better understanding of what the code does.
I am happy for anyone to have a look at it but do not expect it to match your requirements.
For each example there is a separate folder containing all files belonging to the example.
As the examples demonstrate steps of code change there is one file per step.
The files are named step00 - original.sql - for the original SQL statement and step X - <whatever happens in this step>.sql.
The idea is to run the original code and the changed versions side by side to spot differences in the result set and the runtime quickly.
For example 2 there are also files (create schema) to create the tables referenced in the code and to populate them with some data.
For ad hoc volume testing, there is also a file create big schema.sql that generates Millions of records in the referenced JDT1 table.
The mock data I used in this example has been created using Mockaroo and does not have any real-life meaning at all.
Speaking of the tables: the original question referred to a SAP Business One system and the referenced tables seem to stem from there.
As I do not have access to such a system, I googled the table definitions (found here JDT1 and here OCRD) and removed any columns not referenced in the statements.
This means, these are not the tables that you can find in a SAP system!
Again, do not use these coding examples in your productive systems.
The code has been developed with a SAP HANA Express Edition VM (HANA 2 SP04).