Introduction to Magic SQL

Magic SQL

Magic SQL is a powerful auto-generated hidden smart scripting feature that enables Statelake to keep track of the relationship between parent and child records across tables in a Data Definition.

There are three ways of scripting SQL code -

Manual SQL - the code will stay as manually entered.
Parameterized - the code fires each time the master record position changes, where the SQL is augmented with a parameter linked to the master record.
Magic - the code fires once for the entire report, where the SQL is augmented with search and sort information from the master record.

Using the table DR_ACCS, parent table DR_TRANS and linked child table DR_INVLINES as an example in a DB Definition, without the introduction of Magic SQL, at runtime when the DB Definition queries are executed and records are populated from the database, Statelake would select only the records specified by the selection criteria from the parent DR_TRANS table, but every single record from the child DR_INVLINES table would be loaded into memory.

Depending on the quantity of records involved, this could result in unnecessary massive memory usage which could have a major negative effect on performance and response time.

Magic SQL is the answer to this problem.

Through the use of Magic SQL implementing the code changes, Statelake is able to filter out the unnecessary records from the child dataviews at SQL server level, enabling the processing of the records specifically required to satisfy the queries. This lets SQL - which is optimized for this kind of relational query - sort out the most efficient way to filter down to just the data that Statelake needs, to support efficiency and minimise any negative memory impact.

And it does it all without you having to lift a finger.

While Magic SQL may be the default setting when linking tables in a DB Definition, this option can easily be changed to the others as above, through changing the settings once tables have been linked.

A working example illustrating Magic SQL can be found in Tutorial 5, by importing the configuration Statelake_Magic.

The following examples show how the code is automatically changed, depending on the setting selected.

Example of the code in the linked child DR_INVLINES dataview (MANUAL SQL).

SELECT DR_INVLINES.SEQNO, DR_INVLINES.HDR_SEQNO, 
       DR_INVLINES.STOCKCODE, 
       DR_INVLINES.DESCRIPTION, 
       DR_INVLINES.QUANTITY, 
       DR_INVLINES.UNITPRICE, 
       DR_INVLINES.LINETOTAL_TAX, 
       DR_INVLINES.LINETOTAL, 
       DR_INVLINES.LINETOTAL_INCTAX
FROM DR_INVLINES DR_INVLINES

Example of the code in the linked child DR_INVLINES dataview (PARAMETERIZED SQL).

SELECT DR_INVLINES.SEQNO SEQNO, 
       DR_INVLINES.HDR_SEQNO HDR_SEQNO, 
       DR_INVLINES.STOCKCODE STOCKCODE, 
       DR_INVLINES.DESCRIPTION DESCRIPTION, 
       DR_INVLINES.QUANTITY QUANTITY, 
       DR_INVLINES.UNITPRICE UNITPRICE, 
       DR_INVLINES.LINETOTAL_TAX LINETOTAL_TAX, 
       DR_INVLINES.LINETOTAL LINETOTAL, 
       DR_INVLINES.LINETOTAL_INCTAX LINETOTAL_INCTAX
FROM DR_INVLINES DR_INVLINES
WHERE ( DR_INVLINES.HDR_SEQNO = :SEQNO )

Example of the code in the linked child DR_INVLINES dataview (MAGIC SQL).

SELECT DR_TRANS.SEQNO SEQNO_2, DR_INVLINES.SEQNO, 
       DR_INVLINES.HDR_SEQNO, 
       DR_INVLINES.STOCKCODE, 
       DR_INVLINES.DESCRIPTION, 
       DR_INVLINES.QUANTITY, 
       DR_INVLINES.UNITPRICE, 
       DR_INVLINES.LINETOTAL_TAX, 
       DR_INVLINES.LINETOTAL, 
       DR_INVLINES.LINETOTAL_INCTAX
FROM DR_TRANS DR_TRANS
      INNER JOIN DR_ACCS DR_ACCS ON 
     (DR_ACCS.ACCNO = DR_TRANS.ACCNO)
      INNER JOIN DR_INVLINES DR_INVLINES ON 
     (DR_INVLINES.HDR_SEQNO = DR_TRANS.SEQNO)
WHERE ( DR_TRANS.TRANSTYPE = 1 )
       AND ( DR_TRANS.ACCNO <> 0 )
       AND ( DR_TRANS.X_FLOID = -1 )
ORDER BY DR_INVLINES.HDR_SEQNO

Row Limits In Magic SQL

Another feature that you can use in conjunction with Magic SQL, is Row Limit. Entering a value into this field at table level, limits the search to find and return the number of rows that are specified in this field. The Row Limit is to only be applied on the parent table.

For instance, entering a Row Limit of 1 will cause Statelake to find all of the records that met the specified criteria in the parent table as per the SQL script, and after performing any requested sort, will then return the first record only. Where there is no particular sort specified, the results will be random and only the first row in this random order will be returned.

However, by entering a Row Limit of 10 on the parent table, Statelake would find all of the records that met the specified criteria as per the SQL script, and after performing any requested sort, would then return the first 10 records only. Where there is no particular sort specified, the results will be random and only the first 10 rows in this random order will be returned.

Regardless of the Row Limit set on the parent, the child will return all records that met the specified criteria,