Explore topic-wise InterviewSolutions in .

• SQR (Structured Query Reports) is a programming LANGUAGE used in DATA processing and data reporting. 
• SQR program can EXECUTE MULTIPLE queries.

Features:

• More flexible
• Scalability for Client and Server architecture.
• Multiple platform portability
• Multiple RDBMS portability

Features:

It is an ATTRIBUTE GIVEN to programming for processing and generating OUTPUT DESTINATION FILE.

It is an attribute given to programming for processing and generating output destination file.

• . LIS (list file) is used for line printer execution. Normally it is used for PRINTING cheques.
• .SPF file is portable format, this is used for all window-based APPLICATIONS with any printer. Normally it is used for MSPAINT, IMAGE, and Graphics.

There are 5 types of SQR files:

• SQC: SQC program files contain commonly included procedures that are called by other SQR programs.
• LIS when an SQR reports generate output it is sent to a file with an. LIS extension.
• LOG: SQR.LOG file is the output file used by People soft. This file contains all INFORMATION such as errors and displays statements. That is output to the terminal during the execution of SQR.
• MAX: SQR does not DYNAMICALLY allocate memory. The default program allocations (which were coded for LOWER DOS memory) usually have to be increased for complex SQR programs.

Note: People soft uses a .MAX file to increase any SQR parameter where default is not adequate for the program. There is one main .MAX file for processing under SQR W (windows) called ALLMAXES.MAX

• SQT: These program files are precompiled SQR programs. As a People soft 5+ there are no delivered precompiled SQR programs.

There are 5 types of SQR files:

Note: People soft uses a .MAX file to increase any SQR parameter where default is not adequate for the program. There is one main .MAX file for processing under SQR W (windows) called ALLMAXES.MAX

The SQR command supports several flags. Each flag begins with a dash (-). Flags with arguments must be followed directly by the argument with no intervening space.

SQR Command-Line Flags :Flag Description :

• A Causes the report output to be appended to an existing file of the user name
• Bnn (Oracle,SYBASE CT-Lib) indicates how many rows to buffer each time data is retrieved from the database. The DEFAULT is 10 rows.
• C Specifies that the cancel dialog box appear while the program.
• CB (Callable SQR) forces the communication box to be used.
• DEBUG (xxx) (Sybase) Causes lines preceded by #DEBUG to be compared. Without this flag, these lines are ignored.
• E(file) Causes error messages to be directed to the named file, or to the default file report errs. If no errors OCCUR, no file is created.
• F (file) (Directory) overrides the default output file name program .lis
• KEEP In addition to .LIS files, creates an .SPF file for each report that your program generates.
• LOCK(RR]CS]RO]RL]XX] (SQL Base) Defines the types of locking (isolation level for the session.
• NR (SQL Base) specifies that the No Recovery mode is used when connecting to the database.
• XCB Do not USE the communication box.
• XL Prevents SQR from logging on to the database. Programs run in this mode cannot contain any SQL statements.
• XMB Disables the error message display so that a program can be run without interruption by error message boxes.
• XP (SYBASE DB-Lib) Prevents SQR from creating temporary stored pro-cedures.
• XTB Preserves the trailing blanks in an LIS file.
• ZIV Invokes the SPF viewer after generating program .spf file. Using this flag implicitly invokes the –KEEP flag to create program .spf file. 
• ZMF(File) specifies the full path and name of the SQR Error Message file. The Sqr err.dat located in the directory indicated by the SQR DIR environment Variable is the default.

The SQR command supports several flags. Each flag begins with a dash (-). Flags with arguments must be followed directly by the argument with no intervening space.

SQR Command-Line Flags :Flag Description :

COLUMNS, VARIABLES, LITERAL

Columns, variables, Literal

FIRST PASS: Or Compile stage. SQR processes the FOLLOWING components of the program.

1. All # include external source files (SQCs) are inserted into the source program.
2. All # commands are evaluated.
3. The setup section is processes including allocation and population of memory arrays created by the load-look up command.
4. OPTIMIZATION of the SQL data access path is determined.
5. Checks syntax of SQR source program.

SECOND PASS: Actual execution of the program source occurs starting at the begin Report stopping at the end-report.

1. Process data for the body section.
2. Senses page overflow or response to new-page command in code.
3. Process heading section
4. Process FOOTING section
5. Writes entire page buffer to file.

FIRST PASS: Or Compile stage. SQR processes the following components of the program.

SECOND PASS: Actual execution of the program source occurs starting at the begin Report stopping at the end-report.

• Report or Program – Determines where SQR will begin and end execution of your report. People soft uses this section to control the flow of processing by calling procedures using the do command, thereby utilizing modular programming.
• Setup Section – Contains commands, which describe the general report characteristics (Note: the setup section is preprocessed and is automatically run before the program begins execution. This section can only include SQR commands.
• Programming section –
• Begin – SQL: The SQL paragraph allows you to execute any non-select SQL Statement.
• Begin-Document-: Allows you to mix background text. With the data retrieved from the database.
•  Begin-Select: Select paragraph is the heart of the SQR program. For each row of data retrieved from the query, acting as an implicit while loop may also process a SET of commands there. Here you specify what columns of data to retrieve from the database. Other procedures may be referenced within a select paragraph, which in turn may contain other select paragraphs.

Note: In begin select SQR REQUIRES that columns names be flesh to the left margin and SQR commands be indented.

• Heading Section
• Footing Section
• PROCEDURE Section : A procedure is a LIST of commands or paragraphs, which are processed when referenced by a corresponding, do command. Procedure names must be unique. Procedures can contain regular SQR commands as well as three special kinds of paragraphs: A. SQL B. Document C. Select

Note: In begin select SQR requires that columns names be flesh to the left margin and SQR commands be indented.

• The print command, is both explicit and implicit, is the FOCAL point for formatting and PROCESSING.
• Explicit print is used to print database column referenced by its PSEUDONYM (&column name) or a user defined variable.
• The Implicit print is only valid for database COLUMNS as they are retrieved in the select para.

Printing in SQR is placing output on the page grid. POSITION syntax is USED to define where to place DATA on the page WITHIN a given section.

Printing in SQR is placing output on the page grid. Position syntax is used to define where to place data on the page within a given section.

On-break option on COLUMNS will cause each column to break independently from each other. When a break OCCURS the following happens, After PROCEDURE are processed in DESCENDING sequence from the highest level to the level of the current break filed.

On-break option on columns will cause each column to break independently from each other. When a break occurs the following happens, After procedure are processed in descending sequence from the highest level to the level of the current break filed.

• ASK : It is requests the USER for the value and SUBSTITUTES the value during compilation. It is used only in the SETUP section.
• INPUT : It requests the user for the value at RUNTIME. (i.e., at execution stage). It can be used anywhere in the programming section.

Note: INPUT is mere efficient than ASK.

Note: INPUT is mere efficient than ASK.

An Array can be DEFINED to STORE INTERMEDIATE RESULTS or DATA retrieved from the database.

An Array can be defined to store intermediate results or data retrieved from the database.

It is an CENTRALIZED tool that enables application developers, SYSTEM ADMINISTRATORS and application users to manage People soft batch PROCESS. Process Scheduler enables you to SCHEDULE running process and create job.

It is an centralized tool that enables application developers, system administrators and application users to manage People soft batch process. Process Scheduler enables you to schedule running process and create job.

• PROCESS: A process is a SINGLE TASK, program or routine (such as SQR reports or Batch Cobol) that runs either on client or server.
• JOB: It is one or more process of the same or different TYPE, which can run either in series or Parallel. (A group of process can be combined and run as process job)

There are five SECTIONS and THREE paragraphs AVAILABLE in SQR programming.

The sections INCLUDE:

1. Begin-setup End-setup.
2. Begin-program End-program.
3. Begin-procedure End-procedure.
4. Begin-heading End-heading.
5. Begin-footing End-footing

And the paragraphs include
Begin-select PARAGRAPH.
Begin-SQL paragraph.
Begin-document paragraph

There are five sections and three paragraphs available in SQR programming.

The sections include:

And the paragraphs include
Begin-select paragraph.
Begin-SQL paragraph.
Begin-document paragraph

No, it is not POSSIBLE to INCREASE the ARRAY SIZE once DEFINED

No, it is not possible to increase the Array size once defined

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

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

We can CALL one SQR program from another SQR program using Call.callsystem. But this is POSSIBLE only in UNIX OPERATING SYSTEM.

We can call one SQR program from another SQR program using Call.callsystem. But this is possible only in UNIX Operating system.

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.
5. RESET.sqc : This is an important Footing SQc.

Some of the most commonly used SQC's are

USING File Handling we can IMPORT.

Steps are

1. OPENING a File Using 'OPEN FOR-READING' COMMAND.
2. Reading Data From File Using 'READ' command.

Writing Data into Table Using 'BEGIN-SQL' PARAGRAPH & 'INSERT' command

Using File Handling we can Import.

Steps are

Writing Data into Table Using 'BEGIN-SQL' paragraph & 'INSERT' command

We can DEBUG SQR using DEBUGGING COMMANDS such as:

1. #IF DEBUGxxx
#END-IF
2. DISPLAY and
3. SHOW

We can Debug SQR using Debugging Commands such as:

1. #IF DEBUGxxx
#END-IF
2. DISPLAY and
3. SHOW

SQR:

1. This is nothing but the Actual
2. This consists of Program SECTION.
3. This can be Compiled & Executed. 
4. We cannot CALL ONE SQR from SQR or SQC

SQC :

1. This is LIKE a function in SQR.
2. This does not consists of program Section.
3. This cannot be Compiled and Executed
4. We can call one SQC from ANOTHER SQC or another SQR

SQR:

SQC :

TYPES of variables in SQR are:

1. COLUMN Variables.
2. List Variables.
3. System Variables.
4. Document Variables.
5. Substitution Variables.
6. User Defined Variables

Types of variables in SQR are:

COLUMN VARIABLES & SYSTEM Variables are Read-Only Variables.

Column Variables & System Variables are Read-Only Variables.

USING ASK & INPUT COMMANDS.

Using ASK & INPUT Commands.

Using PROCEDURES & SQC's to access data on RUN Control PAGE (Run Control FIELDS).

Using Procedures & SQC's to access data on Run Control Page (Run Control Fields).

This is a Process SCHEDULER Interface sqc which is USED to initiate and TERMINATE some field VALUES such as Process_instance & Run_cntl_id

This is a Process Scheduler Interface sqc which is used to initiate and terminate some field values such as Process_instance & Run_cntl_id

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.

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.

'READXLAT.SQC' is the SQC USED to READ DATA from TRANSLATE Table.

'READXLAT.sqc' is the SQC used to read data from Translate Table.

'BEGIN-PROGRAM' SECTION is MANDATORY for an SQR program.

'BEGIN-PROGRAM' Section is Mandatory for an SQR program.

USING 'DECLARE-IMAGE , END-DECLARE' COMMAND.

Using 'DECLARE-IMAGE , END-DECLARE' command.

This is USED to SET the Default Environment based on the DATABASE such Printer Type, Paper SIZE, Date FORMATS Etc….

This is used to set the Default Environment based on the Database such Printer Type, Paper Size, Date formats Etc….

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).

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).

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.

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

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

Yes.

Yes.

ARRAYS:

1. Arrays can be declared in any section. 
2. Number of rows are not automatically 
3. We should insert DATA in to Array by programming.
4. We can retrieve any number of fields from a table.
5. We have Length & Data type.
6. We cannot directly print from Array. 

LOAD-LOOKUP:

1. Load-Lookup is declared in only in SETUP section only.
2. Numbers of rows are automatically added.
3. Rows are automatically INSERTED in to Load-Lookup.
4. We can retrive only TWO fields from from a table. a table.
5. We don't have Length & Data type.
6. We can directly print from

ARRAYS:

LOAD-LOOKUP:

Three STEPS we have to FALLOW to run the SQR program from the peoplesoft environment (Process Scheduler).

These steps include:

• 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.
• 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.
• Giving the permission to the user by giving process groups.

Three steps we have to fallow to run the SQR program from the peoplesoft environment (Process Scheduler).

These steps include:

USING override OPTIONS in the PROCESS DEFINITION in the Process Scheduler in the PEOPLE Tools.

Using override options in the process definition in the Process Scheduler in the People Tools.

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.

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.

From the FIELD VALUE of 'RUNSTATUSDESCR'field from 'PS_PMN_PRCSLIST' table.

From the Field Value of 'RUNSTATUSDESCR'field from 'PS_PMN_PRCSLIST' table.

After special character (EX.#,&) we use UNDERSCORE(_) after that VARIABLE name.

After special character (ex.#,&) we use underscore(_) after that variable name.

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.

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.

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.

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.

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.

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.

SETUP, HEADING, Footing, Program & Procedure SECTIONS. Program section is the MANDATORY section.

Setup, Heading, Footing, Program & Procedure Sections. Program section is the Mandatory section.

This is USED for DEBUGGING in SQR.

This is used for Debugging in SQR.

USING "DECLARE IMAGE" COMMAND

Using "Declare Image" command

SHOW & DISPLAY are DEBUGGING COMMANDS

Show & Display are debugging commands