Interview Q&As All

APP DESIGNER :

What are the   Development Steps?

8 steps in Developments steps:   Design the Application, Create Field Definition, Create Record

Build the Record, Design the Page, Create Component, Register the Component, and Test the Application

Translate Table?   A translate table is a prompt table that serves as data dictionary to store values for fields that don’t need individual prompt tables of their own.

Limitations of Translate table?  3. Field values should be small (static).

1. Field type should be character   2. Minimum Field length should be 1 to 4 characters.

What is a prompt table?  Prompt tables are the most common methods for validating data entry in ps applications.                 This is like application data table the only difference is that it stores values that other tables can validate against. Prompt table is a control table. (Country table and Company table)

How many Table Edits are there in Record field properties?

1. Prompt Table With Edit  2. Prompt Table No Edit 3. Translate Table 4. Yes/No Table Edit

Prompt Table No Edit: Provides List of Suggested values, which users cannot edit.

Prompt Table With Edit: Users can edit the contents of the field against the values that are maintained in the specified prompt Table.

Translate Table Edit: Which is nothing but a translate table created by developer.

Yes/No Table : I need some situations we want the user to enter only Boolean values like yes or no, true or false in this situation we will use yes no table.        

 What is a record?   A record definition is a definition of what your underlying SQL database tables will look like,and how they will process data.

What are the 3 build options available when building a table from a record?

Build script file: which generates a sql script that can be run against the desired database to build the table.

Execute SQL: which builds the table in the database. Execute and build script: which both Build the table and generates a SQL script.

How many types of tables are there? Define them?

SQL Table: A physical SQL Table present in the database, this is the default.

SQL View:  Not a physical SQL Table but corresponds to a view written in SQL referring one or more fields from different tables. This gives a filtered view of information stored in tables. Stored in the database as a view. (Query view is system generated and SQL views are created by developer) whenever you modify a record you need to take care: check if any views are dependent on this record. 

Dynamic view: A record definition that can be used like a view in pages and People Code but is not stored as a SQL view in the database. Dynamic views provide superior performance. 

Query View: using the PeopleSoft Query we don’t need to write a query it will automatically generate by dragging and dropping. This is a two tier tool and runs through application designer and produces SQL dependent upon the host RDBMS.

Derived/Work: We can use it to calculations. It is not an object, and also we can use it as a funclib. A temporary workspace to use during online page processing. A derived or work record is not stored in the database, so no need to build it.

A derived / work field may be used to store and display a calculated value on a panel or to pass a value from one People Code program to another.

Sub Record: Group of fields used in multiple records like companies having address data of employees. If you have to use the same fields in different record definitions, then we will store all those fields in a subrecord and add this subrecord as a field to the record definitions. Stored in the database.

Temporary Table: we will do all the manipulations on the data present in the temporary table. Increases the performance, as it is not stored in the database. Used mainly for the AE batch processes.

Dynamic view? Where we can use dynamic View? It can be used like a view in pages and people code but not stored in database as a sql view, instead the system uses the view text as a base for the sql select that is performed at runtime. Dynamic views shows superior performance in some situations such as search records and people code selects.

Difference between Query Views & Dynamic Views?

1. Query Views are designed using Query Manager Tool where as we write SQL in Dynamic Views.

2. Query View is Built and Exists in the Database where as Dynamic View can’t be build so it doesn’t exists in Database.

Diff:  SQL View and Dynamic View?    

SQL views are written against the RDBMS catalog tables.     Dynamic view is declared only to p.tools and not to the system catalog.

SQL views r not recommended for performance tuning as they run against the database.  Dynamic views improve performance. SQL views reside in the Db, dynamic views are not.          

Difference b/n   primary record and the search record?

All fields in a scroll area or grid must be associated with one (and only one) record, which is the primary record for that level.   The search record builds the search page for a component and populates all fields at level 0.       

Subrecord?  A SubRecord by definition allows you to add a group of fields that are commonly used in multiple record definitions. A SubRecord must be defined before it can be entered into a record definition. Insert - SubRecord

What are Sub panels?  

If you have groups of controls, such as address controls, that you use on multiple panel definitions, you can save it as a subpanel. And insert this sub panel wherever you want.

What are Secondary panels?   Secondary panels are used to gather or display information related to the objects that appear in a primary panel. A secondary panel can be called from the primary panel and used to enter or display additional data without cluttering your primary panel.

Difference b/n standard, secondary and subpage?        

Standard page: It is the graphical interface between users and database. The important data is displayed in the standard page. We can place secondary as well as a subpage in standard page.      Take any page , PRESS Ctl+J  on portal

Secondary Page:  Page that will open on users request but remains as a secondary to the primary page display. We can open it through standard page only.

The data within secondary page is at the same level or below the level of the page control that launches the secondary page.

Subpage: subpage controls are used to provide standard look for the commonly used fields.  A sub page is referenced as a page control on multiple standard pages.

How to invoke the secondary panel?   There are 2  ways:

1.Insert a PB control type of Secondary Panel. This automatically invokes the secondary panel when the user presses the PB.

2.Insert a secondary panel control. This is invisible at run time. When you use a secondary panel control, u must also insert a command PB on the panel and call the DO MODAL Pcode function from the PB's FieldChange event in order to invoke the secondary panel.                    

Difference between subpage and sub record

All the fields in a subpage must be associated with fields of a sub record in the specified record definition.  Subrecord contains all related fields, whereas subpage contains fields from sub record.

What is occur level and occur count?

Occurs Level: Occurs level determines the level of the object that we inserted. By using this we can set levels for example after inserting a level based control in level 2 if you want to change the level based control to level 1,U can change the occurs level.

Occur Count: Occur Count determines how many rows should be displayed.                                                                               For ex if you want the scroll to     display only 5 rows then we will give occurs count to 5.

What is a Related Display field and Display Control field?

A related display field is for display purposes only - it always references a row that's not being updated from the current panel. The display control when you select Related Display field, you need to relate it to the appropriate control. A list of all the controls on the panel marked as display control fields in the Related control field box. Select the control field to Which this particular related display is related.

Scroll Area?  SA Is a Page Control which contains Child Records’-Fields which is used to maintain the Parent-Child Relationship between Tables. We can place any number of scroll areas on a page.

What are Level Based Controls and their Difference?  Level Based controls are Scroll Area, Grid and Scroll Bar.

Scroll areas have their page fields arranged by the developer. Page fields in a gird always appear in a single row. In grid we can view no of rows by giving occurrences. Scroll areas provide a row separator when you select multiple occurrences of data. We can view multiple rows in a grid while in a scroll area you can retrieve only one row at a time. One can have nested scroll areas but not grids. You can insert grid in a scroll area but not scroll in a grid.

What is Parent Child Relationship?    To maintain parent child relationship if the parent table contains one key field the child table should contain the same key field and one additional key.

Conditions are: 3) we can not go for more than three levels of parent/child relationships.

1) The child record should have all the key fields of parent record and at least one more key field other than the key fields of parent record.

2) We should mention the parent record in the record properties of child record.

Advantages are:   1) To have referential integrity.     2) No need to enter information again and again

What is the difference between scroll area and Grid? I used in Employee and Employee dependent details

Scroll areas single row of data will be displayed whereas in grid multiple rows of data will be available.

             Yes I used scrollAreas, 4 Levels of scroll areas are in Peoplesoft, and they are level0, level 1, level 2, level 3, level 4

How many levels can we have on a page? 

Page allows Nested levels up to 3 levels.          

Deffered and Interactive Processing

In Interactive mode for every cursor action there will be a trip to the server. In deferred processing mode, trips to the server are reduced. In Interactive whenever a user exits a field there will be a trip to the server.

In Differed processing until a user performs an action, which requires a trip to the server there wont be any trips to the application server.      

In how many places we can set Differed processing?  

Field,Record,Page,Component

What are the 3- Actions that can be attached to Menu?  -

Component  -Separator  -PeopleCode

What is registering a Component? 

We will add the component to a Menu and give permissions and security.

Component Buffer?  

Area in memory, which stores data for the currently active component. Whenever you open a component the entire data for that component is retrieved upfront and stored in the application server. Component buffer is a temporary buffer allocation.

Component processor?  

It is a peopletool runtime engine that controls the processing of the application.

Data buffer?   

Data buffer contains multiple components of data.

Difference between component buffer and data buffer? 

Component buffer contains active component data i.e. level 0 data. Data buffer contains multiple components of data.

RowSet

is data structure representing the hierarchical data, RowSet consists of set of rows, where Each Row consists of set of records and child RowSets.  Used this Rowset object to retrieve the data from component buffer.

What data comes into Component Buffer?

Primary record data, related display fields, translate values and derived work records. If you keep key and alternate search key on 0 level then only 2 values will appear in Component Buffer the other field values will not available. In Component Buffer if you want to see the other field values then place a non-key field on 0 level and make it invisible. Every level will have its own primary record. A level 0 primary record and a search record can be different but the key field values should match.

Business Unit? What are the major functions?  

It performs 2major functions : 1),2)..

A business unit is a way of tracking specific business information for reporting and other roll-up data collection.             

1) It allows you to maintain a tree structure to facilitate customer-specific roll-up reporting.

2) It allows you to distribute and administer certain control tables like the department table.                     This is extremely important for large or multinational companies.

What is Setid? 

Keys to linking business units to record control groups. It is the key on all control tables in TableSet sharing every row in a control table includes a setid as its key.

SetIDs partition the data into groups or sets of data that are appropriate for each Business Unit.

What is Table Sharing?  

It is a method of filtering prompt data so that only appropriate values are available for data entry. With TableSet sharing, a single table can offer different values based on underlying business rules.    Table set Sharing is organized around these basic elements:Business Units and transaction tables, SetIDs and control tables, Business rules.

Record group

consists of records with similar functionality. To setup a record in record group we should enter a set control field value in record properties

Difference  b/n     a Business Unit and a Set Id?

It’s a way of organizing your data in the system.

A BU is a way of tracking and reporting specific business information for reporting and other roll up data collection.  It does not have to be a legal entity for HR.  It’s a way of organizing your organization.

A SetID is a link to Table Set Sharing. Table set sharing simply consists of assigning specific SetIDs to specific                   record groups for individual Business Units to restrict the view of rows of data within Control Tables.                                                         

Joins:   

Inner join:   Matches key fields & gets the common part in the case of Inner join.

LOJ (Left-outer join):      Matches key fields, and gets common data as well as additional data in left table.

ROJ:     Matches key fields, and gets common data as well as additional data in rightt table.(we cant perform this in ps)

Self join(SJ): Joining same table is called self join.   

SUB Query: the output of one SELECT statement  acts as an InPut for another SELECT statements.

 About Views:

A view is a database object that is a logical representation of a table. It is derived from a table but has no storage of its own and often may be used in the same manner as a table.   Creating a view:

Create view INVASION AS   Select WEATHER .City, Condition, Temperature, Latitude, NorthSouth, longitude, EastWest

From Weather, location,    Where   WEATHER.City = LOCATION.City;               Search views   are used for three main reasons.

1. Adding criteria to the search dialogue page      2. Providing row level security.    3. Implementing search page processing.

Advantages of Using Search Views instead of Search Table:

As the search view is created with selected fields there will not be much stress on app server and there by improves the performance.  Search View also gives additional search criteria.

In PIA (?) …  End users do not have any peoplesoft specific software installed on their machines, they use Internet browser to connect web server, which interacts with application server.

The Effective Date:
- Allows us to maintain history, current and future information.
- Represents the date that the specific data change goes into effect.
- Enables us to maintain a complete chronological history of all records.
- Works as a cross-reference, as the system constantly compares the effective dates to information stored in other tables. This ensures that data you select from PeopleSoft is valid as of the desired effective date.

Effective date?

Date on which a table row becomes effective, the date that an action begins.

 Ex:  if you want to close out a ledger on June 30, the EFFDT for the ledger closing would be July 1.                                                  This date also determines when you can view and change the information. Pages or panels and batch processes that use the information use the current row. Effective date enables us to keep historical, current, and future information in tables. There are 3 types of effective dates: Current, History and Future.

Past date - Within 30 days of current date is called past date. History date - Above 30 days of current date is called History Date.

Effective status?  

EFF_STATUS usually accompanies EFFDT.

Ex:  in Department table suppose we inactivated one dept from so and so future effdt, from that future date onwards this dept is going to be inactivated, after that whenever we click the link prompt button of dept id field we can see only active departments.

Effective sequence?

Whenever we want to enter more than one transaction on the same day for an Employee like promotion and salary hike we will use effective sequence. Effseq number starts with 0

Use of EffSequence.  

same day different action will be performed so Effetivedate , date will be same so to differentiate from other we need a sequence of order so it shows the order of occurrence.  Which is used to maintain different row for the same key value & same Effective Date. The search record is determined at the component level.

SELECT A.EMPLID FROM PS_JOB A        WHERE A.EMPLID = '12345'

AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED    WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= GETDATE())

AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES   WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT)

Component Interface

   

CI is used to access Cmpt from external clients (Third Party Applications) including all business rules and data without going through the ps client.     To expose cmpt (methods and properties) to the external software (ex: vb, c, c++, java, com, dcom etc). Fundamental elements in the CI architecture: components, CI’s, CI API.

 How do you provide security for the component interface?

1.Open the Permission list   2.On the Component Interface tab   3.Add row and select the newly created CI

4.Edit the permissions to give permission for the standard methods    5.Get, Create, Save, cancel, find.

ATTRIBUTES of the Component Interface? and different keys in CI?   

1. CI name.

2.Keys   (get keys, create keys, and find keys).  3. Properties and collections   (fields and records),4. Methods (Save, Cancel...).

KEYS   Get keys are nothing but search key of the component search record, We cannot change the get keys.

Find keys are based on search key and alternate search keys of the search record. We can remove find keys if we don’t want to use

Create keys are based on the add search record keys (add mode) and generated automatically for the CI.

We cannot change the create keys.    Create key are generated only when the CMPT is in Add action only.

Properties: Properties are individual fields in component for CI.   (Standard properties exists for every CI)

***Properties are 2 types’    Standard properties, User defined properties

Createkeyinfocollection Developer Can Further Control The Exposed

Getkeyinfocollection field properties.   Findkeyinfocollection   Property Info collection

GetHistoryItems (Update/Display mode or Correction mode)  EditHistory Items    InteractiveMode.

Collections are referred as scrolls (level based controls will be having a primary rec) in the component for CI.

CreateKeyInfoCollection: Returns a set of items that describes the create keys.

GetKeyInfoCollection: Returns a set of items that describes the get keys

FindKeyInfoCollection: Returns a set of items that describes the find keys

PropertyInfoCollection: Returns a set of items that describes properties.

How do you login in Correction Mode in the CI?

 Get History Items and Edit History items property to should be set to true.  Get History Items alone: - Update display all - modes will be used.

Methods? How many Methods are there?    

A method is an object that performs a very specific function on a component interface at run-time.   There r  2 types of methods are there: Standard methods and user-defined methods.

                    The event of a CI is methods (like On Execute for AE.)    

Standard methods: - Automatically generated upon the creation of a new CI  in Application.

Apart from the Standard methods there are Standard methods available for the use with any collection.

User-Defined methods: - User-defined methods are those that you can create to meet the requirements of an individual CI.

Can we map more than one component at a time from the CI?   

NO

Can we create the get keys and find keys manually?  

No, created based on the search record definition for the underlying component. 

Types of properties can include the CI? 

Standard properties    User-Defined Properties

What are the standard properties? 

Interactive Mode    GetHistoryItems     EditHistoryItems.

Can we create the user-defined methods?

Yes. We can create user-defined methods after creating a CI.

What the steps that you need to do in people code to invoke Component Interface?

The following are various steps that describes the peoplecode logic while implementing a Component Interface

1.Establish a user session   2.Get the Component Interface   3.pulate the Create Keys   4.Create an Instance of the CI                    5.pulate the required fields   6. Save the CI

         &Session = GetSession();     &CI = &Session.GetcompIntfc(CompIntfc.INTERFACE_NAME)

&CI.KEY_FILED_NAME = ‘NEW’

If not &CI.Create () Then

Else  Populate other fields   End-if;

Populate the other fields

If not &CI.Save () Then   Else   End-if;

 If you are PS developer then In what scenarios Component Interface will have advantages over component?

A component exposes your pages to peoplesoft only. Moreover it helps us to define common properties for a set of related pages.
A CI is a set of application programming interfaces (APIs) that U can use to access and modify PS DB information programmatically. 

PS CI expose a PeopleSoft component (a set of pages grouped for a business purpose) for synchronous access from another application (PeopleCode, Java, C/C++, or Component Object Model [COM]).

How do you TEST Component Interface? 

1.Using the CI tester   2.Give values in the tester for options  3.Get Existing, Create new, and Find and perform the operation from the CI Tester

Catching Error Message in the CI? Or Use of PSMessages in the CI?

This function needs to be called when ever methods like Find, Save, Create methods return false.

Error text and Error type can be printed in the log message for any other action in to the Log Message.

Function CheckErrorCodes()   &PSMessages = &Session.PSMessages;   &ErrorCount = &PSMessages.Count;

For &i = 1 To &ErrorCount

&ErrorText = &PSMessages.Item(&i).Text;   &ErrorType = &PSMessages.Item(&i).Type; End-For;   End-Function;

Traversing …  the Collections in the CI? 

COLL_JOB – Collection        Coll_JOBItm – Row in the collection.

&COLL_JOBCol = &CI_JOB_DATA.COLL_JOB;

For &i = 1 to &COLL_JOBCol.Count

&COLL_JOBItm = &COLL_JOBCol.Item (&i);    &COLL_JOB_JRCol = &COLL_JOBItm.COLL_JOB_JR;

For &J = 1 to &COLL_JOB_JRCol.Count

&COLL_JOB_JRItm = &COLL_JOB_JRCol.Item (&j);   &COLL_JOB_JRItm.KEYPROP_EFFDT =;

How can a component have more than one search record? Give a situation.

You might want to reuse the same component multiple times with different search records. You can accomplish this by overriding the component search record at runtime when the component is opened from a menu item without creating separate copies of the component. The component override is temporary, and occurs only when the component is opened from the menu item in which the override is set. It does not change the component definition.

Why should we write in save post change only, why not in other event?

Because CI object data will not be in component buffer and student information component data will not be in                    component buffer so we are updating/inserting the data in the data base not in the component buffer tables.                        Whenever we create a CI, we will give a name to CI to access.

What is SQR used for?  It is a Reporting Tool for creating customised reports to be run from PeopleSoft panels for formatting reports, data transfer, data uploading/downloading etc.

Where and when do we use SQR?   1. Where the Programming logic is more.                           2. If We need to generate different kinds of reports.  3. Background SQL processing.  4. File Interfacing. Etc…

Processing sequence of SQR………

Compile Stage    All #Include external source file (SQCs) are inserted into the source.

All #commands are evaluated. The #DEFINE text substitution variables are placed into the program source.

The SETUP section is processed, including allocation and population of memory arrays created by the Load-Lookup and Array commands.

Work buffers are allocated.   Optimization of the SQL data access path is determined.    Syntax of SQR source program is checked.

Execution Stage Processes data for the Body section   Processes HEADING section   Processes FOOTING section

Writes entire page buffer to file.

How you can see the SQR Errors (after Execution)?

SQR normally places the SQR PROG output files in the directory from which you run the PROG. The output file has the same file name as the SQR file that created it, but the file extension is different.

To produce the desired output file for this exercise, the -KEEP flag is used in our example.                                                 We explain the -KEEP flag later in this guide.

The output files should appear as soon as your PROG has finished running. If you specified the -KEEP argument, one output file is in SQR Portable Format (recognizable by its .SPF extension). SQR Portable Format is discussed later in this guide but for now, you can easily view the sample PROG’s .SPF file output, ex1a.spf, on Windows platforms with the SQR Viewer GUI (sometimes referred to as an “SPF viewer"). The SQR Viewer is invoked from the command line with “sqrwv”.

Default output file is .lis If u specify -keep the output file will be in .lis or .spf(sqr portable format)

What are the sections and paragraphs available?

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

Begin-setup End-setup.   Begin-program/Report  End-program.  Begin-procedure End-procedure.

Begin-heading End-heading.  Begin-footing End-footing

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

What is the only required section of a SQR Program?

The only required section of SQR program is BEGIN-PROGRAM with its corresponding END-PROGRAM.

What is a difference between Begin-Select & Begin-SQL?  BEGIN-SELECT has only one SQL statement.

BEGIN-SQL can have any number of DML statements & it can also have PL- SQL statements for oracle & each statement should end with double semi colon (;;).

File types in SQR?  

1. “.SQR”=This is nothing but the SQR source file.   2. “.SQC”=This is a used as functional Libraries.

3. “.Lis”=This is Line Printer Output file.        4. “.SPF”=This is a Portable format file.

5. “.SQT”=This is Complied time file.   6. “.INI”=These files are used to set the default Environment for our SQR Report.

7. “.MAX”=These are used to increase the memory size.  8. “.ERR,.LOG,.DAT”=These are used for Error Handling purpose.

What is the SPF File? What are the Flags required to make a file to SPF?

SPF is a Portable Format File and the command line flag used is –KEEP.

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.

Can a .sqt file be "de-compiled" into the original SQR code?  

No. This is not possible.

What are the uses of SQR portable files(spf)?

We can create the printer independent output files, so that we can view, e-mail, fax and print these files by using the SQR Viewer. And we can also convert back to the .LIS files by using the SQR printer.

What is the difference between .LIS and .SPF files?

Lis files are printer specific output report files. .spf we can create printer independent output files. .lis files are not convinient for viewing the file before printing or to email. .spf files can be viewed and printed by the SQR viewer and also can be emailed.

List Out some Command Line Flags in SQR? 

 -S,-A,-KEEP,-ZIV,-XMB,-ZMF,-E,-O,-Debug,-RS,-RT etc…

What is –Bnn,--Tnn,--RS & --Rt Flags?

--Bnn for specifying default number rows to retrieve from the database.

--Tnn used for Testing Which specifies the number of pages we want to test.

--RS is used to create a .SQT file.    --RT is used to run .SQT file.

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.

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

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

How can I suppress display of the SQR banner line when running SQR

Use the -XB flag.

What is the value used with -C flag of BEGIN-SELECT?

BEGIN-SELECT -Cflag (Oracle, Ingress, SQL Base) - sets query buffer size.

Use the -F command line flag for multiple reports?

Let’s look at the following example. The program "ex20a.sqr" creates 3 reports.

sqr ex20a tutorial/secret -Flabel.lis -Fletter.lis -Flisting.lis

Which types of command line flags are useful for creating the .SPF files?

-ZIV, -KEEP, -NOLIST.

What are –ziv and –keep command line flags?

-keep is the SQR execute command line flag causes SQR to create spf output in addition to .lis.

-ziv is used for invoking spf viewer after generation SQR output files.

SQR for TESTING purpose?

-Tnn command line flag. For example, -T6, it will stops the program after six pages of output have been created.

We use the –Tnn command line flag?

If we use this flag SQR ignores BEGIN-SELECT ORDER BY clauses.

What is the use of the –Tnn command line flag?

We can speed up the testing cycle. And we can stop the execution after getting the specified number of pages for our report output.

What are the other command line flags supports the SQR for Testing and Debugging purpose?

-S, -C, and –E[file]

What is the use of the –S command line flag?

It will show the status of the program cursors. After completion of the program it will display the following information:

Text of each SQL statement

Number of compiled SQL statements and number of executed SQL statements.

Total number of rows selected.

All these information will be stored into the log file.

What is the use of the –C command line flag?

It creates the Cancel Dialog Box during the program run, So that we can cancel the execution of the program when necessary.

What is the use of the –E[file] command line flag?

We can save the error messages by specifying a file name. Default file name is the name of the program with the extension .err. If SQR generates no messages, no err file is created.

What is the value used with -C flag of BEGIN-SELECT?

 BEGIN-SELECT -Cflag (Oracle, Ingres, SQL Base) - sets query buffer size. If the value of the -C flag is up to 256 it is in kilobytes, any number over 256 is in bytes only.For Oracle the default is set by the context_area parameter in the init.ora file - the default is 4096.

How to use the -F command line flag for multiple reports ?

Lets look at the following example. The program "ex20a.sqr" creates 3 reports.

Example:  sqr ex20a tutorial/secret -Flabel.lis -Fletter.lis -Flisting.lis

In the above example, what would be the names of the SPF files if we use a -keep command line flag ?

The names will be:   label.spf  letter.s01   listing.s02

How to find out Which SQL is running in SQR?

Using -S flag while executing SQR at runtime.

How can you Debug SQR programs?   

We can Debug SQR using Debugging Commands such as

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

What is Debug Command Line Flag? 

 -DEBUGxx.

What are the debugging commands in sqr?

Show & Display are debugging commands

Show is used to display more than one variable at a time.  Display is used to display only one variable at a time.

How do we Debug SQR?

By using #Debug, or #Ifdef Debugx   #endif. Using Show or Display command.

What Commands are used to show Output on Screen of an SQR at runtime ?

Display & Show are the commands for that.

What are the commands SQR supports for Conditional Debugging purpose?

1.       #debug 2.#if 3. #Else 4. #ifdef 5.#ifdeg 6.#End-if

What happens? If we specify like the following:

2.       Suppose that –debugxyz is command line flag and the following are debug statements.

#debugx show ‘x’

#debugy show ‘y’

#debugz show ‘z’

#debugz show ‘a’

#debugz show ‘b’

The output is

show ‘x’

show ‘y’

show ‘z’

because #debugxyzab flag was not specified.

What is the limit for SQR command line length on PC/Windows?

The limit is 128 characters. This is a PC limitation not an SQR limitation. You may want to have the SQR BINW directory on your PATH so that the full pathname to the SQRW executable is not needed. Arguments can be put into a report argument file that is specified with @file on the command line.

How will perform Error-Handling in SQR?

Using the command line flags –O,-L,-ZMF and –XMB

Using SQR commands ON-ERROR=Skip/Warn/Stop, ON-ERROR=Procedure_name

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

How do you trap Errors in SQR?  

a) Using BEGIN-SELECT ON-Error=ErrorProcedureName

b) In procedure display #SQL-Error to show ErrorNumber &$SQL-Error to display  message.

What are the File Manipulation commands in sqr?

Open,Close,For-Reading,For-Writing,For-Appending,Read & Write.

What are the commands used in File Handling of sqr?

Using File Handling we can Import. Steps are

1. Opening a File Using ‘OPEN’ ‘FOR-READING’ commands. 1. Reading Data From File Using ‘READ’ command.

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

What are Open & Read functions of File Opening in SQR?

Open ‘Vendor.dat’ as 1 for-reading record=50:vary

Open ‘C:\sqrtemp\vendor2.txt’ as #1 for-writingrecord=93:FIXED

If #filestat=0 (file opened successfully)

Write #1 FROM $iSalVCode:3    End-If

How does SQR provide you the ability to verify that a file was opened successfully?

If you assign a numeric variable to the STATUS=in a file OPEN, you may check the numeric variable after the OPEN. If the OPEN fails, this variable will contain -1.

What is the maximum record length for the OPEN command?

The maximum is 32K bytes (32767).

How can I read in variable length delimited fields from a sequential file?

An example format would look like this: field1|field2|field3|...

Ans: Instead of reading a record into individual fields, read the entire record into one field and then use the SQR unstring command to separate the delimited fields.

Example :

begin-report       open 'test.dat' as 1 for-reading record=80:vary

while 1                      read 1 into $record:80      if #end-file

break      end-if

unstring $record by '|' into $field1 $field2 $field3 ...

show $field1 ' ' $field2 ' ' $field3 ...                   end-while     close 1     end-report

Can I use 'dynamic column names' in Begin-Select (versions prior to v3.0)?

Yes, see following workaround. This SQR report will actually write another SQR code with column names supplied by the user and execute it. Note that in SQR Version 3.0, dynamic columns are directly supported.  Example:

begin-procedure main

move 'DEPTNO' to $col1       ! You might use INPUT here to prompt the user

move 'ENAME'  to $col2        ! to enter the column and table names instead.

move 'EMP'    to $tab

move 'sqrout.sqr' to $sqrpgmnam     let $syscmd = 'sqr '||$sqrpgmnam||' user/password'

move 100 to #flatfileid     open $sqrpgmnam as #flatfileid for-writing record=132

write #flatfileid from 'BEGIN-REPORT'    write #flatfileid from '   DO MAIN'

write #flatfileid from 'END-REPORT'     write #flatfileid from 'BEGIN-PROCEDURE MAIN'

write #flatfileid from 'BEGIN-SELECT'   write #flatfileid from $col1 ' (+1,1)'

write #flatfileid from $col2 ' (,30)'    write #flatfileid from 'from ' $tab

write #flatfileid from 'END-SELECT'   write #flatfileid from 'END-PROCEDURE MAIN'

close #flatfileid    call system using $syscmd #status      display 'Status of call is ' noline     display #status   end-procedure

How can I find out if rows are selected by a BEGIN-SELECT paragraph? Can I use the SQR variable #sql-count?

#sql-count indicates the number of rows affected by a DML statement (insert, update, or delete).

It cannot be used for a BEGIN-SELECT paragraph. The solution is here below:

begin-procedure main1

move 0 to #count   ! This is optional unless main1 is called more than once

begin-select   column1  column2  column3   add 1 to #count  from table1  end-select

if #count > 0     show 'Number of rows selected was ' #count edit 999

else    show 'No rows selected'    end-if     end-procedure main1

How do you control the number of iterations or fetch records firing SQL statement in SQR.              By using Begin-Select Loops=<number> statement.

What PeopleCode must be on all Run Control Records?

: On the PRCSRUNCNTL record, the RowInit on OPRID and the SaveEdit on RUN_CNTL_ID.

What restricts the size of LOAD-LOOKUP memory arrays? : Available memory.

How can I prevent the printing of the empty page at the end of every .LIS file?

Delete the last line in the .LIS file through an editor or this can be automated with CALL SYSTEM USING command which would execute the following SQR code.This program reads an ASCII file, removes the last line (the line having the Form Feed) from it and writes the output to another ASCII file.

In BEGIN-SELECT do the FROM and WHERE clause have to be aligned to the left margin

No. Only the selected columns have to be.

What are the steps in performance tuning? 

Using Loadlookup & Arrays, Using Command line flags such as –bnn & -s, Using proper programming in our SQR program, Using Proper SQL in our SQR program, Using Multiple Reports, Using SQT files and Running on Batch Server.

How many ways of performance tuning are there in SQR?

1. Using LOADE-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.

How to pass Inputs for sqr program while running from windows?

Using ASK & INPUT Commands.

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

Use of the MOVE Command?   It moves the data from on field to another field and it can also perform data conversions and data editing using special edit masks. it can handle data of any format except date to numeric vice versa.

What is Let command?   A single Let command is capable of replacing a no of data manipulations and logic commands

What is the syntax of the LET Command?

LET target_variable = expression   Expression can be combination of operands, operators, and functions.

Operands in the expression can be SQR columns, literals, or array fields.

Operators in the expression can be arithmetic, string or relational.

What is the difference between Move and Let command?

Move command is used for data conversions where as let command used for data conversations as well as logical expressions and calculations.

What is the difference between Command line flag arguments and command line arguments?

Command line flag arguments are simple parameters related to each specific flag. And command line arguments are application specific input parameters that are retrieved by the program from the command line using the input and ask command.

Diff between ASK & INPUT Commands?

”ASK”:  1. This is used in the Setup section i.e. . Compile Time Variable.

2. This prompts for the values during the compilation time.    3. This does not have the data type and length.

“INPUT”   1. This can be used in other than the Setup section i.e. Run time Variable

2. This prompts for the values during the Execution Time.      3. Here, we can specify the data type and the length.

What is a difference between Ask & Input command ?

Ask is a compile time substitute variable which is defined in Begin-Setup. It is used to take an input at compile time. For ex:  you want to accept a printer from user at compile time. Input command is used to accept user input at runtime.

How do we work on Effective dated rows on SQR?   Write a subquery to get current effective date & then write an outer query for matching effective dated rows from a record.

Effdt and EffSeq Quries?

Select * from Table1 A where A.EffDt= (Select Max (B.EffDt) from Table1 B where

A.keys B.keys AND A.EffDt<= as of Date)

Select * from Table1 A where

A.EffDt= ( (B.EffDt from Table1 B where A.keys B.keys AND A.EffDt<= as of Date) AND

B.EffSeq=(Select Max(C.EffSeq from Table1 C where B.keys=C.keys AND B.EffDt=C.EffDt AND B.EffSeq=C.EffSeq);

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

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

What is Process Scheduler?   

This is a People tool used to Schedule and monitor the Execution of any Process or Report or Program in PS Environment.

How do you link SQR reports to process scheduler?

-    Create/modify/add run control table if you have any new fields

-    Create/modify/add run control page if you have any program inputs

-    Create a menu definition (Note Menu group name: XYZ)         - Give operator security

-    Create Process scheduler definition  -Use-Process definition    – process definition add

-    Give report name and report type.

How do you Execute SQR using Process Scheduler ?

-    a) Include some RunControl panel having OperatorID & RunCntlID.

-    b) This panel includes PRCSRUNCNTRL table & RUNCNTRL  table having  OperatorID & RunCntlID.

-    c) Add your own input fields to this RunControl record & also drag drop it onto your panel.

-    d) Write PeopleCode OPRID = %OperatorID; in RowInit event of OPRID field.

-    Write procedure into SQR code which would have an SQL statement to fetch you fields from RUNCNTL table. Remark all input fields. Execute this procedure in else part of If $prcs_process_instance = ‘’ which means that this sqr is to be executed from Process Scheduler.

What is Process Monitor used for? 

A centralized utility allowing you to monitor the status of process requests that you have submitted, by displaying a list of currently logged requests waiting to be processed. From this list you can view the active list of Process Scheduler Servers and stop their execution depending on your level of security.

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.

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.

How do you run SQR Report from DOS Prompt using a Batch File?

-          C:\FDM75SU\sqrbinw\SQRW.EXE

-          c:\sqrtemp\%1.sqr EPDMO/SYSADM/SYSADM –I     c:\FDM75SU\sqr\ <IncludeFile Path>

-          -Mc:\FDM75SU\sqr\AllMaxes.max -Oc:\SQRTEMP\sqr.log(LogFile)

-          -Fc:\sqrtemp\%1.lis(Output File)

-          -ZIFc:\FDM75SU\sqr\Pssqr.ini -lockrl

-          REM -XCB (do not display Communication Box)

How SQR identifies where we keep sqr files (Path of SQR files)? 

It is specified in Configuration Manager.

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       <Procedure name> [(report arguments)]         from Dual        End-select

To call from Begin-sql, the syntax will be

Begin-SQL             <procedure-name>;;           End-SQL

Where should you place user prompts for non-Process Scheduler execution?

 In the Init-Report procedure, in the following code:

If $prcs_process_instance=’’                !prompts go here        End-If

What PeopleSoft tool is used to change the value of your SQR environment variables?

 The Configuration Manager.

Running SQR from Command Line

D:\PT848\bin\sqr\DB2\BINW\sqrw.exe D:\pt848\SQR\infup_file_name.sqr -iD:\pt848\SQR\ -xmb -xcb -ZIFD:\pt848\SQR\pssqr.ini -S -DEBUGX -PRINTER:PD -EH_PDF -fD:\pt848\output_file_name.pdf -oD:\pt848\log_file_name.log              Options:
-i => Input file Directory  -f => Output File Name  -o => Log file Name -ZIF => SQR Initialization File Name

API aware?

An API aware process updates the process request (PSPRCSRQST) table with the process run status.

If we want to run any program we should specify

C:\>SQRW <PROGRAM_NAME> <DATABASE_NAME>/<PASSWORD> <OUTPUT_DIRECTORY>

C:\>SQRW SAMPLE.SQR HRMS/PS FC: \TEMP

If we want to pass any run time values we should specify ……

C:\>SQRW <PROGRAM_NAME> <DATABASE_NAME>/<PASSWORD> <OUTPUT_DIRECTORY> <values>

C:\>SQRW SAMPLE.SQR HRMS/PS FC: \TEMP ‘100’

How do we run the sqr through peoplecode?

This is the code to run the sqr thru PeopleCode.

      &RQST = CreateProcessRequest();    &RQST.ProcessType = "SQR Report";  &RQST.ProcessName = "HAPDT009";
      &RQST.RunControlID = "1";
      &RQST.RunLocation = "PSUNX";                   &RQST.OutDestType = "File";
      &RQST.OutDestFormat = "PDF";               &RQST.RunDateTime = %Datetime;
      &RQST.TimeZone = %ServerTimeZone;          &RQST.Schedule();      
      &PRCSSTATUS = &RQST.Status

Variables And Arrays

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. and  User Defined Variables

What is #, $, & used for?        

#Numeric, $String, and &Database field values.

What are variable types in SQR?

& Data base reference fields – Read only     $ Character (Same for Date)

# Numeric       { } Variable in ASK or # define        [$ variable] Dynamic variable referencing

Is there any read-only variable in sqr? 

Column Variables & System Variables are Read-Only Variables.

What types of SQR variables are “read only”?

Database column variables and most internal reserved SQR variables are Read Only.

Are SQR commands and variables case sensitive?   

No.

Commands can be spelled with any case combination. The SQR variable $string is equivalent to $STRING.

How to refer a global variable in local procedure?

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

How do I Reference Global variable in a local procedure in SQR?

I can access Global variable with #_VariableName from local procedure.

How do you differentiate printing a database column & any other variable in a select statement?

Database fields should start with the 1st column.  Indent other print statements with at least one column right.

How do I right justify a text string?

Use the LPAD function of the LET command to add blanks to the start to the string. For example: let $s = lpad($s, 80, ' ')

How to print current date in SQR?  : Print $current-date (1,1) EDIT ‘DD/MM/YYYY’ or  use internal function or DateNow for current date & time.

How to Define Constant & its usage in Begin-Select?

#define MAX_LABEL_LINES 10

Can I set the current page number in an SQR program?   

Yes.

Move the desired value to the SQR variable #page-count.

Do SQR programs require a .sqr extension? Do SQR runtime files require a .sqt extension?

SQR programs do not require a .sqr extension. SQR runtime files require a .sqt extension.

How can I insert a null value into a numeric field in the database ? I tried not giving a #variable a value but that did not work, 0 was inserted.

When doing the insert, list only the fields for which a value is known. All other fields will get the null value.

How do you Optimise SQR ? 

a) You can optimise SQR by breaking big sql statements into simple sql  statements.

b)   Using LoadLookup command.   c)  Using an Array or flat file instead of database tables.

d)  Examining Cursor Status by using -S flag while executing sqr.

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

Exceeds the specified number.                             number of added.

3. We should insert data in to Array by programming.      3. Rows are automatically inserted in to Load-Lookup.

4.We can retrive any number of fields from a table.        4.We can retrive only Two fields from 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.

What is a syntax of LoadLookup ? 

It is declared in Begin-Setup.

Syntax: Load-Lookup Name=<name>

Table = <tablename>

Key = <primarykey>

Return_value = <FieldName>

Where = FIELDNAME= <fieldname>

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

Can Multiple Columns be retrieved in LoadLookup command in SQR?

Yes, it has to be joined by a double pipe symbol ( || ).

If I get a duplicate key warning message when SQR loads a lookup table, will the lookup table still work correctly.    

No. Use of a lookup table is dependent on not having duplicate keys.

How do you Define arrays in SQR ?

Ans:  Syntax:  #define array-size 50

Begin-Setup

CREATE-ARRAY NAME=<arrayname> SIZE=<size>

FIELD=<fieldname>:<DataType>:<Size>    End-Setup

How do you Fetch values from Arrays in SQR?                                                                          

Get & Put is used for inserting values in array.

2. Is it possible to increase the array size once defined? 

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

What is the difference between Arrays & Loadlookup?

Which is faster, a database join or LOAD-LOOKUP? It depends on your PROG.

LOAD-LOOKUP improves performance in the following situations:

When it is used with multiple SELECTS.

When it keeps the number of tables being joined from exceeding three or four.

When the number of entries in the LOAD-LOOKUP table is small compared to the number of rows in the SELECT, and they are used often.

When most entries in the LOAD-LOOKUP table are used With LOAD-LOOKUP,

Ucan reduce the number of tables that are joined in one SELECT. Use this command in conjunction with one or more LOOKUP commands.

The LOAD-LOOKUP command defines an array containing a set of keys and values and loads it into memory. The LOOKUP command looks up a key in the array and returns the associated value. In some PROGs, this technique performs better than a conventional table join.

You can use LOAD-LOOKUP in the SETUP section or in a procedure. If used in the SETUP section, it is processed only once. If used in a procedure, it is processed each time it is encountered Arrays :

An array is a unit of storage that consists of rows and columns and exists in memory.

An array is similar to a database table, but it exists only in memory.

In Arrays we contains the data  in load lookup array contains the set of keys and associated values.

What is the difference between Arrays & Loadlookup?

Arrays: An array is a unit of storage that consists of rows and columns and  exists in memory. An array is similar to a database table, but it exists only in memory.

Loadlookup: Function Loads an internal table with columns from the database. Allows for quick search  using the LOOKUP Usually this type of lookup can be done with a database join, but  joins take  substantially longer. However, if your report is small and the number of rows to be joined is  small, a lookup table cannot be warranted, and in fact can be slower, since the entire  table has to  be loaded and sorted for each report run.

Syntax   In the SETUP section:

^LOAD-LOOKUP

^{NAME = lookup_table_name    TABLE = database_table_name   KEY = key_column_name       RETURN_VALUE = return_column_name}

^{[ ROWS = initial_row_estimate_int_lit ]  [ EXTENT = size_to_grow_by_int_lit ]}

^{[ WHERE = where_clause_txt_lit ]  [ SORT = sort_mode ]  [ CURSOR = cursor_int ] [ QUIET ]}

What are the different types of Arrays in SQR?

Single dimensional, and   2 dimensional,  3 dimensional array.

3 DA used in generating the (depend, job, summary salary) of employee

2 DA used in generating the customer details and order details

1 DA   company id

15. How you can declare 2^nd dimensional Arrays? Using multiple arrays can allow you to reduce database calls. In the SETUP section, two arrays are created—one to summarize monthly orders by product,  and another to summarize monthly orders by price range.

BEGIN-SETUP    create-array      name=order_qty size={max_products}

field=product:char field=month_qty:number:3

create-array     name=order_qty2 size={max_categories}

field=category:char field=month_qty:number:3   END-SETUP

How you can declare 2^nd dimensional Arrays?

Create array     Name : purchage array size = 100  Field1: price:num   Field2: qty :num

create-array   name=order_qty       size={max_categories}  field=category:char  field=month_qty:number:3

How do you Use XLATT table in SQR ?

a) Include ReadXlat.sqc.    b) Put values into $fieldName, $fieldValue      c) Call procedure read-translate-table     d) Get $XLATLongName & $XLATShortName from XLATTABLE of PS database.

Difference between SQR/Crystal/nVision ?

SQR ReportCrystal ReportNVision ReportGUI It does not provide GUI interface to create reports. It provides GUI Interfacefor creating reports. It provides GUI Interface for creating reports.Interface writing. Uploading/Downloading data from flat file to PS & vis-a-versa.Cannot be used for Interface writing.Cannot be used for Interface writing.Programming Language SQR language for reporting is a programming language in which you can create your own SQC’s libraries for reusability & also you can hold intermediate data for processing etc. You cannot write complex programs, at the most you can use few If-Else statements based on the data fetched. You cannot hold intermediate data.Only prints reports based on  PSQueries. Excel Specific Output  SQR Report output can be on text file, word document etc. Crystal Reportoutput can be of any type.It provides output only for Excel spreadsheets.

What is a difference between EDIT & Format in SQR?

EDIT is used for formatting while printing & Format is usedwhile Input especially for date.

How do you make a zero value blank on a report?

Print #varname (+1, 1, 10) EDIT bbb9

Mostly used Format commands in SQR:

CENTER: Center text

EDIT: uses masks to control the format of the output

FILL : fills the specified length with characters specified.

ON-BREAK: allows you to specify an action/procedure in tabular reports when break occurs in the value of the field.

BEGIN-SETUP includes following commands:

Ask : Prompts the user for substitution variable.

BEGIN-SQL : Begins an SQL paragraph.

CREATE-ARRAY: Creates an array of fields to store and process data

DECLARE-CHART: Defines attributes of a chart.

DECLARE-IMAGE: Defines the type, size and source of an image.

DECLARE-LAYOUT: Defines attributes of a report layout.

DECLARE-PRINTER : Overrides the printer defaults.

DECLARE-PROCEDURE

DECLARE-REPORT: Defines reports and their attributes.

DECLARE-VARIABLE : Allows user to explicitly declare a variable name.

LOAD-LOOKUP : Loads columns into an internal array.

USE : Overrides default database (Sybase and MSSQLServer only)

Types of  PRINT commands? 

Function Puts data on the page at a specified position.  PRINT { any_lit | _var | _col } position

position means:

BOLD causes the string or number to be printed in bold type. EXP:print &name (+1, 20) bold                                       BOX Draws a one-line deep graphical box around the printed data.

EXP print &grand_total (+5, 20) box

CENTER Centers the field on a line.    EXP :print ’Quarterly Sales’ (1) center

EDIT edit_format EDIT causes each field to be edited before it is printed.

EXP print #total (7,55,0) edit $999,999.99 ! $ 12,345.67

What are the different types of  PRINTER commands?

Note the use of the ALTER-PRINTER command. This command changes the font in which the report is printed.   declare-report labels    layout=labels   printer-type=ps   end-declare

The first method is to use the -PRINTER:xx command-line flag,

which specifies the output type for your report. Use the following commands:

DECLARE-PRINTER take effect only when you print your report with the printer you specify with the TYPE argument.

-PRINTER:LP for line-printer output   -PRINTER:PS for PostScript output  -PRINTER:HP for HP LaserJet output  -PRINTER:WP for Windows output   -PRINTER:HT for HTML output.

If you are using the system shell, enter the following on the command line:

sqr test username/password -printer:ps

Common Flags used for SQR’s

-A           : Causes reporting output to be appended to an existing file of the same name.

-C           : Cancel dialog box appears so that user can terminate the program.

-DEBUG: Used in conjunction with #debug command to display additional information when troubleshooting SQR programs.

-E           : Specifies that an error file be created and allows you to specify the filename.

-I            : Location of *.SQC or other include program files.

-F            : Specifies a file or directory where the *.lis file should be created, instead of in   the default location.

-O           : Filename for the log file.

-RS         : Creates a runtime version of your SQR. Creates a file with the *.sqt extention.  PeopleSoft does not use runtime SQR programs.

-M           :SQR configuration file. Used to modify default SQR properties(AllMaxes.max)

-PRINTER : Causes printer type to be specified.

-S             :Displays the status of the all cursors at the end of the run. This is used for   troubleshooting SQR programs.

-Tnn        : Specifies you to run SQR in test mode for the specified(nn) number of pages.

-CB          : Forces communication box.

-XCB       :Do not use communication box. Request for input will be made in Windows  dialog boxes.

SQR provides internal, reserved variables with values maintained & updated by SQR.

#Current-Line : Current physical line on a page

$Current-Date : Current date time on the local machine when sqr is running.

#Current-Column : Current column on page.

#Page-Count : Current page number (can be reset).

#End-File : set to 1 if end of file occurs.

#Return-Status : value returned to operating system when sqr exits.

#Sql-Count : The # of rows affected by an SQL DML statement.

#Sql-Status : Status value from database after each query is compiled/executed. This value is database dependent.

$Sqr-Database: The database type for which SQR was compiled. Valid values are ORACLE, INGRES, DB2, INFORMIX, SQLBASE,SYBASE and ODBC.

$Sql-Error: Text message of error returned from DBS.

$Sqr-Locale : The name of the current locale being used.

#Sqr-pid: The process ID of the current SQR process.

$sqr-platform: The hardware/operating system type for which SQR was compiled. Valid values are WINDOWS, VM,MVS,WINDOWS-NT, UNIX, VMS.

$sqr-program: The name of SQR program file.

$sqr-ver: The text string shown with -ID flag.

Functions (Most commonly used)

Isnull- Returns a value of 1 if source_val is null, otherwise returns a value of 0.

Instr- Returns the numeric position of sub_value in source_value or 0 if not found.

Length- Returns the number or characters in source_value.

Lpad- Pads the source_value on the left to a length of length_value using pad_value and returns the result.

Ltrim- Trims characters in source_value from the left until a character is not in set_value and returns the result.

Nvl- Returns y_value if the x_value is null, otherwise returns x_value.

Example: let $city = nval(&city, ‘-- not city -‘)

Round(#avg_length,0)

Substr- Extracts the specified portion of source_value.

To_char- , To_Number,

String-This creates a string, Opposite of this is Unstring

Extract-Copies a portion of a string into string variables generally used in file manipulations. Example: EXTRACT $area_code from &home_phone 0 3

Print is most commonly used stmt. Show and Display are also used

Evaluate

Example: EVALUATE<variable>

WHEN = <value>    WHEN = <value>  WHEN-OTHER   <default  SQR command(s)>

END-EVALUATE

What is the difference between sqr and sqc?

                              SQR                                                                                                                                       SQC

1. This is nothing but the Actual  Source program.                     1. This is like a function in SQR.

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  another SQR or SQC..           4. We can call one SQC from another SQC or SQR program

What is sqc?  SQC’s are nothing but some functions in SQR.here, we have reusable code, by including those SQC’s and calling those functions in our SQR program we can get the functionality of that function.

What is SQC, List out some of them?

SQC is used as Functional Library mainly used to re-use code and get some predefined functionalities used for our SQR programs.        Some of the Important SQC are

STDAPI.SQC, SETENV.SQC ,RESET.SQC, SETUPxx.SQC, NUMBER.SQC, STRING.SQC, DATETIME.SQC etc..

What is the purpose of SQCs?

We can define the function in sqc's ,then call the sqc's in sqr PROGs like c and c++ header files.   Structure query copy book is library used in SQR PROGs as              #include setenv.sqc

#Include 'datetime.sqc'  !Routines for date and time formatting

#Include 'curdttim.sqc'  !Get-Current-DateTime procedure  #Include 'number.sqc'    !Routines to format numbers

3. What are the different type’s sqc’s?

setenv.sqc,   datetime.sqc,    number.sqc,   sysaudit.sqc,   dddaudit.sqc,     sysquery.sqc,   sysrecord.sqc,    syssql.sqc,    systree.sqc,    sysversn.sqc,    sysxlatt.sqc,    reset.sqc,     sysae.sqc,   sysfield.sqc,  sysmenu.sqc,   syspage.sqc,    dddtable.sqc, dddviews.sqc, dddindex.sqc,  curdttim.sqc

What procedures are defined in PRCSAPI.SQC & PRCSDEF.SQC?

PRCSAPI.SQC: Get-Run-Control-Parms(), Get-Process-Language(), Update-Prcs-Run-Status().

PRCSDEF.SQC: Define-Prcs-Vars() This procedure will define and initialize the various process scheduler variables.

STDAPI.SQC is mainly used to make our SQR program API aware.ie; we can see the status of that SQR program in process monitor. This is a Process Scheduler Interface sqc which is used to initiate and terminate some field values such as Process_instance & Run_cntl_id

SETENV.SQC?

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

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.

Standard SQCs

1        Askftd.sqc - This asks from/thru date for reporting of heading

2       Askaod.sqc - Procedure to call is Ask-As-of-Date

3        CurDtTim.sqc - Gets the Current Date Time from System

4        DateTime.sqc - Convert date from system format calling InitDate-Time procedure to set format.

5        DateMath.sqc - Converts Native Date format to YYYY-MM-DD.

6        Eoj.sqc - determines End of job for an SQR report.

7        FSHdg01.sqc,FSHdg04.sqc - for Standard Headers for PS Financials.

8        Number.sqc     - Provides Number related Functions

9        Opsys.sqc - Operating system settings.

10        PrcsApi.Sqc - Used in Process Scheduler SQR Programs, to get Run control parameters

11       RDBMS.sqc - Database Settings. These files uses #DEFINE command to set the variables.

12        ReadXlat.sqc - Read the Translate Table for the desired values (Very Important)

13       Reset.sqc - Used for Footing Section that prints ‘End-of-Report’.

14       SetEnv.sqc - Sets Environment for Country, Printer-Paper, Language, Date,  Platform specific parameters, It also contains Opsys.sqc, RDBMS.sqc

        Setup01.sqc - This is generally called from Begin-Setup section for Portrait Size

        Setup02.sqc - This is generally called from Begin-Setup section for Landscape Size

        STDHDGTR.sqc  - Performs Language Conversion

        STDHDG01.sqc - Provides Standard heading for language conversion

        StdApi.sqc - Gets Std API calls

        Trancntrl.sqc - Purpose is to COMMIT, ROLLBACK etc. across DB platforms

        Convertcurrency.sqc

        AllMaxes.max - It is used for dynamic memory allocations before the execution of SQR.

What is the SQC used to read data from translate table? 

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

How will you display an IMAGE in SQR?   

Using ‘DECLARE-IMAGE , END-DECLARE’ command.

What is a Syntax for using SQC's in SQR?                     

#include 'setenv.sqc'.

Which SQC file is used for From Date and To Date.      

AskEfdt.sqc

Which SQC tells you that some Job is completed?        

Eoj.sqc tells you about that.

Which SQC is responsible for system memory allocations?  

AllMaxes.max

How to Avoid double declaration of an SQC file?

To check this writes all procedure of an SQC in the following:

#Ifdef  <constant>        #define <constant>    <write all procedures of an sqc file>  #endif

What SQC is found in every PeopleSoft SQR program? 

SetEnv.sqc

What SQC files are commonly included in almost all PeopleSoft SQR programs?

CurDtTim.sqc, Number.sqc and Reset.sqc.

Which SQC file should be modified to include your standard company heading ?

XxHDG01.sqc where xx is for either HR for Human Resource or FN for Financials.

What are the most common uses of SQC files ?

a) Setup Section   b) Standard Headings   Footing Section   User prompts

Get related data   Process Scheduler processing

How can I suppress display of the SQR banner line when running SQR?  Use the -XB flag.

STDAPI  :              Process Scheduler Interface

DESCRIPTION:      It can be used in other SQRs by performing a  "do STDAPI-INIT"  "do STDAPI-TERM"

#Include 'stdvar.sqc'       !Updates process scheduler status

STDAPI: Standard Variables

begin-procedure Define-Standard-Vars   let $Payroll_NID_Type = 'PR'   end-procedure

#Include 'prcsdef.sqc'      !Update Process Request variable declare

#Include 'prcsapi.sqc'       !Update Process API

#Include 'eoj.sqc'              !Updates process scheduler status

PrcsDef :        Procedure to define the Process Scheduler variables.

DESCRIPTION:  It can be used in other SQRs by performing a "do  Define-Prcs-Vars"

PrcsAPI :   Procedure to update the Process Scheduler request record  and get the run control parameters.

DESCRIPTION: It can be used in other SQRs by performing a

"do Get-Run-Control-arms"       "do Update-Prcs-Run-Status"

(We need to indicate  that we're in Update-Prcs-Run-Status because if there is an error that goes to SQL-Error (sqlerr.sqc), that  will try to call back here.  If the error came from here, though  it would be an infinite loop in all likelihood.  So, we set the   indication to prevent the call back here.)

SQLERR :          SQL Error Handling Procedure

This procedure is typicallly called via the ON-ERROR  condition of BEGIN-SQL and BEGIN-SELECT.

"do Update-Process-Status"    "do Get-PrcsRqst-Info"       "Get-Job-Instance"

“do  Get-Last-Message-Seq”    “do  Process-Message-Log”  “do Check-Message-Parms”

“do  Parse-Message-Parms”     “do  Insert-Message-Log”       “do  Insert-Message-Log-Parm”

“do  Set-Client-Info”                      “do  MS-Set-Client-Info”

EOJ          :              Perform end of job processing if successful status        #include "tranctrl.sqc"

DESCRIPTION:   It can be used in other SQRs by performing a "do Successful-EOJ"

SETENV :       #include 'rdbms.sqc'---  (Database Environment)           #define ORACLE

#include 'opsys.sqc'----(Operating System Environment)

#define AXP        #define MVS #define NT    #define OS400      #define UNIX  #define VAX    #define VMS

!BasePeopleTools Version                    !Country                 !Printer

!Process Schedulder OUTDESTTYPE (ODT)    !Process Schedulder OUTDESTFORMAT (ODF)

! Print Specific Language                 !Paper Size           !Language     ! File prefixes and suffix

#ifdef NT

#define FILEPREFIX C:\TEMP\

#define FILESUFFIX

#end-if

!

#ifdef MVS    #define FILEPREFIX   #define FILESUFFIX   #define EBCDIC

#end-if

!Return Status

#define GOOD-OS-STATUS 0

!Global Date Defines

#define DEFMDY  'MDY'    #define DEFDMY  'DMY'    #define DEFYMD  'YMD

#define DEFDATE 'DATE'  #define DEFCMP  'CMP'    #define DEFROM  'ROM'    #define DEFKAN  'KA   ! PTRPTYear - Used in determining what format to display the year in. This does not affect the format used to talk to DB)

! Meta SQL (For Use in SQL Statements Only)              !  Standard INFORMIX parameters

! Meta SQL (For Use in SQL Statements Only)

!

#define PTConCat      ||     #define PTTRUNCATE    DELETE FROM    #define PTSubStr      substr

!  Standard MICROSOFT parameters

! Meta SQL (For Use in SQL Statements Only)

!

#define PTConCat      +      #define PTTRUNCATE    TRUNCATE TABLE   #define PTSubStr      substring

!  Standard ORACLE parameters

! Meta SQL (For Use in SQL Statements Only)

!

#define PTConCat      ||   #define PTTRUNCATE    TRUNCATE TABLE   #define PTSubStr      substr

TRANCTRL :  Common Transaction Control Procedures

DESCRIPTION    :    The purpose of this SQC is to support COMMIT, ROLLBACK, etc., across platforms.                     For example, it can be used in other SQRs by  performing a "do Commit-Transaction" rather than a "commit".

How can you send a mail to person?

If this is AE, The command used to send email is..  If this is SQR, the command used to send email is .. ..   For this to happen, we have to have mail server set in Unix box.

For WF, I did set up the WF in such away that is send email to a person or group by settings the properties in a business process, Activity and Event where in I gave the email address of the person or used a Role Query.Iused PeopleCode at component level or Record level to Trigger the WF. I know how to create Role queries,develop Business process,set WF or Worklist. Using activity ,event and

mail setting the properties and connected by routing, I write  a WF PeopoleCode using TriggerBusinessEvent function I can send a mail.

Can we Change the login id of other DB using SQR?

Using CONNECT Function Logs off the database and logs on under a new user name & password.

Syn:CONNECT { txt_lit | _var | _col }[ ON-ERROR= procedure [ ( arg1 [, argi ] .. )] ]

Arguments

txt_lit | _var | _col – Specifies a user name and password for the logon.

ON-ERROR – Specifies a procedure to be executed if the logon fails. If no ON-ERROR procedure is specified and the logon fails, SQR halts with an error message.

You can optionally specify arguments to be passed to the ON-ERROR procedure.

Arguments can be any variable, column, or literal.

Description: The new user name and password can be stored in a string variable, column, or literal.

After each CONNECT, the reserved variable $username is set to the new user name.

All database cursors or logons are closed before the CONNECT occurs. You should not Issue a CONNECT within a SELECT or an SQL paragraph while a query is actively Fetching or manipulating data from the database. CONNECT SQR 4.0

Examples:  Connect $new-user on-error=bad-logon($new_user)

Connect ’sqr/test’, À Warning the username/password is not encrypted, so beware of security issues.

The SETUP section, if present, is typically placed at the top of the PROG before the PROG section. It begins with BEGIN-SETUP and

The following commands can be issued in the SETUP section. If used, they r  processed at compile time, before the PROG begins executing.   Command Comments

ALTER-LOCALE Can also appear in a procedure,  ASK Allowed only in SETUP section.

BEGIN-SQL Can also appear in a procedure. Executed when a run-time file (.SQT) is loaded.

CREATE-ARRAY Can also appear in a procedure.

DECLARE-CHART   DECLARE-IMAGE   DECLARE-LAYOUT  DECLARE-PRINTER

DECLARE-PROCEDURE   DECLARE-REPORT

DECLARE-VARIABLE Can also appear in a local procedure.  LOAD-LOOKUP Can also appear in a procedure.

USE SYBASE only.

In SQR functions parameters are passed by Reference or value?

Objects are passed by reference and variable assigned values are passed by values.

19. What Are The Application Libraries?  In AE this is the one type Prog, like header files in sqr , c  and c++ ,These apllication Libraries are with out having MAIN section.

4. How many types you Run SQR’s?       1. Using SQRW (in TwoTier)   2.Using Process scheduler                 3.In UNIX, you can run the SQR Using Shell Scripts.

17. How does u represent variables in sqr?  Variables - $     Numeric - #        Field Values - &

How you can declare Dynamic variables in SQR? Dynamic variables by enclosing them in square brackets.   For example,   [$col1] ,[$col2]  : At run time,Dynamic variable should be on [ ]

You can also use Dynamic SQL in some situations to simplify a SQL statement and gain performance.

Dynamic SQL enables you to check the value of $state and create the simpler condition:

if $state = 'CA'       let $datecol = 'order_date'    else   let $datecol = 'ship_date'   end-if

begin-select     order_num    from orders, customers

Where order.customer_num = customers.customer_num and [$datecol] > $start_date      end-select

The substitution variable [$datecol] substitutes the name of the column to be compared with $state_date. The SELECT is simpler and no longer uses an OR. In most cases, this use of dynamic SQL improves performance.

16. Can we create table-using sqr? Yes, using sqr begin-sql & end-sql section only in set-up section.

In procedure section only dml commands can be given.

12. How you can Update values by using SQR?

In beging sql write update table name set where coloum name with out set condition total table will be updated…. Write in Begin-select using update commad  end-select

16. About Begin SQL  :Function   Begins an SQL paragraph. This paragraph can reside in a

BEGIN-PROCEDURE, BEGIN-SETUP, or BEGIN-PROG section.   Examples

Begin-Sql   update orders set invoice_num = #next_invoice_num

where order_num = &order_num   end-sql     begin sql

delete orders    where order_num = &order_num;

insert into orders values ($customer_name, #order_num,...)    End-Sql

Can we write Insert and Update in a single Begin SQL section.      May be

What is the use of SkipLine? Further enhance the visual effect of break processing by inserting

one or more lines between groups. To do so, use the SKIPLINES qualifier with ON-BREAK……&

The SKIPLINES=1 argument skips one line between records, then renumbers the current line as line 1.

Can I write a matrix report in SQR? Yes.    Example of a SQR cross-tab (matrix) report

Example:

Begin-Setup    Page-Size 60 133

declare printer        type = postscript      orientation = landscape  line-size = 8  char-size = 6     End-Setup

MULTIPLE REPORTS***************

How many DECLARE-PRINTER commands can a program have?

At the most, one for each printer type (lp, hp, ps) for each report plus one declaration for 'default-lp', one for 'default-ps' and one for‘default-hp'. SQR will produce an error message if you declare two hp printers for the same report (or two ps printers, etc.). SQR will issue an error if you define 'default-ps' twice, etc.

Do I have to have the DECLARE-LAYOUT before a DECLARE-REPORT that uses it?

No, you can have them in any order.

How do I have different reports generate output for different printers in the same program?

U have to place an appropriate USE-PRINTER-TYPE after the corresponding USE-REPORT.

Example:

begin-program       use-report listing     use-printer-type ps    use-report labels    use-printer-type hp

use-report form_letter     use-printer-type lp     do main     end-program

What's the effect of the -PRINTER command line flag in a multiple report program?

It overrides the printer type for all the report.

How can unique temporary file names be generated in SQR ?

One technique is to combine the report name (minus the extension) with the system process ID. The SQR pre-defined variables $sqr-report and #sqr-pid are used. The following example will create a temporary file named techtip1234.tmp for program techtip.sqr ran as process ID 1234. The temporary file is deleted at the end of the procedure.

What is Declare-Layout <layoutname> used for?  It is used for defining Page Layout & includes Paper Size, Margins in Begin-Setup. Such as Left-Margin = 1, Top-Margin=1.

What is Master Detail Report?  Report where tables with one to many relationships are involved.                           Project, Tasks, Resources, Efforts. On-Break Level =1 etc.

How do you print a document/letter using SQR? Using Begin-Document, End-Document in a procedure.    Call this procedure before From Clause in Begin-Select to print for each record.

How does SQR calculate the number of rows available for the body of the page?

The rows available to the body of your report are calculated from the rows defined in PAGE-SIZE minus the number of rows reserved by the BEGIN-HEADING and BEGIN-FOOTING sections.

Which SQL command is not allowed in the BEGIN-SQL paragraph?    SELECT

What will happen if you do not include an ON-ERROR=procedure in a BEGIN-SQL if your program encounters an SQL error or warning?  SQR program will abort.

Which table stores the translated static report values?  The Strings Table.

What type of variable does the INPUT command use?  The INPUT command always inputs into a TEXT variable.

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.

What the use of ON-Break in SQR ? Usage: It is used to group set of records for each level & break at each level.       Syntax: ON-Break Level=1 skiplines= 4 After = ProcedureName vcc

What is the BREAK command used for within a WHEN clause of the EVALUATE command ?

The BREAK command ends the EVALUATE at that point.

What is the difference between the parameters for the EXTRACT command versus the LET/SUBSTR command?     The EXTRACT command uses the offset 0 as the first position. The LET/SUBSTR command uses 1 as the first position.

What is Extract in SQR?   Extract copies a portion of string into a string variable & substring is to extract a string from a string variable.

What must you remember to program into a WHILE loop? u must increment your WHILE loop counter.

What are the restrictions governing where a GOTO label can be placed?   GOTOs and their labels must be in the same Section or Paragraph. Labels must be left justified and GOTO label must end with Colon(:)

How do we Convert Number to String in SQR?

Using To char function or even by assigning. #var is numeric, $var is string.

How do we Concatenate String in SQR?   Using Concat function or || as pipes.

How do you Declare a local procedure in SQR? Using Begin-Procedure <procedurename> local

How do you know in SQR on which cursor position you are residing? : #Cursor-pos

What is STOP QUIET used for in SQR? Stops execution of SQR & come out of it.

How do you refer to a Constant in SQR? A constant is referred in curly braces { }.

How do you put a blank line in Begin-Document End-Document?  Using .b

How do you Enter a line in an SQR?     Print ‘-’ (y,x,100) FILL

How to place a corporate logo in sqr?   Using “Declare Image” command

How do you Place a Date into Heading Section of SQR?

Use Date-Time function in SQR & Page-Number.

How can I reset the page number that is printed with the PAGE-NUMBER command?

: Move the desired value to the #PAGE-COUNT reserved variable. For example: move 1 to #page-count.

How do you accept Date variable as an Input from a user ? Input $Fieldname type-date format ‘dd/mm/yyyy’