Arrays and Load-lookup_SQR

Arrays in SQR:

An Array, like a database record, is organized as rows and columns. But unlike a database record, Arrays exists only in memory and NOT in the disk. One of the advantages that Arrays provide is that, once the data is populated into the array, it can be presented in multiple ways without further trips to the database there by making processing faster.

Creating Arrays in SQR
Arrays must explicitly be declared before they can be used. This is how we create an array in SQR

Create-Array name=contracts size=50
field=contract_num:char
field=contract_val:number

The above code creates an array in SQR named contracts which has two fields which are contract_num and contract_val. Once the array is created, all numeric fields are defaulted to zero and everything else is set to NULL. So contract_num will have a value of zero and contract_val will have NULL.

Arrays are created at compile time. It is necessary that you provide the size of the array during this time. If you are not sure of how much data the array would need to hold, you no option but to over allocate the size of the array so that the SQR program doesn’t fail.

Using Arrays in SQR
once we have the array created, we can place values into it and retrieve them for processing. The rows in arrays are numbered starting from zero. While using the array care needs to be taken to ensure that the limit specified while declaring the array are not crossed during operations.

The below section explains the different options available for array operations.

Loading data into Arrays
We can use the simple ‘Let’ statement to load values into an array. The ‘Put’ statement can also be use. Here’s how this can be done.

Let contracts.contract_num(0) = 100000
Put 100000 into contracts(0) contract_num
Put 100001 150000 into contracts(1)

Line one and two does the same task of assigning the value 100000 to the contract_num field on the zeroth row of the array. Line 3 assigns the values 100001 to contact_num and 150000 to contract_val on the first row of the array contracts.

Retrieving data from Arrays
In addition to the let command, SQR uses ‘Get’ command to retrieve values from the array. This is how it can be used.

Let contracts.contract_num(0) = $con_num
Get $con_num from contracts(0) contract_num
Get $con_num #con_val from contracts(1)

The first two lines does the same task of copying the value of contract_num in the zeroth row to the $con_num variable. The third line copies the value of contract_num into $con_num and that of contract_val into con_val.

Arithmetic Operations
the below arithmetic operations can be performed on arrays when all the variables are declared prior of these statements.

Array-Add
Array-Subtract
Array-Multiply
Array-Divide

Best Practices
these are some of the best practices are observed while using arrays in SQR.

1. Use #Define to set the size of the array. This way, understanding the size of the array would be easy and meaningful

#Define MAX_CONTRACTS 100
Create-Array name = contracts size={MAX_CONTRACTS}
field = contract_num:char
field = contract_val:number

2. Reinitializing the array after use. This can be done by using the Clear-Array command
Clear-Array name = contracts

Load-Lookup in SQR

It’s common to join tables within SQRs to retrieve data from normalized tables. As SQL statements consume significant computing resources, such joins may be a hindrance to performance of the SQR. Further, as the number of tables that are used in the join increases, the performance decreases.

This rational makes us look for ways to reduce the number of tables used in the join as a means to tune the SQR. This is when Load-Lookup in SQR comes into picture. Using Load-Lookup is a two step process – here’s how to make use of it in your SQR programs.

Load-Lookup
You start by loading the Load-Lookup. This can either be done within the setup section or within a procedure. While done within the setup section, it is only executed once. When within procedures, the execution happens each time the code is encountered.

The code snippet shows how this is used within the setup section. On execution of the below Load-Lookup, SQR creates an array containing a set of return values against keys.

Begin-Setup
Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
End-Setup

Lookup
Once we have the first step in place, it’s time to utilize the lookup. The below code will essentially look up for a key (PRODUCT_CODE) in the array and return the return value (DESCRIPTION).

Begin-Select
ORDER_NUM (+1,1)
PRODUCT_CODE
Lookup Product_Names &PRODUCT_CODE $DESC
print $DESC (,15)
from ORDERLINES
End-Select

Multiple Keys / Return_values
Although Load-Lookup doesn’t support multiple keys or return_values, we can do this by concatenating the values using database specific concatenation operators. So if you are on Oracle DB, this would be how you can do it. The return values can later be separated using the unstring command.

Load-Lookup
Name = Product_Names
Table = PRODUCTS1
Key = 'PRODUCT_CODE||','||KEY2'
Return_value = 'DESCRIPTION||','||COLUMN2'

Using where clause in Load-Lookup
To limit the values that are populated in the Load-Lookup array, we can use a where clause as shown below.

Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
Where = PRODUCT_CODE > 1000

Multiple Reports in SQR

Generating multiple reports in SQR is common these days. Writing SQRs that produce multiple reports have many advantages over the other approach of having multiple SQRs do this job. Here are some of them.

Advantages

1.     Multiple reports in one SQR approach reduce database trips thereby making the reports faster. This is especially true when all the reports are based on the same set of data. However, be cautious not to get totally different SQR reports into one – this can complicate things.

2.     Easily possible to direct multiple reports to multiple printers. Since we are free to have different layouts and printers for different reports, we can easily direct some reports to one printer while some other reports to a different printer.

3.     This results in fewer SQRs which in turn reduces maintenance costs. Well, this one needs no further explanation.

Having seen the advantages, you would be curious to see how we can generate multiple reports in SQRs. This can essentially be achieved in a three step approach.

Declare-Report
When generating multiple reports, SQR mandates us to declare all the reports that we wish to generate. This is done within the Setup section of the SQR. We can use different printers / layouts for different reports. If you are happy with the default printer and layout, just ignore these in your report definition.

In the sample code below, we have declared two reports, both of which use the default layout and printer for simplicity sake.

Begin-Setup

declare-report TEST1
end-declare

declare-report TEST2
end-declare

End-Setup

For-Reports
Standard SQRs that generate just one report would have only one Heading / Footing. However, we need to have a mechanism to print different headings / footings on different reports. This can be achieved using the For-Reports parameter. This is how we use it.

Begin-Heading 1 for-reports=(TEST1)
print ’Test Report One’ (1) center
End-Heading

Begin-Footing 1 for-reports=(TEST1)
page-number (1,1) ’Page ’
last-page () ’ of ’
End-Footing

Begin-Heading 1 for-reports=(TEST2)
print ’Test Report Two’ (1) center
End-Heading

Begin-Footing 1 for-reports=(TEST2)
page-number (1,1) ’Page ’
last-page () ’ of ’
End-Footing

Use-Report
We have reached the final stage – printing the actual report. Before we can print anything, we would need to inform the processor about the report to which we are printing. We use the Use-Report command to set the printing context. This is how we do it.

Begin-Program

use-report TEST1
print 'This text goes into report TEST1' (,1)

use-report TEST2
print 'This text goes into report TEST2' (,1)

End-Program

This method would however be useful only if you know, at the time of writing the SQR, how many reports you would need. If you need to generate multiple reports on the fly, then you would use the New-Report command. We will write about this in a future post.