Code :- Run AE thru CreateProcessRequest in  PeopleCode

Local ProcessRequest &RQST;

&RQST = CreateProcessRequest();
&RQST.ProcessType = "Application Engine";
&RQST.ProcessName = "POLOADFILE";
&RQST.RunControlID = "POLOAD";
&RQST.Schedule();

If &RQST.Status = 0 Then
   WinMessage("AE Successfully Scheduled", 0);
Else
   WinMessage("There is a problem while Scheduling AE", 0);

End-If;

ArrayS in Peoplecode

Local File &FILE1;

Local string &STRING, &ITEM_ID, &ship_id;

Local array of string &Array;

&FILE1 = GetFile(xyz.txt, "R", %FilePath_Absolute);

&Array = CreateArrayRept("", 0);

If &FILE1.IsOpen Then

   While &FILE1.ReadLine(&STRING);

      &Array = Split(&STRING, Char(9));/*** Define array and make sure the Fields separated by tab space  in xyz.txt file***/

      &ITEM_ID = LTrim(RTrim(&Array [1], " "), " ");

      &ship_id = LTrim(RTrim(&Array [2], " "), " ");

                MessageBox(0, "", 0, 0, "The Processed  item id's are  # " | &ITEM_ID );

   End-While;

End-If;

&FILE1.Close();
/***The string &item_id ( includes 1234,4567 values) which can be used for validations or printing ***/

Dynamic SQL in SQR with example:

Assume a scenario; SQR generates employee details report based on either Company or pay Group.

 So SQR code would be.

If $Run_Option = ‘P’

Begin-Select

A.EMPLID (+1, 1)

A.NAME (, +5)

 FROM XYZ A

WHERE A.PAYGROUP IN (‘PG’)

End-Select

Else

Begin-Select

B.EMPLID (+1, 1)

B.NAME (, +5)

 FROM XYZ B

WHERE B.COMPANY IN (‘SAP’)

End-Select

End-If

Above code can be tuned by Create dynamic SQL.

If $Run_Option = ‘P’

   LET $WHERE = 'WHERE A.PAYGROUP IN (' || '''' || 'PG' || '''' || ')'

END-IF

IF $Run_Option = ‘C’

   LET $WHERE = 'WHERE A.COMPANY IN (' || '''' || 'SAP' || '''' || ')'

End-If

Begin-Select

EMPLID (+1, 1)

NAME (, +5)

 FROM XYZ A

[$where]

End-Select

If run option is not P and C then there won’t be any where condition. SQR will not give any error. It runs without the Where condition, so all the rows from XYZ table will be Selected.

EDIT MASK -Show - Display SQR Commands

SQR Commands – Show and Display and EDIT MASK

·   Display command useful to display only one value at a time

Display &STUDID Display &NAME Display &SEM Output: 100 Satish 4

Display &STUDID NO LINE Display &NAME NO LINE Display &SEM Output: 100 Satish 4

·   Show command useful to show more than one value at a time.

                                          

Show &STUDID &NAME &SEM Output: 100 Satish 4

·   We can also use the EDIT Mask with Show and Display.

Display '123456789' xxx-xx-xxxx Output: 123-45-6789

Display 1234567.89 999,999,999.99 Output: 1,234,567.89

Pseudo Code -File Operations - Reading from one file and writing into another file .

Reading from one file and writing into another file Pseudo Code.

Local array of string &FNAMES;

Local string &FILEPATH,&Lin;

Local File &File1,&File2;

&FILENAME2 = "c:\abc.txt";

&FILENAME1 = "d:\xyz.txt";/***Give the right path with file name***/

/***Opening File2 in Append mode and writing data into File2***/

&File2 = GetFile(&FILENAME2, "A", %FilePath_Absolute);

If &File2.IsOpen Then

   &TextPRINT = "Program  ABCEF";

   &File2.WriteLine(&TextPRINT);

End-If;

&File2.Close();

/***Opening File2 in Read mode ***/

&File2 = GetFile(&FILENAME2, "R", %FilePath_Absolute);

/***Check whether File1 Exists***/

If FileExists(&FILENAME1, %FilePath_Absolute) Then        

   &File1 = GetFile(&FILENAME1, "A", %FilePath_Absolute);

   /***read the data from File2 and  writing into File1***/

   If &File1.IsOpen Then

      While &File2.ReadLine(&Lin) 

         &File1.WriteLine(&Lin);

      End-While;

   End-If;

   &File1.Close();

   &File2.Close();

End-If;

*******************

Expected output

File1 d:\xyz will have the Data of File2 (c:\abc.txt)

On Break in SQR with Example

On Break in SQR

A break is a change in the value of a field.

Uses

1. Avoids printing redundant data.

2. Skip lines when the value of the Column is changed.

3. Print a value only if changed.

4. To add spaces in the report.

5. Perform conditional processing on variables that change.

6. Execute a special procedure before or after the break.

7. Print Subtotals.

Sample Programs – Break Logic?

Assume a table ABC is having following data.

1.      SKIPLINES

BEGIN-SELECT

COMPANY (,1) ON-BREAK SKIPLINES = 1

COUNTRY (,+5)

PAY_END_DT (,+5)

EMPLID (,+5)

NAME (,+5)

SALARY (,+5)

 POSITION (+1)

 FROM ABC

END-SELECT

Company will be printed only once and is not repeated.

Skiplines argument provides a line break between each break.

2.      SKIPLINES & LEVEL

BEGIN-SELECT

COMPANY (,1) ON-BREAK SKIPLINES = 2

COUNTRY (,+5) ON-BREAK LEVEL = 2 SKIPLINES = 1

PAY_END_DT (,+5)

EMPLID (,+5)

NAME (,+5)

SALARY (,+5)

 POSITION (+1)

 FROM ABC

END-SELECT

 

Best Practice to take back up Before deleting any data in Table

Before deleting any data in table it always Best Practice to take back up

So here is the Sample SQL 

Creating a Temp Table

CREATE TABLE SYSADM.PS_PO_HDR_TMP AS SELECT * FROM SYSADM.PS_PO_HDR WHERE BUSINESS_UNIT = 'MN300';

Make sure Table is created with Expected rows of data

SELECT * FROM SYSADM.PS_PO_HDR_TMP;

Delete rows from main table to remove data of backup data

DELETE FROM SYSADM.PS_PO_HDR

WHERE (BUSINESS_UNIT,PO_ID) IN  (SELECT BUSINESS_UNIT,PO_ID FROM SYSADM.PS_PO_HDR_TMP);