Skip to main content

Posts

Creating Left Outer Joins in PSQuery

To create a left outer join on a new query:
On the Query Manager search page (Reporting Tools, Query, Query Manager), click the Create New Query link.
On the Records page, search for the first (left) record for the left outer join.
Click the Add Record link on the same row as the record that you want to add.

The Query page appears.

Select the appropriate fields to add to the query.
Navigate to the Records page.
Find the joining record and then click the Join Record link on the same row as that record.
Select Join to get additional fields only (Left outer join). Complete the join.
If the Enable Auto Join preference is selected on the Query Preference page, click the record name to join with.
If the Enable Auto Join preference is not selected on the Query Preference page, click the OK button.
Define the join criteria.
If the Enable Auto Join preference is selected, the Auto Join Criteria page appears.
You can clear the criteria from the query. You can also click the Add Criteria …
Recent posts

Difference between do-while, do-when, do-until and do-select in app engine.

I have sometimes found theDoactions in an Application Engine to be pretty confusing – especially the nature of iterations that happen with each of these actions. I thought it would be informative to share some insights into the working of these actions in an AE program.
Consider the following scenario:
If we have a select statement as below:
Selectemplidfrom PS_JOB where action_dt = sysdate order by emplid.
Suppose executing this query on your DB returns 10 rows. To understand the working of the Do actions, if I embed this query in an AE and print the emplid in a file,how many EMPLIDs will be printed in case of each Do actions?
The Do When in the above diagram can be replaced by Do While and Do Select, while checking for the number of iterations. The Do Actions will contain the SQL select statement as mentioned before and the Peoplecode will write the Emplid value to a File. In case of Do Until, Peoplecode will precede Do Until (as Do Until is the last action).
Coming back to our original q…

Validating Email Address in Peoplecode

We have a number of pages in PeopleSoft, where user/admin is free to enter email addresses. How to validate if a value entered in a PeopleSoft field is a valid email address? In general, how does an email id look like? It will have few characters followed by @ symbol followed by few characters and then a .(dot) symbol followed by few more characters. So, we have to validate the presence of these. For example, if &email_addr is a variable which contains the string entered in email address field and needs to be validated then try the below code: &AT = Find("@", &email_addr); &DOT = Find(".", &email_addr);
If ALL(%AT,&DOT) Then /* Validation Success */ Else /* Validation Fail*/ /* Issue Error message */ End-if; Additionally we could also perform checks to ensure that: the position of @ is not the first character.(dot) is not the last character.@ is not repeated else where in the stringDot is not repeated else where in the stringThis can be done b…

Custom Run Control Page

Creating a Custom Run Control Page

This step is the same regardless of whether you will ultimately use the Run Control page with an SQR or App Engine program.  Do the following in Application Designer:

Creating the Run Control Record

Open Record definition PRCSRUNCNTL and make a copy by using "Save As" and giving the Record a new name (for example, "MY_PRCS_RC").When prompted to save a copy of the PeopleCode associated with PRCSRUNCNTL, choose "Yes".Update the following Record PeopleCode definitions on your new Run Control Record, replacing references to PRCSRUNCNTL with your custom Run Control Record name:OPRID.RowInitRUN_CNTL_ID.SaveEditLANGUAGE_CD.RowInitLANGUAGE_OPTION.FieldChangeAdd any desired additional fields to your custom Run Control Record (for example, "STRM").Build the Record.Creating the Run Control Page Create a new Page definition and save it with a new name (for example, "MY_PRCS_RC").Insert a Subpage onto your page,…

SQLExec function

Description: Use the SQLExec function to execute a SQL command from within a PeopleCode program by passing a SQL command string. The SQL command bypasses the Component Processor and interacts with the database server directly. If you want to delete, insert, or update a single record, use the corresponding PeopleCode record object method.
If you want to delete, insert, or update a series of records, all of the same type, use the CreateSQL or GetSQL functions, then the Execute SQL class method.

Limitation of SQLExec SELECT Statement: SQLExec can only Select a single row of data. If your SQL statement (or your SQL.sqlname statement) retrieves more than one row of data, SQLExec sends only the first row to its output variables. Any subsequent rows are discarded. This means if you want to fetch only a single row, SQLExec can perform better than the other SQL functions, because only a single row is fetched. If you need to SELECT multiple rows of data, use the CreateSQL or GetSQL func…

Access level 1 field from level 0

/*Declaring Variables*/
Local Rowset &rowset;
Local Record &rec;
Local Field &field;

&rowset0=getlevel0(); /*Get level 0 rowset*/
&row=&rowset0.getrow(1);  /*Get row 0 of level 0 rowset*/
&rowset1=&row0.getrowset(scroll.RECORD_NAME1);/*Get rowset from row 0 using the scroll name(can also be the name of the primary record in the scroll)*/
&row1=&rowset1.getrow(1);/*get row1 of level 1 rowset*/
&rec1=&row1.getrecord(record.RECORD_NAME2);  /*get record in the row1*/
&field1=&rec1.FIELD_NAME;/*finallay access the field in the record*/ 

OR------

&rowset1 = getlevel0(1).getrowset(scroll.RECORD_NAME1);
&field = &rowset.getrecord(record.RECORD_NAME).FIELD_NAME;


HAPPY LEARNING :)

Trigger Peoplecode on selection of radio button.

When we are working with radio buttons, there is a problem say associated peoplecode does not trigger when we select radio button. Lets learn how associated peoplecode can be triggered on selecting radio button below.

Create a simple Peoplesoft page having radio buttons.



Now go to page properties and uncheck allow Defferred Processing.