SQR

SQR SQL Trace

To trace the status of cursors being executed, append the -S flag to your parameter list. You can do this through the command line (if you're brave) or through the process definition in the override options:

sqr-sql-trace-flag.png

While you can normally extract the appropriate SQL from the SQR, there are cases where this proves quite useful. In particular it is really helpful for investigating performance issues with SQRs as it tells you the SQL text, number of compiles, executes and rows related to the cursor.

Unfortunately this doesn't resolve dynamic SQL or tell you the value of bind parameters that were passed to the SQL so it certainly has limitations but as far as I know its the closest you can get to a SQL trace in SQR.

Stringing Text in SQR

You can use the commands string, by, and into in SQR to string together text by a particular character and put it into a file reference variable. Probably the most useful application for this is in creating a delimited file such as a CSV file.

Here's an example of the syntax

string $seq $emplid $lastname $firstname 'Y' by ',' into $testfile

The above code strings together the variables $emplid, $lastname, and $firstname as well as the constant value Y by a comma and stores the result into the $testfile file reference variable.

SQR CreateProcess Failed

When trying to run an SQR on an NT process scheduler, I received the following error:

Error! SQR Failed To Process
***
*** System Error Code 2
****** PS_StartProc: CreateProcess failed:
    cmd = C:\PT8.51\bin\sqr\ORA\binw\sqrw
    cwd = C:\oracle\instantclient11r2

***
*******************  Error ***********************

Somewhat cryptic! What's happened was that the %PS_HOME% directory had been changed, so the path it was using to run sqrw had changed. Going back to the process scheduler configuration and changing the SQRBIN parameter to the correct location, and restarting process scheduler resolved the issue.

SQR Trace

One of the first things you used to learn as a PeopleSoft developer is to add debug flags in an SQR program and then to run the program with debug flags turned on to get additional information in your .log (.out in the process monitor) file.

The general syntax is to append:

-debug[trace flag letters]

Some of the common trace flags include -debugd generally for data and -debugf for flow (where you are in the program). You can determine the flags you want by scanning through the SQR (related SQCs) and picking out the appropriate trace flag letters by looking for #debug[letter] statements in the code.

Or, you can use a catch-all like this (note it also catches just a -debug statement without a letter at the end)

-debugabcdefghij
Note you can't use the entire alphabet at once as the debug flag is limited to a maximum of 10 characters.

You do this in the process definition by appending to the parameter list in the override options like so (but without the whole alphabet!)

catch-all-sqr-trace-debug-flags.png

Page Breaking In SQR

The page breaking functionality in SQR is really one of the most useful features in the language. The power of the page breaking functionality (on-break) is that you can break as required while processing rows being returned in a begin-select ... end-select block.

So this means you can simply order the data selected out of the database and page-break as required rather than having to check for complex page break conditions manually using variables to store the last value(s) processed.

The key to the on-break code is the level parameter which is essentially acts a priority (level 1 is the first priority, then level 2, then level 3 and so on).

For example, here's a snippet of code used to break based on three selected columns:

begin-select
CRSE_ID ()
    on-break level=1
    print=never
CAMPUS ()
    on-break level=2
    print=never
ACAD_GROUP ()
    on-break level=3
    print=never
    before=Print-New-Page()
. . .
end-select

In this example, it will break in order of level priority, that is first on on a change to CRSE_ID, then on a change to CAMPUS and third on a change to ACAD_GROUP.

Note the procedure call to Print-New-Page() is made using the before keyword which means that the break happens before the next row change in ascending order of break levels. That is, the Print-New-Page procedure will happen before the change in data for a new CRSE_ID, CAMPUS and ACAD_GROUP combination.

Dates in SQR

There are two main ways to store and use dates in SQR:

Both types of variables (strings and dates) use the $ dollar sign prefix but dates are declared differently to strings.

So why use a date variable instead of a string? Well the main reason is if you want to perform any date based calculations. If just need to store a date in a variable then using a string is fine. However if you wanted to perform a calculation like adding 7 days to a date then a date variable is the way to go. Date variables are also required when using the delivered SQR date functions.

Date variables are declared in the setup section like this:

begin-setup
    declare-variable
        date $dtVariable
    end-declare
end-setup
NOTE: date variables are initialized to null when they are declared.

Now that you have a date variable, there a number of delivered date functions you can use in SQR including:

Example using datediff()

Return the number of days between the column variable, &EFFDT and the variable $BaseDate:

let #nbrOfDays = datediff(&EFFDT, $BaseDate, 'DAYS')

Example using dateadd()

Add four weeks to the $dtFinish variable:

let $dtFinish = dateadd($dtStart, 'WEEK', 4)

The options available for the period include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR.

Output to the Report Repository

If you are developing a new SQR that creates files that need to be posted from the process scheduler to the report repository, (report manager), here are the steps for doing this. Note this uses delivered SQCs.

The delivered getlogou.sqc can be used to post files to the report repository. There are two functions in this SQC:

Both functions require a global variable, #prcs_process_instance to be declared somewhere in your SQR. This is normally taken care of if you call stdapi.sqc.

The orignal output destination is what has been entered into the process definition for the output location. This is normally %%Log/Output Directory%%. The process output directory is the expanded application server path to the log output directory.

The log output directory contains folders that are posted to the report repository. The naming convention for these folders is:

ProcessType_ProcessName_ProcessInstance

For example, CCLTRGEN is an SQR process. If the process instance is 1234567 then folder for this process in the log output directory would be:

SQR_CCLTRGEN_1234567

This folder typically contains log files (.log, .out), any of the common files output by the process (e.g. .pdf) and any files specifically created and placed in this directory.

This is a typical example of how you would use the get-logoutputdir-value procedure to determine the output location for a CSV file generated by your SQR:

do Get-logoutputdir-value ! getlogou.sqc
if (rtrim($prcsoutputdir,' ') <> '')
    move $prcsoutputdir to $CSVPath
end-if

You can check the process output location using the following SQL if you know the process instance:

select PRCSOUTPUTDIR
from PSPRCSPARMS 
where PRCSINSTANCE = '123456789'

Error Code 2904

(SQR 2904) File number already opened.

This indicates that the file number you used for reference (e.g. 1) is already in use.

Two common reasons are:

The easiest solution is to increment your file reference number and see if it fixes it. So go from 1 to 2. Make sure you update your write statements too!

If you are still having issues, then it might be that your code to open the file is being called multiple times, you may need to move where it is.

Error Code 3719

(SQR 3719) Columns names and expressions must be unique or be given unique pseudonyms (&name)

This is a common error for people new to SQR. It happens when you use SQR statements in a begin-select ... end-select block and do not indent them. If any SQR statement is flush left within these blocks (e.g. a let or move statement) then it will cause the error.

Note that the line shown in the error log file is actually the line directly after the error and not the line causing the error which can be rather confusing.

This error can also happen if you use the same table alias more than once. For instance, say you used the alias A for both PSOPRDEFN A and PSROLEUSER A. The error will appear telling you that the alias A must be unique. Simply use different aliases for each table, e.g. PSOPRDEFN A and PSROLEUSER B.

Error Code 4713

(SQR 4713) Cannot open the #INCLUDE file: 'setenv.sqc'
(2): No such file or directory

SQR for PeopleSoft: Program Aborting.

This generally occurs when you are trying to run SQRs manually (outside of the PeopleSoft process scheduler) and you haven't supplied the correct report arguments. Here's an example of the arguments you would use to run an SQR (using sqrw.exe). Note the trailing slash on the input directory is very important. Substitute PS_HOME with the appropriate path.

Error Code 5400

(SQR 5400) Second page write attempted while writing current page.
Check BEFORE-PAGE, AFTER-PAGE procedures.

This error can happen if you exceed the number of lines reserved in your begin-heading and begin-footingstatements. For example, if you reserve 3 line with begin-footing 3 then try to print on the 4th line, the error will occur. Try increasing the reserved lines for your header/footer, or reducing the number of lines being printed.

Error Code 5528

(SQR 5528) ORACLE OCIStmtExecute error 933 in cursor 11: ORA-00933: SQL command 
not properly ended

I caused this error by placing a single commit; statement within the same begin-sql and end-sql block used by a delete statement. The solution was to move the commit; to its own begin-sql ... end-sql block like this:

#ifdef ORACLE
begin-sql
    commit;
end-sql
#endif

There are actually a number of reasons why this error may occur and they are generally due to Oracle not being able to interpret the SQL passed by SQR. The error tells you which cursor is in error. If you can't find what part of your code is causing the issue, start with an SQR SQL trace then use the output file to find the cursor number that is causing you grief.