SQR Qick Reference

1.       What is the program flow of SQR?

When SQR Program starts executing. There are two stages in the Program Flow.

1) Compile stage

2) Execution Stage

 Compile Stage.

All the Preprocessor directives are compiled (which starts with #include).

Ex: All the SQC are run.

Check for the syntax errors for the conditions.

Ex: if for loop while loop are properly ended with the respective syntax.

Allocates memory structure if you are using the Arrays and load look up .

 Execution stage:

Starts interpreting the code line by line.

Check for the begin -program body.

Begin -heading.

Begin- footer.

2.       What are the compulsory SQC's that should be used in SQR Program?

There are no required SQCs unless procedures (functions) are being used in the main program that requires them. setenv.sqc, prcsapi.sqc and prcsdef.sqc are required if you want to schedule the job using PS process scheduler.

3. What is difference between Load Look up and Array

Load Lookup is essentially an array which acts in the form of a table. Load Lookup is used to reduce the complexity of joins - it populates the values of a certain field depending on the key field specified from a certain table. Then the users can query from the preloaded lookup table instead of joining tables.
While arrays are used to store and retrieve data using the get and put commands

Example for load lookup

load-lookup

name=states

rows=50

table=stateabbrs

key=abbr

return_value=name

where=country='USA'

to retrieve value from the load lookup we use

lookup states $state $name

to declare an array

syntax

create-array  name=custs size=100

   field=name:char

   field=no:number

   field=state:char

   field=zip:char

   field=contacts:char:5

   field=last-contacted:date

example

! declare arrays

create-array name=emps size=1           ! one row needed for this example

   field=Salary:number=35000               ! initialize to 35,000

   field=Comm:number=5000                  ! initialize to 5,000

to retrieve value from the array

get #sal #com FROM emps(0) Salary Comm

Multiple report

declare-layout labels

    paper-size=(10,11)   left-margin=0.33

  end-declare

to use the declared report

use-report labels

Can we have two main functions in an application engine? OR Can we have two sections in application engine with same name?

The answer is YES.

The section name is stored with the combination of the Name, Market, Platform and effective date.

Eg : Main.GBL.0.1900-01-01

From the above eg if we change any of the parameter we can save it with same name (name like Section01)

Possible cases are Section01.GBR.0.19-01-01 and Section01.USA.0.1900-01-01 is a valid case and will not throw any error. Here the difference is in the market.

If all the parameters in the name are same we will get the error like :

The fun in this is we can write a single application engine which can work in different market. From main you have a call section and you want the same application engine to work different for different market. In this case you will call the same section but based on the market the call will be handled to the respective sections. 

The above eg shows a single application engine with 2 Main functions but the markets are different.

Some PeopleCode sample for quick reference

Stand alone rowset

Stand alone rowset is used to create a record structure and traverse using that. We use the &rowset = CreateRowset(Recorc.record_name); to create a standalone rowset. When we create a stand alone rowset the data will not be filled in this. For filling the data we can use Fill property (as shown in below example).

Local rowset &rowset;
&rowset = CreateRowset(Recorc.record_name);
&rowset.Fill(“WHERE EMPLID = :1”, Record.Fieldname);

We can copy a rowset from one to another using the Copyto function

Local Rowset &MYRS1, MYRS2;
Local String &EMPLID;

&MYRS1 = CreateRowset(RECORD.SOMEREC);
&MYRS2 = CreateRowset(RECORD.SOMEREC);

&EMPLID = '8001';

&MYRS1.Fill("where EMPLID = :1", &EMPLID);
&MYRS1.CopyTo(&MYRS2);

To add a child rowset for a created rowset.
• RECORD1
• RECORD2
• RECORD3
To build rowsets with child rowsets, use code like the following:
Local Rowset &rsREC1, &rsREC2, &rsREC3;

&rsREC3 = CreateRowset(Record.RECORD3);
&rsREC2 = CreateRowset(Record.RECORD2, &rsREC3);
&rsREC1 = CreateRowset(Record.RECORD2, &rsREC2);

OR in a single line we can write like

&rsREC3 = CreateRowset(Record.RECORD1, CreateRowset(Record.RECORD2, CreateRowset(Record.RECORD3)));

Component buffer access using PeopleCode

Buffer access is used to access the value for the current active component. When a component is modeled and displayed in the page the records/fields/values are stored in the hierarchical structure in the memory. We can use the component buffer access classes to get these values.

There are four data buffer classes: Rowset, Row, Record, and Field.

These four classes are the foundation for accessing component buffer data.

A field object, which is instantiated from the Field class, is a single instance of data within a record and is based on a field definition.

A record object, which is instantiated from the Record class, is a single instance of a data within a row and is based on a record definition. A record object consists of one to n fields.

A row object, which is instantiated from the Row class, is a single row of data that consists of one to n records of data. A single row in a component scroll area is a row. A row may have one to n child rowsets. For example, a row in a level two scroll area may have n level three child rowsets.

A rowset object is a data structure used to describe hierarchical data. It is made up of a collection of rows. A component scroll area is a rowset. You can also have a level zero rowset.

Getlevel0 is used to access the level0 record or the primary record.

Local Rowset &ROWSET;
&ROWSET = GetLevel0()(1).GetRowset(SCROLL.LEVEL1_REC);
Example:
&Rowset0 = GetLevel0();
&Rowset1 = &Rowset0(1).GetRowset(Scroll.RECORD1);
&Rowset2 = &Rowset1(CurrentRowNumber(1)).GetRowset(Scroll.RECORD2);
&z_ats_sw = &Rowset2(1).RECORD2.FIELD.Value;

OR

GetLevel0()(1).GetRowset(Scroll.dept_tbl).GetRow(getcurrentactiverow(1)).GetRecord(Record.Z_HRS_JO_CMP_WK).GetField(Field.SS_HIST_GRPBOX_LBL)

To get the current active number for a record use

geteffectivenum(effdt)
geteffectiveitemnum(effdt,effseq)

To read data from a file layout

rem***********************************************************;
rem * PeopleCode to Import Data *;
rem **********************************************************;
Local File &FILE1;
Local Rowset &RS1, &RS2;
&FILE1 = GetFile("specify the path here", "r", "a", %FilePath_Absolute);
&FILE1.SetFileLayout(FileLayout.PMD_FILE1);
&RS1 = &FILE1.CreateRowset();
&RS1 = &FILE1.ReadRowset();While &RS1 <> Null;
    rem code
End-While;


Call the CI using Peoplecode


Local ApiObject &oSession, &oPmdComp1;
&oSession = %session;
&oPmdComp1 = &oSession.GetCompIntfc(CompIntfc.PMD_COMP1);
If &oPmdComp1 = Null Then errorHandler(); throw CreateException(0, 0, "GetCompIntfc failed"); End-If;
rem ***** Set the Component Interface Mode *****; &oPmdComp1.InteractiveMode = False; &oPmdComp1.GetHistoryItems = True; &oPmdComp1.EditHistoryItems = False;
rem ***** Set Component Interface Get/Create Keys *****;
&oPmdComp1.EMPLID = [*];
&oPmdComp1.get();

To Schedule a job using PeopleCode

This sample code is used to submit a sqr to the scheduler.

REM Declare your Variables;
Local ProcessRequest &MYRQST;
Local String &MySQR;
&MySQR = "MYSQR"
REM Create My Process Request Object;
&MYRQST = CreateProcessRequest("SQR Process", &MySQR);
REM Set Properties of My Process Request Object;
&MYRQST.RunControlID = "MYRUNCNTRL_ID"
REM Set Properties of My Process Request Object;
&MYRQST.SetOutputOption("Web", "PDF", "", &MySQR);

The above example creates a ProcessRequest object for the MYSQR SQR named &MYRQST.

&MYRQST.Schedule();
If &MYRQST.Status = 0 then
/* Schedule succeeded. */
Else
/* Process (job) not scheduled, do error processing */
End-If;