Preface

Query and reporting applications often involve complex computations. Since data is typically stored in databases, these computations are usually implemented using SQL. However, SQL has multiple disadvantages, such as incomplete set-orientation, lack of inherent ordering, difficulty in performing step-by-step computations, and debugging challenges, which often lead to cumbersome or even unwritable code. As a result, various difficulties arise frequently. A clear example of this is the globally renowned IT Q&A websitestackoverflow.com,where people post questions almost every day seeking solutions to challenging SQL problems.

In contrast, esProc SPL (Structured Process Language) achieves more thorough set-orientation, features inherent ordering, supports step-wise computations, eases debugging, and simplifies coding, significantly reducing computational complexity.

SPL can be embedded into applications, connect to databases to read data, and then perform complex computations. This process only requires very simple SQL for data retrieval, thus avoiding difficult to write SQL.

This article, targeting various complex computational tasks, explains SPL’s problem-solving methods in separate chapters, including: retaining grouped subsets, order-based computations, relative position reference, order-based grouping, dynamic row-to-column/column-to-row transposition, and so on.
The article is accompanied by a number of txt files, three of which – Orders, Employee and Client, serve as public datasets for most of the examples. Their data structures are as follows:

The special datasets some examples need will be specifically provided when used.

Table of contents

Preparation