Get Latest Updates directly delivered in your Email

PeopleSoft SQR Frequently Asked Question

1
Publish On In PeopleSoft SQR FAQ

1. What are the sections and paragraphs available?
There are five sections and three paragraphs available in SQR programming. The sections include
a. Begin-setup End-setup.
b. Begin-program End-program.
c. Begin-procedure End-procedure.
d. Begin-heading End-heading.
e. Begin-footing End-footing
And the paragraphs include
Begin-select paragraph.
Begin-SQL paragraph.
Begin-document paragraph

    2. Is it possible to increase the array size once defined?
No, it is not possible to increase the Array size once defined

    3. What is Load-lookup? Give the Syntax for Load-Lookup?
The LOAD-LOOKUP command defines an array containing a set of keys and values and loads it into memory. With LOAD-LOOKUP, you can reduce the number of tables that are joined in one SELECT. Use this command in conjunction with one or more LOOKUP commands.
Syntax: begin-setup
load-lookup
name=prods
table=products
key=product_code
return_value=description
end-setup

     4. Can you Call a SQR from another SQR? How?
We can call one SQR program from another SQR program using Call.callsystem. But this is possible only in UNIX Operating system.

     5. Briefly explain Most commonly used SQC’s in SQR programs?
: Some of the most commonly used SQC’s are
1.STDAPI.sqc : This Is Process Scheduler Interface which is used to initiate and terminate some field values such as Process_instance & Run_cntl_id
2.SETENV.sqc : This is used to set the Default Environment based on the Database such Printer Type, Paper Size,Date formats Etc….
3.NUMBER.sqc : This file contains generalized routines to format numbers.
4.DATETIME.sqc : This file contains generalized routines to format dates and times.
6.RESET.sqc : This is an important Footing SQc.

     6. What are the commands used in filehandling of sqr?
Using File Handling we can Import. Steps are
1. Opening a File Using ‘OPEN FOR-READING’ command.
1. Reading Data From File Using ‘READ’ command.
Writing Data into Table Using ‘BEGIN-SQL’ paragraph & ‘INSERT’ command

      7. What are On-break parameters?
ON-BREAK causes the specified action to perform in a tabular report when the value of a field changes. The default action prints the field only when its value changes (PRINT=CHANGE).Mainly used for Redundancy of Printing on a page.

     8. How can you Debug SQR programs?
We can Debug SQR using Debugging Commands such as
2. #IF DEBUGxxx
#END-IF
2. DISPLAY and
3. SHOW

     9. What is the difference between sqr and sqc?
SQR SQC
1.This is nothing but the Actual 1.This is like a function in SQR.
Source program.
2.This consists of Program Section. 2.This does not consists of program Section.
3.This can be Compiled & Executed. 3.This cannot be Compiled and Executed.
4.We cannot call one SQR from 4.We can call one SQC from another SQC or another SQR
or SQC. SQR program.

    10. What are different types of variables in sqr?
Types of variables in SQR are
1. Column Variables.
2. List Variables.
3. System Variables.
4. Document Variables.
5. Substitution Variables.
User Defined Variables

    11. Is there any read-only variable in sqr?
Column Variables & System Variables are Read-Only Variables.

    12. How to pass Inputs for sqr program while running from windows?
Using ASK & INPUT Commands.

    13. How to pass Inputs to the SQR while running through the Process Scheduler?
Using Procedures & SQC’s to access data on Run Control Page (Run Control Fields).

     14. What is the purpose of Stdapi.sqc?This is a Process Scheduler Interface sqc which is used to initiate and terminate some field
values such as Process_instance & Run_cntl_id

    15. What is the SQT file? What are the advantages of SQT files?
SQT’ file is nothing but resultant file after the compilation (i.e.) Runtime File. By using this SQT file for Execution we can increase the Performance of the program by reduciong the Compilation Time.

     16. What is the SQC used to read data from translate table?
‘READXLAT.sqc’ is the SQC used to read data from Translate Table.

     17. Which section is mandatory in an SQR program?
‘BEGIN-PROGRAM’ Section is Mandatory for an SQR program.

     18. How will you display an image in SQR?
Using ‘DECLARE-IMAGE , END-DECLARE’ command.

     19. What is use of SETENV.SQC?
This is used to set the Default Environment based on the Database such Printer Type, Paper Size, Date  formats Etc….

     20. How will you perform ERROR handling in SQR?
We can do Error handling in SQR using some command line flags such as –O, -L,-ZMF,-XMB and using Error handling commands such as
ON-ERROR = Skip/Warn/Stop (for Compile time errors)
ON-ERROR = Procedure Name (for execution stage errors).

     21. How many ways of performance tuning are there in SQR?
 1. Using LOADF-LOOKUP & LOOKUP.
2 .Using ARRAYS.
3. Using MULTIPE REPORTS.
4. Using SQT Files.
5. Using –Bnn Command line Flag.
6. Running on BATCH SERVER.
7. Using Proper Programming Logic in SQR such as Using BREAK statement in EVALUATE Command.
8. Proper SQL tuning such as using Sub queries instead of Joins.

     22. Can you call procedure in oracle from Sqr? How?
To call a database procedure using Begin-select paragraph, the syntax is as follows:
Begin-select
[(report arguments)] from Dual
End-select
To call from Begin-sql, the syntax will be
Begin-SQL
;;
End-SQL

     23. Can you connect more than one database using one Sqr? How?
Yes.

     24. What are the differences between Load lookup and Array?ARRAYS LOAD-LOOKUP
1. Arrays can be declared in any section. 1. Load-Lookup is declared in only in
SETUP section only.
2. Number of rows are not automatically 2. Numbers of rows are automatically added. gives a error if the number of added.
Exceeds the specified number.
3. We should insert data in to Array by 3. Rows are automatically inserted in to
programming. Load-Lookup.
4.We can retrive any number of fields. 4.We can retrive only Two fields from
from a table. a table.
5.We have Length & Data type. 5.We don’t have Length & Data type.
6.We cannot directly print from Array. 6.We can directly print from
Load-Lookup.

     25. What are the steps required to run the SQR from peoplesoft environment?
Three steps we have to fallow to run the SQR program from the peoplesoft environment (Process Scheduler). These steps include
a. Making the SQR program API aware by calling two procedures (stdapi-init, stdapi-term) from the Begin-program section and including the Stdapi.sqc in the bottom of the program.
b. Create new run control table or using existing run control table and creating run control page, component and register the component. Creating new run control SQC if we create a new run control table.
c. Giving the permission to the user by giving process groups.

     26. How to pass command line flags to a sqr report running through process scheduler?
Using override options in the process definition in the Process Scheduler in the People Tools.

     27. What are On-break parameters?
ON-BREAK causes the specified action to perform in a tabular report when the value of a field changes . The default action prints the field only when its value changes (PRINT=CHANGE).Mainly used for Redundancy of Printing on a page.

     28 .How does peoplesoft Process monitor knows that the process (Say sqr report) is at what stage such as initiated, in process, completed etc
From the Field Value of ‘RUNSTATUSDESCR’field from ‘PS_PMN_PRCSLIST’ table.

    29. How can u Debugging SQR programs?
We can Debug SQR using Debugging Commands such as
3. #IF DEBUGxxx
#END-IF
2. DISPLAY and
3. SHOW

     30. How to refer a global variable in local procedure?
After special character (ex.#,&) we use underscore(_) after that variable name.

     31. What is -Bnn flag?
This is used to specify the number of rows that are to be taken from Dbase to the Buffer because default the system takes 10 rows at a time.

     32. What is -Tnn flag?
This is mainly used for testing purpose. We can restrict the system to generate only a specific number of pages instead of generating all pages for testing. This is used in Windows Environment.

     33. What is -RS & -RT Flags?
RS is used to generate SPF file ie; stopping the program after the comilation. _RT is used to run the SPF file .This is also one kind of Performance increasing technique by saving the compilation time.

     34. What are the sections in Sqr? Which one is necessary?
Setup, Heading, Footing, Program & Procedure Sections. Program section is the Mandatory section.

    35. What is -debug command line flag?
This is used for Debugging in SQR.
36. How to place an corporate logo in sqr?
Using “Declare Image” command
37. what are the debugging commands in sqr?
Show & Display are debugging commands
38. what is the difference between them?
Show is used to display more than one variable at a time.
Display is used to display only one variable at a time.

1) What is the difference between:
Print $some_var (1,1,0) and
Print $some_var (+1,1,0)
2) What are some possible reasons for seeing an error like this:
(SQR 5528) DB2 SQL PREPARE/DECLARE error -204 in cursor 1:
SQL0204N “DRO.DEALERSHIP” is an undefined name. SQLSTATE=42704
SQL: SELECT orgHier.GEOG_NM,
dlrshp.DLR_CD,
dlrshp.dlrshp_nm,
agmt.APPNTD_DT,
agmt.CNSCTV_TERM_NO
FROM
DRO.DEALERSHIP dlrshp,
…..
….
Error on line 227:
(SQR 3716) Error in SQL statement.
Errors were found in the program file.
SQR: Program Aborting.
3) What is the Load-Lookup structure in SQR useful for and when might you use it.
4) How is a Load-lookup table different from an Array
5) What is the difference between an ASK and an INPUT parameter.
6)
let $var = “SQR RULES”
print $var (1,1,5)
print $var (+1,5,0)
What will the output of these print statements be? (Type in the answer below)
7) For this questions assume you are given the following report to build. The requirements are that after each break in diversity type the total net profit and total number of dealers in the diversity type should be printed (the bolded values inside the squares). And at the end of the report a grand total should be printed. The diversity type should only be printed for the first dealer of that diversity type. Assume you are connecting to the following table:
Diversity_Dealers
dd_id INTEGER
diversity_type VARCHAR
region_cd VARCHAR
start_dt DATE
dealer_cd VARCHAR
dealer_nm VARCHAR
address VARCHAR
city VARCHAR
state_abr VARCHAR(2)
zip_code VARCHAR(5)
area_code VARCHAR(3)
phone_nbr VARCHAR
first_nm VARCHAR
middle_ini VARCHAR
last_name VARCHAR
net_profit INTEGER
Refer the mock up of the report below and write an SQR program that will use the above table to generate this report. (Note: don’t worry about minor errors in syntax or getting the print placement parameters exactly correct. The idea here is to test your ability to use SQR to generate the report and meet the requirements.)
7) True or False, the SQR syntax does allow for a GOTO control structure.
8) What do the RTRIM and RPAD functions do?
9) Write an SQR program that offers a local procedure that allows a user to form a single string that will contain the value of a person’s full name, “Darrin Alan Miller” for example. The SQR program should:
  1. Prompt the user for a person’s first name, middle name, and last name
  2. Allow the user to enter the aforementioned values in upper, lower, or mixed case
  3. Prompt the user for the “style” of name they want to see (“LFM”, “FML”, “LF”, “FL”)
  4. Prompt the user with a choice whether or not they want the “middle name” portion of the resulting name to be abbreviated or not. If the user wants the “abbreviated” version of the middle name, then that name must be followed with a period.
  5. Return the person’s full name with each word having its first character capitalized.
  6. If a person’s last name is the first portion of the full name string created, then that last name should be followed by a comma and appropriately spaced
  7. Although the user may be requesting to see the person’s middle name as part of the full name variable that is created, if the middle name is null (and it can be), then the logic for making this name part of the full name variable should be omitted.
First Name
Middle Name
Last Name
Style
Abbr
MN
Result
DARRIN
Alan
Miller
LFM
Y
Miller, Darrin A.
Darrin
Miller
FML
Y
Darrin Miller
Darrin
AlAN
miller
FML
Y
Darrin A. Miller
10) True or False, the attributes associated with a chart as declared within the DECLARE-CHART definition of an SQR program may be overridden by corresponding attributes within the PRINT-CHART command.
11). The creation of charts within an SQR program requires the use of what type of in memory data type:
A. An array
B. A load-lookup
C. A file
D. A cursor
E. A select statement
12) True or False, the use of arrays within an SQR program should be avoided because they can not be dynamically sized and therefore may obtain more OS memory than needed.
13) What are the methodologies available to pass user parameters into an SQR program?
  1. INPUT
  2. PROMPT
  3. ASK
  4. Choices A and B above
  5. Choices B and C above
  6. Choices A and C above
  7. All of the above
14) True or False, user parameters passed to an SQR program via the INPUT command retrieve a compile-time substitution variable.
15) Within a BEGIN-SQL paragraph various DDL and DML commands such as “creates”, “drops”, “updates”, etc. can be issued against the RDBMS. The ability to issue these types of commands is determined and constrained by the…
  1. RDBMS privileges assigned to the database administrator
  2. RDBMS privileges assigned to Dan Thornhill
  3. RDBMS privileges assigned to the user executing the SQR program
  4. roles, grants, and synonyms assigned to the user executing the SQR program
  5. SQR program designer
16) Which SQR section is responsible for defining and controlling information that is to be printed at the top of each output page?
17) An “&” variable within SQR…
A. May represent and store a numeric value selected from the RDBMS
B. May represent and store a string value selected from the RDBMS
C. Is a read only variable
D. A, B, and C above
E. None of the above
18) Referring to the excerpt of SQR code that follows and assuming that the procedure queried 10 rows of data and that each row possessed a value of 10.00 for the PROD_PRICE column, what will be the value of the #product_total variable that is displayed?
BEGIN-PROCEDURE CALC_FIGURES
LET #PRODUCT_TOTAL = 100
BEGIN-SELECT
PROD_CODE &PROD_CODE
PROD_PRICE &PROD_PRICE
LET #PRODUCT_TOTAL = (#PRODUCT_TOTAL + &PROD_PRICE)
FROM PRODUCTS
WHERE PROD_CODE = 1709
END-SELECT
DISPLAY #product_total
END-PROCEDURE
19) Referring to the excerpts of SQR code that follow, will the contents of #PROD_TOTAL in both excerpts be the same after program execution has completed?
BEGIN-PROCEDURE CALC_FIGURES
BEGIN-SELECT
PROD_CODE &PROD_CODE
PROD_PRICE &PROD_PRICE
LET #PROD_TOTAL = (#PROD_TOTAL + &PROD_PRICE)
FROM PRODUCTS
WHERE PROD_CODE = 1709
END-SELECT
END-PROCEDURE
BEGIN-PROCEDURE CALC_FIGURES
BEGIN-SELECT
PROD_CODE &PROD_CODE
SUM(PROD_PRICE) &PROD_TOTAL
MOVE &PROD_TOTAL TO #PROD_TOTAL
FROM PRODUCTS
WHERE PROD_CODE = 1709
END-SELECT
END-PROCEDURE
20) “&” variables within SQR are assigned during the processing of what type of SQR paragraph?
A. BEGIN-SQL
B. BEGIN-PROCEDURE
C. BEGIN-ERROR
D. BEGIN-SELECT
E. BEGIN-PROGRAM
Use the following excerpts of SQR code to answer following questions.
Program Excerpt A:
BEGIN-PROCEDURE GET_CUSTOMERS
BEGIN-SELECT
C.NAME (1,1)
O.ORDER_DATE (1,35)
O.ORDER_NUM (1,50)
NEXT-LISTING
FROM CUSTOMERS C,
ORDERS O
WHERE C.CUST_NUM = O.CUST_NUM
ORDER BY C.NAME
END-SELECT
END-PROCEDURE
Program Excerpt B:
BEGIN-PROCEDURE GET_CUSTOMERS
BEGIN-SELECT
C.NAME (+1,1)
C.CUST_NUM &LE_NUMBER
DO GET_ORDERS
FROM CUSTOMERS C
ORDER BY C.NAME
END-SELECT
END-PROCEDURE
BEGIN-PROCEDURE GET_ORDERS
BEGIN-SELECT
O.ORDER_DATE (0,35)
O.ORDER_NUM (0,50)
FROM ORDERS
WHERE CUST_NUM = &LE_NUMBER
END-SELECT
END-PROCEDURE
21) The “&LE_NUMBER” portion of code is known as a:
  1. blind variable
  2. bound variable
  3. flexible variable
  4. bind variable
  5. substitution variable
22) If the CUSTOMERS table contains 20 rows, and the ORDERS table contains 20 rows, and these tables have 10 rows with matching CUST_NUM values, will both programs achieve the same results?
23) The code within “Program Excerpt B” can be described as:
  1. a correlated sub-query
  2. a cascading query
  3. a join query
  4. a cluster query
  5. a goto query
24) How many distinct queries are being issued to the RDBMS within “Program Excerpt B”
25) Modify the excerpts of SQR code above related to the previous questions so that possible errors encountered from the RDBMS will be handled.
sqr
1. Explain what is on-break in SQR with options and their use?
Ans: On-break is a very powerful SQR feature that will allow you to control what happens when values in a column break, or change. Specifically, you will use on-break to:
• Suppress duplicate values
• Produce subtotals
• Produce subheadings
• Control the flow of your SQR
Syntax: print {field} (a, b, c) on-break {print = change |
print = change/top-page |
print = always |
print = never}
before = procedure_name
after = procedure_name
save = $text_var
skiplines = nn
level = nn
2. What is the load look up concept in SQR?
Ans: A common function within an SQR program is to join tables in order to get the long description of some data code.
For instance, in a given table, you may have a COURSE or DEPT_ID but the corresponding description is in another table.
This situation requires the SQR program to retrieve subordinate data and can be done via a join, or by performing a procedure that performs a
separate SELECT from within the main SELECT statement. However, for large tables with large amounts of data, a table join can be a slow process.
There is a more efficient way of retrieving this data in SQR. You can use lookup tables to save static data in a dynamic structure providing you more
efficient data retrieval during the execution of an SQR program. Lookup tables are implemented in SQR programs using the load-lookup and lookup commands.
load-lookup
Example: load-lookup
name=EmployeeDetails
rows=12
table=PSJOB
key=EMPLID
return_value=NAME
where=DEPT=’’Company’’’
and (effective dating logic) … (this is the syntax we gve in set-up section)
lookup EmployeeDetails &EMPLID $NAME(this is how we call from a procedure)
3. How do you pass parameters among procedures, what is the alternative to do that?
Ans: We usually pass parameters from one procedure to another using variables prefixed by $ or # depending on the data type.
<******************************* I doubt even arrays ****************************************>
4. How do you control page breaks?
Ans: You can prevent page breaks within a record by following four simple rules:
1. Place ON-BREAK columns ahead of other columns in the select paragraph.
2. Place the lower-level ON-BREAK columns ahead of the higher-level ON-BREAK columns in the select paragraph.
3. Use the same line positions for all ON-BREAK columns.
4. Avoid using WRAP and ON-BREAK together on one column.
5. How do you influence report output to landscape or portrait
Ans: PeopleSoft has standardized the Setup section using SQC files references by the #include command. Examples
setup31.sqc Portrait mode
setup32.sqc Landscape mode
6. How you debug SQRs
Ans. 1. By displaying data when the query runs using show/display so that isolate problems areas by temporarily skipping the program that is not working.
Show is used to show any number of variables. Display is also speified for any variable, column or literal but it displays only one value.
2. SQR provides #debug command to make temporary changes to the code.
7. Explain Complier directives in SQR
Ans. Compiler directives are #IF, #ELSE, #END-IF, #IFDEF, #IFNDEF used before the substitution variables declared by #DEFINE.
1. #if: Compiles the commands followed by #if if the condition satisfies.
2. #else: compiles the commands followed by else if the #if condition doesn’t satisfy.
3. #end-if : Ends the #if.
4. #ifdef : Compliles the commands under #ifdef if the substitution variable is defined.
5. #ifndef : Compliles the commands under #ifdef if the substitution variable is not defined.
8. How many flat files can be opened through SQRs and what is the limitations in reading or write those files?
Ans. As many but the maximum number of files is 99.
9. What is a difference between Ask & Input command?
Ans. 1. Ask : Retrives values from compile time variables, and can be written in begin-setup section only. The retrieval can be either from userinput, command line, or from a file.
2. Input : Accept dat entered by the user into the variable specified.
10. How do we work on Effective dated rows on SQR?
Ans. We add the criteria in the sql command the effective date must be equal to current date.
EFFDT = (SELECT MAX(E.EFFDT) FROM PS_JOB A
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL-RCD = A.EMPL-RCD
AND A1.EFFDT <= DATENOW()
11. How to Refer Global variable in a local procedure through SQR?
Ans: <**********************not sure of ans **************************************>
12. How do you declare a local procedure and global procedures in SQR?
Ans: SQR procedures that contain variables that are visible throughout the program are called global procedures. These procedures can also directly reference any program variable.
In contrast, procedures that take arguments, such as the spell number procedure in this section’s check-printing sample program, are local procedures. In SQR for PeopleSoft, any procedure that takes arguments is automatically considered local.
13. What is the difference between Begin-Select & Begin-SQL?
Ans: 1. The Begin-SQL paragraph allows you to execute any non-select SQL statements (Insert, Update, Delete) DML
2. The Begin-Select paragraph is the heart of the SQR program.
example: begin-SELECT [DISTINCT]
COLUMN_NAME
[SQR commands]
FROM TABLE/VIEW
[WHERE…]
[Order by …]
end-SELECT
14. Can multiple columns be retrieved in Load-Lookup command in SQR?
Ans: <**************************** I wud like to know this ans**************************************>
15. How do you control the number of iterations or fetch records firing SQL statement in SQR?
Ans: By looping
16. What is a difference between EDIT & Format in SQR?
Ans: Edit is used to edit any data type and format is used to edit only date fields in input command.
17. How do you Execute SQR using Process Scheduler?
Ans: BEGIN-SELECT
FROM PS_
WHERE OPRID = $PRCS_OPRID
AND RUN_CNTL_ID = $PRCS_RUN_CNTL_ID
END-SELECT
18. Are SQR commands and variables case sensitive?
Ans: SQR commands and variables are not case sensitive.
19. Do SQR programs require a dot(.) sqr extension? Do SQR runtime files require a dot (.) sqt extension?
Ans: Yes a SQR needs to be saved as (.sqr) extension and runtime file need (.sqt).
20. How can I read invariable length delimited fields from a sequential file? An example format would look like this: field1|field2|field3|…
Ans: With the field separator as in above example “|” is the delimiter.
21. How can I insert a null value into a numeric field in the database?
Ans: By inserting zero (‘0’) into the field we can give null values to numeric field.
22. What does the SQR variable “#sql-count” holds?
Ans: #sql-count holds the count of rows that are affected by a begin-select paragraph(INSERT, UPDATE, or DELETE).
23. How do you reset the page number printed on a PAGE?
Ans:
24. For multiple reports is it compulsory to use command “declare layout”? Or can we create multiple reports without “declare layout”?
Related Article you might like:
Previous:
PeopleSoft Business intelligence ( XML ) Publisher Frequently Asked Question
Next:
PeopleSoft Component Interface Frequently Asked Question

One comment on “PeopleSoft SQR Frequently Asked Question”

Leave a Reply

Your email address will not be published. Required fields are marked *

Show Comment Guide
Write your code inside of code or pre tag

<code> your code resides here </code>