This article goes through some of the error codes I have encountered while developing SQRs and how to fix them. There are a lot of resources on the Internet (mainly in forums) regarding SQR error codes. Unfortunately, it can be quite hard to find the correct answer (if there is one posted at all) for them.
SQR 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 procedure you use to do the open is being called more than once
- There is a call to some other code (perhaps in an SQC?) that also opens a file with the same file reference.
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.
SQR 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.
SQR 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.
- Report Name: PS_HOME\sqr\setspace.sqr
- Username : sysadm
- Password : sysadm
- Database : PTDMO
- Report Arguments: -ZIFPS_HOME\sqr\pssqr.ini -iPS_HOME\sqr\ -oPS_HOME\log\setspace.log -keep
SQR 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-footing statements. 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.
SQR 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 … 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.