SQR is a high level language like Basic but with built in SQL capabilities - it became very popular with PeopleSoft in the early 90's and is still a mainstay of that product. It can run on many platforms and use many databases
SQR is a programming language designed for generating reports from database management systems. The name is an acronym of Structured Query Reporter, which suggests its relationship to SQL (Structured Query Language). Any SQL statement can be embedded in an SQR program.
Features
SQR is notable for its powerful database and printing functions. It can embed any SQL statement almost anywhere in a program. There is a version of SQR that can use multidimensional databases like Essbase. It can combine database reads with print instructions. It flexibly formats data breaks and page breaks. It prints variable fonts, sizes, and colors. It has a graph generation command that offers dozens of parameters for adjusting content and appearance.
The language is very easy to learn but has some nuances. It is normally compiled at run time, however, it can also be pre-compiled, which does save time on larger programs.
Syntax
SQR has four scalar data types. The first three are:
- numeric (variables begin with “#”)
- character string (variables begin with “$”)
- and date (variables begin with “$”, same as with character string variables.).
Date variables must be declared, to be distinguished from character string variables. There is the option to declare numeric variables to specify them more precisely (integer, floating point, etc.). The last data type is a database column (variables begin with “&”). The values of database columns are set only by a SQL “select” statement; no other command can change their values.
SQR also has arrays, like those of the C programming language or COBOL. An array has one or more fields, where each field is either a numeric, string, or date variable. Each field may have multiple occurrences, giving SQR the equivalent of two-dimensional arrays. SQR has special commands that manipulate multiple items within a single array. There are many commands that cannot use an array element in place of a scalar variable.
The language has four primary control structures. The first three are:
- if-else-end
- while-end
- and evaluate (like case or switch).
The fourth is the embedded SQL “select” statement, which allows SQR statements to be executed for each row of data in a loop.
SQR has commands to open, read, write, append, and close computer files. File input and output is sequential and record-oriented only; random access is not supported.
SQL Select statements are built into the language using Begin-Select and End-Select statements. It can also run native SQL for Inserts, Updates, Deletes, Truncates, etc within Begin-SQL and End-SQL statements.
Within a Begin-Select and End-Select block, each row returned from the query can be trapped and used to either print or call other procedures that may call other SQL statements (or do other logic).
It can be used to produce very pretty reports (a PDF format is an option as well as HTML and CSV formats) and can also read and write files very easily. Multi-dimensional arrays are also supported.
One of the most under-utilized features is the built-in on-break mechanism. Instead of having to manually set break-points when switching groups, this language has an on-break clause that can be attached to fields being returned from the SQL query - using these, Before and After operands allow calling of procedures. For example:
Begin-Select
Company() on-break print=change/top-page before=Init-Company after=Process-Company Level=1
Name () on-break print=change/top-page before=Init-Employee after=Process-Employee Level=2
From PS_Employees
Order by Company, Name
End-Select
This logic will break on company and employee automatically and call before and after procedures without any flags or grouping required.
SQR is a language still currently being used and it remains a top report and processing program language with PeopleSoft systems.
