Returning SQL Result as Boolean

If you return either a true or false string as the result of executing SQL through PeopleCode, it can be stored directly into a PeopleCode boolean variable.

To explain what I mean, here's a simple example. Say you to want check if a user has a role (for example, the PeopleSoft Administrator role). You could create the following SQL definition called HAS_ROLE to give you a true or false result:

SELECT CASE WHEN EXISTS ( 
 SELECT 1 
  FROM PSROLEUSER 
 WHERE ROLEUSER = :1
   AND ROLENAME = :2) THEN 'true' ELSE 'false' END AS HAS_ROLE
  FROM DUAL

This is an Oracle example using case when exists syntax but you can do something similar in other database platforms.

You can execute this in a SQLExec statement and store the result in a boolean variable:

Local boolean &bHasRole;

SQLExec(SQL.HAS_ROLE, "<OPRID>", "<ROLE>", &bHasRole);

Another example is to show or hide a field. You can directly pass the result of the HAS_ROLE SQL to the visible property of a field like this:

SQLExec(SQL.HAS_ROLE, "<OPRID>", "<ROLE>", RECORD.FIELD.Visible);

If the result is true, the field is visible, if it is false, the field is invisible. All in one nifty piece of code. You could do something similar with other on/off properties such as Enabled or DisplayOnly.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License