This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

PeopleSoft Query Language (PsoftQL)

This documents the detailed PsoftQL - PeopleSoft Query Language

PsoftQL (PeopleSoft Query Language) is a request structure that is used by SWS to “ask” for data. This is similar in spirit to GraphQL but it has a syntax that is targeted specifically for asking for PeopleSoft data in a minimal format.

1 - Service Operation CHG_SWS_PSOFTQL

Documentation on the CHG_SWS_PSOFTQL web service. This is a more advanced web service that will will give to very limited and trusted integration partners or when prototyping new web services.

The CHG_SWS_PSOFTQL service operation is a web service that allows a privileged and trusted client to “ask” for any number of tables in PeopleSoft using PsoftQL Syntax. It can return structured JSON or XML data that mimics the PeopleSoft data structure. It is similar in spirit to GraphQL but only works inside a PeopleSoft database. The response body structure will vary based on the input request parameters.

This web service puts the responsibility on the integration client to know what they are asking for. The SWS GET Handler uses similar concepts but hides the complexity of the PeopleSoft data and is more secure.

Using the Service

Unlike the standard GET Service where the request syntax is hidden in the configuration table, this web service requires the client to know the PeopleSoft record structure and how to craft a request. The service takes a PsoftQL Syntax request as the body of the HTTP Post.

Let’s look at some examples and it will make more sense. We are using PsoftQL syntax to ask for tables. The request body is JSON or XML. The response body is JSON or XML. The response body structure will vary based on the input request parameters. All tables that are requested must be “whitelisted” to the API user. See the security section below for more details.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic basic ....token....
Content-Type: application/json
Host: your-ib-host.com

{
    "isDebugMode": true,
    "includeFieldTypes": true,
    "includeAllDescriptions": true,
    "includeKeyFieldIndicators": true,
    "records": [
    {
        "recordName": "PSOPRDEFN",
        "includeDescriptionsFor": [],
        "excludeFields": []
    }
    ]
}

Here is the same request but in XML form.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic basic ....token....
Content-Type: application/xml
Host: your-ib-host.com


<?xml version="1.0" encoding="UTF-8" ?>
<request>
    <isDebugMode>false</isDebugMode>
    <rowLimit>20</rowLimit>
    <noEffectiveStatusLogic>false</noEffectiveStatusLogic>
    <noEffectiveDateLogic>false</noEffectiveDateLogic>
    <includeFieldTypes>false</includeFieldTypes>
    <includeAllDescriptions>false</includeAllDescriptions>
    <includeKeyFieldIndicators>false</includeKeyFieldIndicators>
    <includeAllFieldLabels>false</includeAllFieldLabels>
    <effectiveDateOverride></effectiveDateOverride>
    <records>
        <record>
            <recordName>PSOPRDEFN</recordName>
        </record>
    </records>
</request>

Intended Use Cases

  • This web service can replace many other “GET” operations and can provide more flexibility for different clients. This can accommodate custom client tables to be included in standard GET operations by just changing the request body. You should try to use this operation as a GET first. If it cannot accommodate the request then you can develop a custom operation.
  • Pulling out “pick lists” or “prompt tables” values. For example, a list of subjects, locations, etc.
  • Pulling out “XLAT” values
  • Generating “Changes since x hours ago” queries. Many institutions may have large numbers of tables that you only want to pull out changes since the last sync. In this situation, you need some way to query “what has changed in the last hours” and then only pull those objects that have changed. You can use this web service and the “where clause” functionality to craft a query to find these and then potentially use other web services to only pull those objects.

Security Considerations

Data security is an important component of an enterprise. This web service technically can extract any PeopleSoft table or view out of the client system. 99.99% of institutions will not like this and want some sort of restriction on what tables this web service can pull. We have developed a client-owned “Whitelist” table. This is a simple listing of tables that this web service can be allowed to pull from the client system.

There is a PeopleSoft page where this is configured.

  • Menu Name: CHG_TOOLS
  • Component Name: CHG_SWS_REC_WL
  • Record Name: C_SWS_REC_WL

The table also allows you to define a different whitelist for different API users. For example, we might have different API clients using this API that need different sets of data. The API authentication token is tied to a PeopleSoft user (Permission List). Therefore, you define what records (tables) each permission list should be allowed to see.

Here is an example where there are a few tables that are whitelisted. In this case, these are the only tables that can be pulled by this web service.

Letting a Caller Discover Their Own Whitelist

For advanced use cases, it is often helpful for the client to be able to see what they have access to. Add C_SWS_REC_WL itself to the caller’s whitelist and they can query the table to introspect their own permissions.

The whitelist query uses the same PsoftQL syntax as any other record:

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
  "rowLimit": 5,
  "records": [
    { "recordName": "C_SWS_REC_WL" }
  ]
}

Response — captured live from our demo system, authenticated as an OPRID whose permission list is CHG_PSOFT_LENS_API_USER:

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "C_SWS_REC_WL": {
      "objectType": "record",
      "objectName": "C_SWS_REC_WL",
      "fields": [
        { "rowNumber": 1, "RECNAME": "ACAD_GROUP_TBL",  "CLASSID": "CHG_PSOFT_LENS_API_USER", "LASTUPDDTTM": "2026-04-20-18.30.05.000000", "LASTUPDOPRID": "PS" },
        { "rowNumber": 2, "RECNAME": "ACAD_ORG_TBL",    "CLASSID": "CHG_PSOFT_LENS_API_USER", "LASTUPDDTTM": "2026-04-20-17.38.00.000000", "LASTUPDOPRID": "PS" },
        { "rowNumber": 3, "RECNAME": "ACAD_PLAN",       "CLASSID": "CHG_PSOFT_LENS_API_USER", "LASTUPDDTTM": "2026-04-20-03.08.37.000000", "LASTUPDOPRID": "PS" },
        { "rowNumber": 4, "RECNAME": "ACAD_PROG",       "CLASSID": "CHG_PSOFT_LENS_API_USER", "LASTUPDDTTM": "2026-04-01-05.22.22.000000", "LASTUPDOPRID": "PS" },
        { "rowNumber": 5, "RECNAME": "ACCESS_GRP_LANG", "CLASSID": "CHG_PSOFT_LENS_API_USER", "LASTUPDDTTM": "2026-01-22-15.25.47.000000", "LASTUPDOPRID": "PS" }
      ]
    }
  },
  "responseCode": 200,
  "pageNumber": 1,
  "nextPageNumber": 2
}

Three things to notice:

  • The RECNAME column is the whitelisted record name. This is the list the caller can use in PsoftQL records[].recordName.
  • The CLASSID column is the permission list that owns the entry. Note that even though the request did not filter by CLASSID, every returned row carries the caller’s own permission list value. The whitelist query is implicitly filtered to the caller’s permission lists — a caller cannot enumerate another tenant’s whitelist by querying C_SWS_REC_WL.
  • nextPageNumber: 2 confirms there is more data to page through. Standard pageNumber pagination applies to the whitelist exactly as it does to any other record.

You can also filter explicitly with criteriaFields if the caller’s OPRID is granted multiple permission lists and you only want to see one slice:

{
  "rowLimit": 100,
  "records": [
    {
      "recordName": "C_SWS_REC_WL",
      "criteriaFields": [
        { "fieldName": "CLASSID", "fieldValue": "CHG_PSOFT_LENS_API_USER", "operator": "=" }
      ]
    }
  ]
}

Features

  • This web service is JSON or XML-based.
  • The client can request a PeopleSoft record be returned and can include a where clause in various forms.
    • Child and Grandchildren table nesting is supported using specific request syntax.
    • A table in PeopleSoft is called a “record”. That is an object in the PeopleTools code. The field structure of the record will be synced to a database table with the same name as the record with “PS_” prefix for application tables and “PS” for PeopleTools table. All parameters for this web service refer to the record name and not the database table name.
  • All record fields are exported unless you specifically ask for certain table fields to NOT be included. This can be useful on tables that have sensitive data (SNN, Salary) that you may not be using, and you don’t want logged across the infrastructure.
  • The service supports adding human-readable code descriptions to the output. For example, if a CAMPUS code is “SDIEG” a description attribute may be returned that says “San Diego”.
  • The service supports pagination to extract large amounts of data.
  • Effective data logic is automatically handled. If the parent has the EFFDT field then all children requested will use that EFFDT.
  • Records that have the EFF_STATUS field pull only the active value. This happens automatically but the request parameter is highly configurable.

Assumptions & Notes

  • The client is a very trusted service account (PeopleSoft OPRID). This web service is not meant to be called from a web browser by some end user or javascript. This is something that should be called from immutable server code.
  • The base response JSON structure is the same. All data is returned inside a data element. The JSON structure of what is returned nested inside the data element is entirely based on the request as the response data mimics that database/record structure. This will make more sense when you look at the examples.
  • When requesting child and grandchild records, the logic looks at all fields on the parent. If the child table has a key field matching the field name then that value is used to pull data for this child. This is dynamically generated based on the PeopleSoft metadata. This allows you to ask for “children” that are NOT true children in PeopleSoft. For example, you may have a FACILITY_TBL that has a LOCATION code validated by the LOCATION_TBL. You can ask for the LOCATION_TBL as a child, and it will include the row from the LOCATION_TBL that matches the LOCATION entered on the FACILITY_TBL.
    • This service does not validate the parent-child relationships in the input parameters. You can submit bad requests. It is assumed you are working with someone who can tell you what the correct parent-child relationship is of tables. It gives you plenty of room to make a mistake because there is really no place in PeopleSoft to map parent-child relationships.
    • There can be any number of nesting of parent/child/grandchild.
  • Effective dated logic is automatically applied to each record. You can pass in an optional ISO 8601 date (YYYY-MM-DD) like "effectiveDateOverride": "2021-12-25" for a record. This is optional. The current date will be used for all effective dating logic if data is not specified.
    • If you need all historical records, there is an optional parameter to NOT apply effective dated logic.
  • You can only have one root record.
  • Row Limits - If a row limit is not specified, then only the 1st 50 rows are returned and the remaining rows can be retrieved using pagination.
  • Pagination is supported and enforced for large tables.
  • Effective Status
    • This web service only pulls back records where EFF_STATUS = 'A'. Not all records have the EFF_STATUS field. The web service will apply this automatically. It can be turned off if you need values that are inactive.

2 - PsoftQL Syntax 🌟

Detailed PsoftQL syntax documentation. PsoftQL serves as the “language” of SWS

The PeopleSoft Query Language (PsoftQL) is a powerful tool that allows users to extract information from PeopleSoft applications with SWS. PsoftQL is a request syntax used by the SWS to extract data from PeopleSoft. PsoftQL syntax is used when configuring SWS services.

PsoftQL is a succinct language that provides fine-grain control over what data is returned from a PeopleSoft application. It allows users to specify key parameters that determine the exact data that should be returned, making it an indispensable tool for those who seek specific information from their PeopleSoft applications.

By default, PsoftQL has reasonable defaults that accommodate most requests. However, advanced users can manipulate the various knobs and dials available to override the default behavior and access more detailed information. For instance, one can hide fields or join non-standard fields across tables to the query, ensuring that the data returned meets their specific needs.

Understanding base PsoftQL syntax is essential for working with SWS. The syntax is relatively easy to learn and can be used to access any data stored in PeopleSoft applications.

This document will cover every piece of the syntax.

First, we’ll demonstrate a few basic examples using the default behavior of SWS and PsoftQL syntax. This will prove that you don’t need to know every parameter to extract key information with SWS. After covering the details in the documentation, we’ll explore advanced usage later on.

Simple Examples

We will use JSON syntax here but XML syntax is also supported.

One Table

First, ask for the first 3 rows from the PSROLEDEFN table which defines roles in PeopleSoft security.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ..redacted..
Content-Type: application/json
Host: dev.ib.cedarhillsgroup.com
Content-Length: 84

{
  "rowLimit": 3,
  "records": [
    {
      "recordName": "PSROLEDEFN"
    }
  ]
}

The response we get back from the web service is detailed and exports all fields.

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 795
content-type: application/json; encoding=UTF-8
date: Tue, 06 Jun 2023 05:21:32 GMT

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "ACM Administrator",
          "VERSION": 1,
          "ROLETYPE": "U",
          "DESCR": "ACM Administrator",
          "QRYNAME": "",
          "ROLESTATUS": "A",
          "RECNAME": "",
          "FIELDNAME": "",
          "PC_EVENT_TYPE": "",
          "QRYNAME_SEC": "",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "",
          "ALLOWNOTIFY": "Y",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2020-02-19-13.49.14.223597",
          "LASTUPDOPRID": "PPLSOFT"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "ADS Designer",
          "VERSION": 1,
          "ROLETYPE": "U",
          "DESCR": "ADS Designer",
          "QRYNAME": "",
          "ROLESTATUS": "A",
          "RECNAME": "",
          "FIELDNAME": "",
          "PC_EVENT_TYPE": "",
          "QRYNAME_SEC": "",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "Role for Application Dataset Designer",
          "ALLOWNOTIFY": "Y",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2020-02-19-13.49.14.223642",
          "LASTUPDOPRID": "PPLSOFT"
        },
        {
          "rowNumber": 3,
          "ROLENAME": "AG Composer Administrator",
          "VERSION": 1,
          "ROLETYPE": "U",
          "DESCR": "AG Composer Administrator",
          "QRYNAME": "",
          "ROLESTATUS": "A",
          "RECNAME": "",
          "FIELDNAME": "",
          "PC_EVENT_TYPE": "",
          "QRYNAME_SEC": "",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "This role provides access to the setup pages to define, manage, and maintain Activity Guide Composer templates.",
          "ALLOWNOTIFY": "Y",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2019-05-22-09.34.01.102262",
          "LASTUPDOPRID": "PPLSOFT"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-05.21.33.000000",
    "psftTransactionId": "ffb423b7-0429-11ee-9a28-e332b6feab8c",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "apiVersion": "2023-03-14",
  "nextPageNumber": 2
}

Please note that SWS is NOT hard coded in any way with the fields that exist. SWS reads the PeopleTools meta-data to determine what fields exist on the record. So if Oracle changes the fields or you add custom fields those are automatically picked up.

Adding Some Metadata

Let’s go a bit deeper. Let’s say that your external integration partner needs to know the field label and also decode any sort of XLATs or prompt tables that could exist.

Here we just add to new optional parameter that tells the web service to dynamically pull back labels and any descriptions.

  • "includeAllDescriptions": true
  • "includeAllFieldLabels": true
POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted..
Content-Type: application/json
Host: dev.ib.cedarhillsgroup.com
Content-Length: 151

{
  "rowLimit": 3,
  "includeAllDescriptions": true,
  "includeAllFieldLabels": true,
  "records": [
    {
      "recordName": "PSROLEDEFN"
    }
  ]
}

Here is the response.

You will see that there is the following pattern.

  • Field labels are output as {{fieldName}}_defaultLabel
  • Fields with XLATs or prompt tables are exported as {{fieldName}}_description

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 1075
content-type: application/json; encoding=UTF-8
date: Tue, 06 Jun 2023 05:29:49 GMT

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "ACM Administrator",
          "ROLENAME_defaultLabel": "Role Name",
          "VERSION": 1,
          "VERSION_defaultLabel": "Version",
          "ROLETYPE": "U",
          "ROLETYPE_defaultLabel": "Role Type",
          "ROLETYPE_description": "User List",
          "DESCR": "ACM Administrator",
          "DESCR_defaultLabel": "Description",
          "QRYNAME": "",
          "QRYNAME_defaultLabel": "Role-Query Name",
          "ROLESTATUS": "A",
          "ROLESTATUS_defaultLabel": "Role Status",
          "ROLESTATUS_description": "Active",
          "RECNAME": "",
          "RECNAME_defaultLabel": "Record (Table) Name",
          "FIELDNAME": "",
          "FIELDNAME_defaultLabel": "Field Name",
          "PC_EVENT_TYPE": "",
          "PC_EVENT_TYPE_defaultLabel": "PeopleCode Event Name",
          "QRYNAME_SEC": "",
          "QRYNAME_SEC_defaultLabel": "Query Name",
          "PC_FUNCTION_NAME": "",
          "PC_FUNCTION_NAME_defaultLabel": "PeopleCode Function Name",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_PCODE_RULE_ON_defaultLabel": "PeopleCode Rule Enabled",
          "ROLE_QUERY_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON_defaultLabel": "Query Rule Enabled",
          "LDAP_RULE_ON": "N",
          "LDAP_RULE_ON_defaultLabel": "Directory Rule Enabled",
          "DESCRLONG": "",
          "DESCRLONG_defaultLabel": "Description",
          "ALLOWNOTIFY": "Y",
          "ALLOWNOTIFY_defaultLabel": "Allow notification",
          "ALLOWLOOKUP": "Y",
          "ALLOWLOOKUP_defaultLabel": "Allow Recipient Lookup",
          "LASTUPDDTTM": "2020-02-19-13.49.14.223597",
          "LASTUPDDTTM_defaultLabel": "Last Update Date/Time",
          "LASTUPDOPRID": "PPLSOFT",
          "LASTUPDOPRID_defaultLabel": "Last Update User ID"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "ADS Designer",
          "ROLENAME_defaultLabel": "Role Name",
          "VERSION": 1,
          "VERSION_defaultLabel": "Version",
          "ROLETYPE": "U",
          "ROLETYPE_defaultLabel": "Role Type",
          "ROLETYPE_description": "User List",
          "DESCR": "ADS Designer",
          "DESCR_defaultLabel": "Description",
          "QRYNAME": "",
          "QRYNAME_defaultLabel": "Role-Query Name",
          "ROLESTATUS": "A",
          "ROLESTATUS_defaultLabel": "Role Status",
          "ROLESTATUS_description": "Active",
          "RECNAME": "",
          "RECNAME_defaultLabel": "Record (Table) Name",
          "FIELDNAME": "",
          "FIELDNAME_defaultLabel": "Field Name",
          "PC_EVENT_TYPE": "",
          "PC_EVENT_TYPE_defaultLabel": "PeopleCode Event Name",
          "QRYNAME_SEC": "",
          "QRYNAME_SEC_defaultLabel": "Query Name",
          "PC_FUNCTION_NAME": "",
          "PC_FUNCTION_NAME_defaultLabel": "PeopleCode Function Name",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_PCODE_RULE_ON_defaultLabel": "PeopleCode Rule Enabled",
          "ROLE_QUERY_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON_defaultLabel": "Query Rule Enabled",
          "LDAP_RULE_ON": "N",
          "LDAP_RULE_ON_defaultLabel": "Directory Rule Enabled",
          "DESCRLONG": "Role for Application Dataset Designer",
          "DESCRLONG_defaultLabel": "Description",
          "ALLOWNOTIFY": "Y",
          "ALLOWNOTIFY_defaultLabel": "Allow notification",
          "ALLOWLOOKUP": "Y",
          "ALLOWLOOKUP_defaultLabel": "Allow Recipient Lookup",
          "LASTUPDDTTM": "2020-02-19-13.49.14.223642",
          "LASTUPDDTTM_defaultLabel": "Last Update Date/Time",
          "LASTUPDOPRID": "PPLSOFT",
          "LASTUPDOPRID_defaultLabel": "Last Update User ID"
        },
        {
          "rowNumber": 3,
          "ROLENAME": "AG Composer Administrator",
          "ROLENAME_defaultLabel": "Role Name",
          "VERSION": 1,
          "VERSION_defaultLabel": "Version",
          "ROLETYPE": "U",
          "ROLETYPE_defaultLabel": "Role Type",
          "ROLETYPE_description": "User List",
          "DESCR": "AG Composer Administrator",
          "DESCR_defaultLabel": "Description",
          "QRYNAME": "",
          "QRYNAME_defaultLabel": "Role-Query Name",
          "ROLESTATUS": "A",
          "ROLESTATUS_defaultLabel": "Role Status",
          "ROLESTATUS_description": "Active",
          "RECNAME": "",
          "RECNAME_defaultLabel": "Record (Table) Name",
          "FIELDNAME": "",
          "FIELDNAME_defaultLabel": "Field Name",
          "PC_EVENT_TYPE": "",
          "PC_EVENT_TYPE_defaultLabel": "PeopleCode Event Name",
          "QRYNAME_SEC": "",
          "QRYNAME_SEC_defaultLabel": "Query Name",
          "PC_FUNCTION_NAME": "",
          "PC_FUNCTION_NAME_defaultLabel": "PeopleCode Function Name",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_PCODE_RULE_ON_defaultLabel": "PeopleCode Rule Enabled",
          "ROLE_QUERY_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON_defaultLabel": "Query Rule Enabled",
          "LDAP_RULE_ON": "N",
          "LDAP_RULE_ON_defaultLabel": "Directory Rule Enabled",
          "DESCRLONG": "This role provides access to the setup pages to define, manage, and maintain Activity Guide Composer templates.",
          "DESCRLONG_defaultLabel": "Description",
          "ALLOWNOTIFY": "Y",
          "ALLOWNOTIFY_defaultLabel": "Allow notification",
          "ALLOWLOOKUP": "Y",
          "ALLOWLOOKUP_defaultLabel": "Allow Recipient Lookup",
          "LASTUPDDTTM": "2019-05-22-09.34.01.102262",
          "LASTUPDDTTM_defaultLabel": "Last Update Date/Time",
          "LASTUPDOPRID": "PPLSOFT",
          "LASTUPDOPRID_defaultLabel": "Last Update User ID"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-05.29.49.000000",
    "psftTransactionId": "28121750-042b-11ee-9a28-e332b6feab8c",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "apiVersion": "2023-03-14",
  "nextPageNumber": 2
}

Adding Some Criteria

In those examples above, we were just pulling back all the rows from the PSROLEDEFN table in a paginated way and only returning the top 3. Let’s limit our request to roles starting with “SA”% using the criteriaFields and remove some fields from that output that are not important to our integration partner using excludeFields. PsoftQL also has syntax for advanced SQL clauses but the criteriaFields is good for simple use cases. When you configure PsoftQL you can substitute user parameters from the HTTP request instead of hard coding like we are doing here for demo purposes.

We also removed the labels by removing includeAllFieldLabels from the request just to make the output more succinct.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted..
Content-Type: application/json
Host: dev.ib.cedarhillsgroup.com
Content-Length: 394

{
  "rowLimit": 3,
  "includeAllDescriptions": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": ["RECNAME", "FIELDNAME", "PC_EVENT_TYPE", "QRYNAME_SEC", "VERSION", "QRYNAME", "ALLOWNOTIFY"],
      "criteriaFields": [
        {
            "fieldName": "ROLENAME",
            "fieldValue": "SA%",
            "operator": "LIKE"
        }
        ]
    }
  ]
}

Here is the response.

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 686
content-type: application/json; encoding=UTF-8
date: Tue, 06 Jun 2023 05:42:59 GMT

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "SA Administrator",
          "ROLETYPE": "U",
          "ROLETYPE_description": "User List",
          "DESCR": "SA and CR Administrator",
          "ROLESTATUS": "A",
          "ROLESTATUS_description": "Active",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "Student Administration and Contributor Relations administrator.  Do not modify.",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2004-05-11-21.49.57.000000",
          "LASTUPDOPRID": "PPLSOFT"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "SAIP Administrator",
          "ROLETYPE": "U",
          "ROLETYPE_description": "User List",
          "DESCR": "SAIP Administrator",
          "ROLESTATUS": "A",
          "ROLESTATUS_description": "Active",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "Adminster Student Administration Integration Platform",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2007-07-11-16.45.39.000000",
          "LASTUPDOPRID": "PPLSOFT"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-05.42.59.000000",
    "psftTransactionId": "feb52cc9-042c-11ee-9a28-e332b6feab8c",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

Joining Tables - Automatically

Ok, we just hit 2nd gear. Let’s shift into 3rd gear and join in some child tables!

We want to join in a few more tables. They have the following hierarchy

  • PSROLEDEFN - Defines a role
    • PSROLECLASS - Defines Permission lists (CLASSID) assigned to a role.
      • PSCLASSDEFN - The permission list definition.
        • PSAUTHBUSCOMP - The component interface authorizations

We use the PsoftQL syntax to pass in more records but this time we tell it using the parentRecordName what the parent should be and SWS will automatically join the record based on the key fields. There are some more advanced options you can override the join logic if joining “strange” tables that are documented in detail in the syntax documentation. The default behavior works in 90% of the cases.

We will also just ask for one row because you are going to have to scroll.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted..
Content-Type: application/json
Host: dev.ib.cedarhillsgroup.com
Content-Length: 717

{
  "rowLimit": 1,
  "includeAllDescriptions": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": [
        "RECNAME",
        "FIELDNAME",
        "PC_EVENT_TYPE",
        "QRYNAME_SEC",
        "VERSION",
        "QRYNAME",
        "ALLOWNOTIFY"
      ],
      "criteriaFields": [
        {
          "fieldName": "ROLENAME",
          "fieldValue": "SA%",
          "operator": "LIKE"
        }
      ]
    },
      {
      "recordName": "PSROLECLASS",
      "parentRecordName": "PSROLEDEFN"
    },
    {
      "recordName": "PSCLASSDEFN",
      "parentRecordName": "PSROLECLASS"
    },
    {
      "recordName": "PSAUTHBUSCOMP",
      "parentRecordName": "PSCLASSDEFN"
    }
  ]
}

Here is the response. In this case, this role only had one permission list and a bunch of Component Interface permissions (PSAUTHBUSCOMP). I deleted some of the rows so you did not have to scroll so much.

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 2731
content-type: application/json; encoding=UTF-8
date: Tue, 06 Jun 2023 05:56:54 GMT

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "SA Administrator",
          "ROLETYPE": "U",
          "ROLETYPE_description": "User List",
          "DESCR": "SA and CR Administrator",
          "ROLESTATUS": "A",
          "ROLESTATUS_description": "Active",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "Student Administration and Contributor Relations administrator.  Do not modify.",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2004-05-11-21.49.57.000000",
          "LASTUPDOPRID": "PPLSOFT",
          "PSROLECLASS": {
            "objectType": "record",
            "objectName": "PSROLECLASS",
            "fields": [
              {
                "ROLENAME": "SA Administrator",
                "CLASSID": "HCCPCSSA1000",
                "PSCLASSDEFN": {
                  "objectType": "record",
                  "objectName": "PSCLASSDEFN",
                  "fields": [
                    {
                      "CLASSID": "HCCPCSSA1000",
                      "VERSION": 131,
                      "CLASSDEFNDESC": "CS Administration - All Pages",
                      "TIMEOUTMINUTES": 0,
                      "DEFAULTBPM": "",
                      "STARTAPPSERVER": 0,
                      "ALLOWPSWDEMAIL": 0,
                      "LASTUPDDTTM": "2020-12-18-09.13.22.000000",
                      "LASTUPDOPRID": "PPLSOFT",
                      "PSAUTHBUSCOMP": {
                        "objectType": "record",
                        "objectName": "PSAUTHBUSCOMP",
                        "fields": [
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_LIST",
                            "BCNAME_description": "Enrollment List for the Term",
                            "BCMETHOD": "GetEnrlFldXlat",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_LIST",
                            "BCNAME_description": "Enrollment List for the Term",
                            "BCMETHOD": "GetSection",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_REQUEST",
                            "BCNAME_description": "Submit Enrollment Request",
                            "BCMETHOD": "Cancel",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_REQUEST",
                            "BCNAME_description": "Submit Enrollment Request",
                            "BCMETHOD": "Create",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_REQUEST",
                            "BCNAME_description": "Submit Enrollment Request",
                            "BCMETHOD": "Find",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_REQUEST",
                            "BCNAME_description": "Submit Enrollment Request",
                            "BCMETHOD": "Get",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "SCC_CHESSN_AUS_CI",
                            "BCNAME_description": "Chessn Page CI",
                            "BCMETHOD": "Save",
                            "AUTHORIZEDACTIONS": 4
                          }
                        ]
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-05.56.54.000000",
    "psftTransactionId": "f075d5ba-042e-11ee-9a28-e332b6feab8c",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "apiVersion": "2023-03-14",
  "nextPageNumber": 2
}

So what did we learn from the examples above? That SWS PsoftQL can do a lot of stuff for you automatically with a short syntax. We did not show you how effective dating and effective status is also handled automatically. However, those are better covered in some of the detailed examples for each PeopleSoft pillar in the later sections of this chapter.

PsoftQL Syntax

Here is the base JSON syntax example for one field. Most of these parameters are completely optional.


{
  "isDebugMode": false,
  "rowLimit": 1,
  "pageNumber": 3,
  "includeFieldTypes": false,
  "includeAllDescriptions": false,
  "includeKeyFieldIndicators": false,
  "includeAllFieldLabels": false,
  "noEffectiveDateLogic": false,
  "effectiveDateOverride": "1888-01-01",
  "noEffectiveStatusLogic": false,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "includeDescriptionsFor": [
        "ROLESTATUS",
        "ROLETYPE"
      ],
      "excludeFields": [
        "ALLOWNOTIFY",
        "VERSION"
      ],
      "parentRecordName": "",
      "useParentEffectiveDate": true,
      "doNotAutoJoinToParent": false,
      "criteriaFields": [
        {
          "fieldName": "ROLENAME",
          "fieldValue": "A%",
          "operator": "LIKE"
        }
      ]
    }
  ]
}

There is also an XML syntax that has the same parameter names but there are some subtle differences due to how XML and JSON are slightly different, especially around arrays. This is an XML example of a parent-child request.

<?xml version="1.0" encoding="UTF-8" ?>
<request>
    <records>
        <noEffectiveStatusLogic>true</noEffectiveStatusLogic>
        <noEffectiveDateLogic>true</noEffectiveDateLogic>
        <includeFieldTypes>true</includeFieldTypes>
        <includeAllDescriptions>false</includeAllDescriptions>
        <includeKeyFieldIndicators>false</includeKeyFieldIndicators>
        <includeAllFieldLabels>true</includeAllFieldLabels>
        <record>
            <recordName>PSOPRDEFN</recordName>
            <excludeFields fieldName="OPERPSWD" />
            <excludeFields fieldName="PTOPERPSWDV2" />
            <excludeFields fieldName="OPERPSWDSALT" />
            <excludeFields fieldName="ENCRYPTED" />
            <parentRecordName></parentRecordName>
            <includeDescriptionsFor fieldName="LANGUAGE_C" />
            <sqlWhereClause><![CDATA[]]></sqlWhereClause>
            <criteriaFields fieldName="OPRID" fieldValue="AJORDAN" operator="=" />
        </record>
        <record>
            <recordName>PSROLEUSER</recordName>
            <parentRecordName>PSOPRDEFN</parentRecordName>
            <includeDescriptionsFor fieldName="" />
            <excludeFields fieldName="" />
            <sqlWhereClause><![CDATA[]]></sqlWhereClause>
            <joinFields parentField="OPRID" childField="ROLEUSER" />
        </record>
    </records>
    <isDebugMode>true</isDebugMode>
    <rowLimit>30</rowLimit>
</request>

Important Notes

  • PsoftQL is case-sensitive.
  • JSON can be very picky. If you have not worked with JSON before, I would suggest you use online JSON Linters or if you are using VSCODE I would suggest finding a JSON linter plugin.
  • JSON requires some escaping of certain characters and this is especially true if you are passing complex where clauses in sqlWhereClause

PsoftQL Syntax Detail

  • rowLimit - (integer, optional, Override the default 50 row limit.)
  • includeFieldTypes - (boolean, optional, default: false) - Set to true if you want the PeopleSoft field types to come back. This will also output field length data. This should generally only be true during development as it causes extra processing at run time.
  • includeAllDescriptions - (boolean, optional, default: false) Set to true if you want to include all field descriptions to come back. This should generally only be true during development as it causes extra processing at run time.
  • pageNumber - (integer, optional) Request a different page number other than the first page
  • noEffectiveDateLogic - (boolean, default: false) This parameter is optional and only applies to record with EFFDT field. Set this to true if you want to include all historical rows. The default behavior when false is that only the most effective dated row is included.
  • noEffectiveStatusLogic - (boolean, default: false) This parameter is optional and only applies to records with EFF_STATUS. Set this to true if you want to include rows where EFF_STATUS = 'I'.
  • noEffectiveSequenceLogic - (boolean, default: false) Optional. Only applies to records that carry an EFFSEQ field (for example JOB and other HR records that allow multiple effective-dated rows on the same day). When false (the default), the row with the highest EFFSEQ value for the chosen EFFDT is returned. Set to true to return every EFFSEQ row for that date — useful when auditing same-day correction history or building point-in-time snapshots that need to see every intermediate value, not just the final one.
  • effectiveDateOverride - (string, optional) This parameter is optional and only applies to record with EFFDT field. Optional date in YYYY-MM-DD format to override the current date logic used for effective dated records.
  • includeAllFieldLabels (boolean, default: false) Optional boolean to ask that the default field label be included in the output in the form of {fieldName}_label. This will be the default field label at the field level. There are many times in PeopleSoft where the page field label is actually different. So this label will NOT always be the correct label that shows up on user-facing pages. This should generally only be true during development as it causes extra processing at run time.
  • isDebugMode - (boolean, optional, default: false)
  • records (array of objects, at least one object required) This is a required list of records you want from the database. There has to be at least one value with the parentRecordName blank to designate the ‘root parent record’. There can only be one record listed with parentRecordName blank. “,
    • recordName (string, required) The record name that you want to return data from. It must be whitelisted to you.
    • parentRecordName (string, optional) There must be one array with this blank. There can only be one record with this blank. Then any child table you are requesting must have this specified with the recordName you want to be the parent.”
    • useParentEffectiveDate (boolean, optional for child records only, default: false) If set to true this forces the parent effective date to be used instead of letting the logic build an effective dated clause. This should be set to true for any true children records where a PeopleSoft component enforces the child effective date to match the parent. Only a PeopleSoft expert will know this by looking at the data entry component.
    • doNotAutoJoinToParent (boolean, default false) - Only in rare situations do you want to set this to true. If set to true then the code will not look at common key fields between parent and child and automatically join them. The request MUST include an additional key-value pair array to tell the API how to join the parent and child. These are cases where the parent and child do not share a common field or key values.
    • useNonKeyFieldsInJoin (boolean, optional for child records only, default: false) When PsoftQL auto-joins a child to its parent it normally considers only the child’s key fields. Set this to true to let non-key fields participate in the join discovery. This is needed in the rare case where a child record shares a meaningful non-key field with the parent (for example, a denormalized FK that isn’t part of the child’s key list). Most standard PeopleSoft tables join correctly on keys alone, so leave this off unless you have inspected the record and confirmed the join needs a non-key field. If you also want to override the join completely, prefer doNotAutoJoinToParent plus an explicit joinFields array.
    • joinFields (optional array of objects) - This allows you to tell the web service to join on fields between parent and child that are not obvious or where parent and child do NOT share a common field name but are truly related. OPRID = ROLEUSER, SETID = DEPTID_SETID, EMPLID = MANAGER_ID, etc. This is generally NOT required for this web service. If you are joining standard PeopleSoft tables they generally have the same field and the keys match. However, there are times when you need to force a join between fields. In that case, you can use theses. You might also need to set doNotAutoJoinToParent = true if you want complete control over how the table are joined.
      • parentField - The name of the parent field that you want to join to.
      • childField - The name of the child field you want to join to.
    • sqlWhereClause (string, optional) Optional where clause to limit the data. Do NOT include ‘where’ at the front. The record is loaded with an alias of ‘A’.
    • criteriaFields (array, optional) This is another way to pass in structured data that is used to build a where clause to limit the data. See Operator Examples for worked examples of every operator and LIKE wildcard pattern.
      • fieldName (string) Field name to limit. It must exist on the record.
      • fieldValue (string) The value to limit
      • operator (string) SQL Operator to use like equal to, greater than, etc
    • excludeFields (array of string, optional) Optional array of fields that you do not want to include in the output.
    • includeDescriptionsFor (array of string, optional) Optional array of fields that want to include the description for any codes.
    • includeKeyFieldIndicators (boolean, default: false) Optional boolean to ask for a return of fields that are defined as keys on the record. The response will be {fieldName}_isKey: true only if the field is defined as a key. If the field is not defined as a key then this attribute will NOT be present. This should generally only be true during development as it causes extra processing at run time.
    • orderByFields (array of objects, optional) Optional array of objects to specify custom ordering of results. When specified, this overrides the default key-based ordering used for pagination. Each object has the following properties:
      • fieldName (string, required) The field name to order by. Must exist on the record.
      • sortOrder (string, optional) The sort direction - either ASC (ascending) or DESC (descending). Defaults to ASC if not specified.

Field-Level Options — Worked Examples

The examples below cover the per-record options that most callers reach for once they move past the “give me every column” defaults. Each shows a focused request and the relevant slice of the response.

includeDescriptionsFor — translate selected codes only

includeAllDescriptions: true is convenient during development but expensive at run time because the handler probes every coded field on the record. In production, name the few fields whose translations you actually want:

{
  "rowLimit": 2,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "includeDescriptionsFor": ["ROLETYPE", "ROLESTATUS"],
      "excludeFields": [
        "VERSION", "QRYNAME", "RECNAME", "FIELDNAME",
        "PC_EVENT_TYPE", "QRYNAME_SEC", "PC_FUNCTION_NAME",
        "ROLE_PCODE_RULE_ON", "ROLE_QUERY_RULE_ON", "LDAP_RULE_ON",
        "ALLOWNOTIFY", "ALLOWLOOKUP", "DESCRLONG"
      ]
    }
  ]
}

Response — only ROLETYPE_description and ROLESTATUS_description appear; the rest of the coded fields stay un-translated:

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "ACM Administrator",
          "ROLETYPE": "U",
          "ROLETYPE_description": "User List",
          "DESCR": "ACM Administrator",
          "ROLESTATUS": "A",
          "ROLESTATUS_description": "Active",
          "LASTUPDDTTM": "2021-05-19-07.38.09.805472",
          "LASTUPDOPRID": "PPLSOFT"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "nextPageNumber": 2
}

excludeFields — drop sensitive or noisy columns at the source

excludeFields filters before serialization, so the dropped columns never leave PeopleSoft. Always use it for hashed password material on PSOPRDEFN, and for any sensitive payroll/SSN columns on JOB/PERSON tables. See the combined-criteria example on the Operator Examples page for a PSOPRDEFN query that strips OPERPSWD, PTOPERPSWDV2, OPERPSWDSALT, and ENCRYPTED.

effectiveDateOverride — point-in-time queries

Without effectiveDateOverride, PsoftQL filters effective-dated records to the row that is current today. Override it to ask “what was the state of this data as of date X?” — useful for reconciling reports, replaying historical decisions, or matching what a user would have seen in a Classic page on a specific date.

In this example CRSE_ID = '000101' has two effective-dated rows in the demo database: one with EFFDT = 1900-01-01 and one with EFFDT = 2009-01-01.

Default behaviour — returns the 2009 row because today is later:

{
  "rowLimit": 1,
  "records": [
    {
      "recordName": "CRSE_CATALOG",
      "criteriaFields": [
        { "fieldName": "CRSE_ID", "fieldValue": "000101", "operator": "=" }
      ]
    }
  ]
}

{
  "data": {
    "CRSE_CATALOG": {
      "fields": [
        { "rowNumber": 1, "CRSE_ID": "000101", "EFFDT": "2009-01-01", "DESCR": "Chemistry 1" }
      ]
    }
  },
  "responseCode": 200
}

With effectiveDateOverride: "2005-01-01" — returns the 1900 row, which was the row in effect on Jan 1, 2005:

{
  "rowLimit": 1,
  "effectiveDateOverride": "2005-01-01",
  "records": [
    {
      "recordName": "CRSE_CATALOG",
      "criteriaFields": [
        { "fieldName": "CRSE_ID", "fieldValue": "000101", "operator": "=" }
      ]
    }
  ]
}

{
  "data": {
    "CRSE_CATALOG": {
      "fields": [
        { "rowNumber": 1, "CRSE_ID": "000101", "EFFDT": "1900-01-01", "DESCR": "Chemistry 1" }
      ]
    }
  },
  "responseCode": 200
}

effectiveDateOverride is global to the request: every effective-dated record in records uses it. Date format is ISO YYYY-MM-DD. To bypass effective-date filtering entirely (return all historical rows), use noEffectiveDateLogic: true instead.

joinFields — joining records whose field names don’t line up

The auto-join logic finds the common key fields between parent and child. That covers almost every standard PeopleSoft parent/child relationship — for example, PSROLEDEFN.ROLENAME joins cleanly to PSROLECLASS.ROLENAME. The auto-join breaks down when the related fields are spelled differently between parent and child. The classic case is PSOPRDEFN.OPRID (parent) → PSROLEUSER.ROLEUSER (child) — same semantic value, different field name.

Use joinFields to spell the relationship out:

{
  "rowLimit": 1,
  "records": [
    {
      "recordName": "PSOPRDEFN",
      "excludeFields": [
        "OPERPSWD", "PTOPERPSWDV2", "OPERPSWDSALT", "ENCRYPTED",
        "VERSION", "USERIDALIAS", "SYMBOLICID"
      ],
      "criteriaFields": [
        { "fieldName": "OPRID", "fieldValue": "AADAMS", "operator": "=" }
      ]
    },
    {
      "recordName": "PSROLEUSER",
      "parentRecordName": "PSOPRDEFN",
      "joinFields": [
        { "parentField": "OPRID", "childField": "ROLEUSER" }
      ]
    }
  ]
}

The response nests every role assigned to AADAMS under the parent record:

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSOPRDEFN": {
      "objectType": "record",
      "objectName": "PSOPRDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "OPRID": "AADAMS",
          "OPRDEFNDESC": "Andrew Adams",
          "LASTUPDDTTM": "2021-09-08-05.01.17.440969",
          "LASTUPDOPRID": "PPLSOFT",
          "PSROLEUSER": {
            "objectType": "record",
            "objectName": "PSROLEUSER",
            "fields": [
              { "ROLEUSER": "AADAMS", "ROLENAME": "CS - Student",          "DYNAMIC_SW": "N" },
              { "ROLEUSER": "AADAMS", "ROLENAME": "CS - Student Applicant","DYNAMIC_SW": "N" },
              { "ROLEUSER": "AADAMS", "ROLENAME": "EOPP_USER",             "DYNAMIC_SW": "N" }
            ]
          }
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1
}

For cases where the parent/child relationship doesn’t share any common key and PsoftQL cannot discover a join on its own, also set doNotAutoJoinToParent: true on the child so the handler does not attempt its key-matching pass.

Date fields

PeopleSoft was developed to be database-independent. There are some built-in “META-SQL” functions that can be leveraged in the request to handle data parsing.

  • %datein - can be used to get the current system date. At run time this will expand to either the Oracle or SQL Server (or other).
  • %currentdatein can be used to get the current system date time.

There are several other “meta-sql” variables around data math that can be helpful.

Pagination

This web service handles pagination of large amounts of data. This web service does pagination using a dynamic “Order by” clause that is generated at run-time. The “root” record is used and drives the sort order. By default, the web service looks at the root record and uses the defined database keys to automatically generate an “order by” clause based on the keys and the field ordering of the keys on the record definition. These attributes are all accessible as meta-data inside PeopleSoft.

Custom Ordering With orderByFields

You can override the default key-based ordering by specifying the orderByFields parameter on a record. This allows you to control the sort order of your results, which is especially useful when you want to see the most recently updated records first or sort by a specific business field.

{
  "rowLimit": 3,
  "records": [
    {
      "recordName": "PSPROJECTDEFN",
      "sqlWhereClause": "LASTUPDDTTM is not null",
      "orderByFields": [
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}

The response returns the three most recently updated projects first:

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSPROJECTDEFN": {
      "objectType": "record",
      "objectName": "PSPROJECTDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "PROJECTNAME": "CHG_CI_TEST",
          "LASTUPDDTTM": "2026-05-08-23.22.42.036849"
        },
        {
          "rowNumber": 2,
          "PROJECTNAME": "CMALEK_NODE",
          "LASTUPDDTTM": "2026-04-23-02.15.14.806879"
        },
        {
          "rowNumber": 3,
          "PROJECTNAME": "CHG_TOKEN_GEN_POC",
          "LASTUPDDTTM": "2026-04-22-16.53.29.852062"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "nextPageNumber": 2
}

Flip the same request to "sortOrder": "ASC" and the first three rows become the oldest projects in the database — useful proof that orderByFields is honored end-to-end and that pagination follows the custom order, not the record’s key order.

You can specify multiple fields to create a compound sort order:

{
  "rowLimit": 10,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "orderByFields": [
        {
          "fieldName": "ROLESTATUS",
          "sortOrder": "ASC"
        },
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}

Note: When using orderByFields, pagination will use your custom ordering instead of the default key-based ordering.

Next, we will look at a simple pagination example. First, we will ask for pageNumber 1 and ask for only 2 rows to be returned per page using the rowLimit parameter.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 2,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

The response coming back will look like this:

{
  "data": {
    "ACAD_ORG_TBL": {
      "objectType": "record",
      "objectName": "ACAD_ORG_TBL",
      "fields": [
        {
          "rowNumber": 1,
          "ACAD_ORG": "A&S",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Faculty of Arts and Social Sci",
          "DESCRSHORT": "Arts&SoSci",
          "DESCRFORMAL": "Faculty of Arts and Social Sciences",
          "INSTITUTION": "PSGBR",
          "CAMPUS": "MAIN",
          "MANAGER_ID": "",
          "INSTR_EDIT": "P",
          "CAMPUS_EDIT": "N",
          "SUBJECT_EDIT": "N",
          "COURSE_EDIT": "N"
        },
        {
          "rowNumber": 2,
          "ACAD_ORG": "ACAD DIV",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Academic Division",
          "DESCRSHORT": "Acad Div",
          "DESCRFORMAL": "Academic Division",
          "INSTITUTION": "PSCCS",
          "CAMPUS": "",
          "MANAGER_ID": "",
          "INSTR_EDIT": "P",
          "CAMPUS_EDIT": "N",
          "SUBJECT_EDIT": "N",
          "COURSE_EDIT": "N"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-06.29.55.000000",
    "psftTransactionId": "8d83aaed-0433-11ee-96c1-2302b6923a7a",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "apiVersion": "2023-03-14",
  "nextPageNumber": 2
}

At the bottom you will see a nextPageNumber with a value of “2”. That tells you that there are more pages to pull. To pull the next page you would request the following. The only thing that is changing is the pageNumber parameter.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 2,
  "pageNumber": 2,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

The response contains the next two rows of the underlying result set, and again carries a nextPageNumber until you reach the final page. Note that rowNumber is the absolute row index across the entire result set — it continues counting from page to page rather than restarting at 1 each page.

{
  "data": {
    "ACAD_ORG_TBL": {
      "objectType": "record",
      "objectName": "ACAD_ORG_TBL",
      "fields": [
        {
          "rowNumber": 3,
          "ACAD_ORG": "ACCOUNTING",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Accounting",
          "DESCRSHORT": "Accounting",
          "DESCRFORMAL": "Accounting",
          "INSTITUTION": "GLAKE",
          "CAMPUS": "MAIN",
          "MANAGER_ID": "",
          "INSTR_EDIT": "P",
          "CAMPUS_EDIT": "N",
          "SUBJECT_EDIT": "N",
          "COURSE_EDIT": "N"
        },
        {
          "rowNumber": 4,
          "ACAD_ORG": "ACCT",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Accounting",
          "DESCRSHORT": "Acct",
          "DESCRFORMAL": "Accounting",
          "INSTITUTION": "PSCCS",
          "CAMPUS": "",
          "MANAGER_ID": "",
          "INSTR_EDIT": "P",
          "CAMPUS_EDIT": "N",
          "SUBJECT_EDIT": "N",
          "COURSE_EDIT": "N"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 2,
  "apiVersion": "2023-03-14",
  "nextPageNumber": 3
}

How do you know when you have hit the last page? The response will NOT have a nextPageNumber property. You can also ask for pages beyond the result set like this:

Request:

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 2,
  "pageNumber": 999,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

The response will look similar to this:

{
  "data": {
    "ACAD_ORG_TBL": {
      "objectType": "record",
      "objectName": "ACAD_ORG_TBL",
      "fields": []
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-06.30.58.000000",
    "psftTransactionId": "b28e2298-0433-11ee-96c1-2302b6923a7a",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 9999,
  "apiVersion": "2023-03-14"
}

Response Structure Reference

Every PsoftQL response — successful or not — has the same outer shape. This section names every property you will see, so you know which ones to depend on in client code and which ones are diagnostic-only.

Top-level properties

PropertyTypeWhen presentMeaning
dataobjectAlwaysContainer for the actual record data, keyed by record name. Empty {} on error.
responseCodeintegerAlwaysApplication-level status. 200 = success, 400 = handler-detected validation error. Check this before parsing data. See Error Responses.
errorMessagesstringAlwaysEmpty string on success; a single human-readable error string when responseCode is 400. Reserved name; production code should not depend on the exact wording.
pageNumberintegerStandard queriesThe page you just received. Equals the pageNumber you sent (defaults to 1).
nextPageNumberintegerStandard queries when more pages existThe next page to request. Absence of this property is how you detect “last page” or “zero results.”
apiVersionstringOlder buildsLegacy version stamp. Newer builds emit productVersion instead. Treat as informational; do not gate behavior on it.
productVersionstringNewer buildsReplaces apiVersion. Build/release stamp of the SWS code that served the request.
isAggregatebooleanAggregate queries onlyAlways true when present. Echoes back the request’s isAggregate flag so the caller can tell aggregate responses apart from standard ones at a glance.
totalGroupsintegerAggregate queries onlyCount of rows in data.{RECORD}.results — i.e. the number of distinct GROUP BY buckets returned. With rowLimit set, this is the truncated count.

Inside data.{recordName} (standard queries)

PropertyTypeMeaning
objectTypestringAlways "record" for standard queries; "aggregateResult" for aggregate queries.
objectNamestringThe record name. Echoes what you asked for in recordName.
fieldsarrayThe result rows. Empty array [] means “filter matched zero rows” (not an error).

Each row in fields carries the record’s field values plus a few synthetic properties:

PropertyTypeWhen present
rowNumberintegerStandard queries. Absolute row index across the whole result set — continues counting page-to-page rather than restarting per page.
{FIELD}_descriptionstringWhen includeAllDescriptions: true or when {FIELD} is in includeDescriptionsFor. Decoded XLAT or prompt-table value.
{FIELD}_defaultLabelstringWhen includeAllFieldLabels: true. Default field label from the record definition.
{FIELD}_fieldTypestringWhen includeFieldTypes: true. PeopleSoft data type (e.g. CHAR, NUMBER).
{FIELD}_fieldLengthintegerWhen includeFieldTypes: true. Field length declared on the record.
{FIELD}_isKeybooleanWhen includeKeyFieldIndicators: true. Only emitted for fields that are part of the record’s key list; not emitted when false.
{ChildRecord}objectWhen a child record is configured. Nested { objectType, objectName, fields: [...] } object — see the parent/child examples.

Inside data.{recordName} (aggregate queries)

Aggregate responses use a different shape — see Aggregate Response Structure for the comparison table.

The meta block

The meta block carries diagnostic and environment information. It is present in most responses but its contents vary between builds — older PeopleTools versions emit fewer fields, and the handler may add fields in future releases. Treat anything in meta as informational; never gate business logic on it.

PropertyTypeUse
toolsVerstringPeopleTools version that served the request (e.g. "8.61.03"). Useful when reproducing issues across environments.
dbnamestringDatabase name (e.g. "CS92DEV"). Sanity-check when configurations span multiple databases.
dbTypestring"ORACLE" or "MICROSOFT" — drives the SQL dialect SWS generated (e.g. FETCH FIRST vs TOP).
serverTimeZonestringApp server’s local time zone. The responseDTTM field is in this zone, not UTC.
responseDTTMstringServer timestamp when the response was assembled, format YYYY-MM-DD-HH.MM.SS.NNNNNN.
psftTransactionIdstringPer-call GUID issued by PeopleSoft IB. Use this when filing support tickets — it links the response to logs in PSAPPSRV and the IB monitor.
currentUserstringOPRID of the authenticated caller. Older builds only; newer builds omit it.
serverDirectorystringApp-server install path. Older builds only.
debugMessagesstringEmpty unless the request sent isDebugMode: true; then carries handler trace output.

For SQL-configured endpoints (CHG_SWS_GET) the meta block has additional SQL-specific fields like finalSQL, sqlIDExecuted, rowCount, URLPath, and QueryString — those are documented in the SQL response metadata reference on the configuration page.

Error Responses

PsoftQL surfaces failures in two distinct layers. Knowing which layer rejected a request determines how you should react in your client code.

Layer 1 — PeopleSoft Integration Broker (transport / auth)

The Integration Broker gateway authenticates the basic-auth credentials and authorizes access to the service operation before PsoftQL runs. When the gateway rejects a request you see a normal HTTP error status code (and typically no JSON body). The most common case:

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic <wrong password>
Content-Type: application/json

{ "records": [ { "recordName": "PSROLEDEFN" } ] }

HTTP/1.1 401 Unauthorized

A 401 means PeopleSoft never saw your payload — the OPRID is unknown, the password is wrong, the account is locked, or the OPRID does not have access to the CHG_SWS_PSOFTQL service operation. Fix the credentials or the security setup; the body of your request is irrelevant.

Layer 2 — PsoftQL handler (validation / authorization / SQL)

Once authentication passes, requests reach the PsoftQL handler. Handler errors come back with HTTP 200 — the gateway considers the call successful — but the JSON body carries responseCode: 400 and a human-readable errorMessages string. Always check responseCode and errorMessages, not the HTTP status, when calling PsoftQL.

Example: record not whitelisted

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
  "rowLimit": 5,
  "records": [ { "recordName": "PS_SOME_RANDOM_TABLE_XYZ" } ]
}

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {},
  "responseCode": 400,
  "errorMessages": "Error - At least one record in your request is not whitelisted or is not a real record name.",
  "pageNumber": 1,
  "productVersion": "2026-04-20"
}

The same message is returned whether the record genuinely does not exist or simply hasn’t been whitelisted for your permission list. This is intentional — the service does not reveal which case it is, so a caller cannot use error messages to enumerate the schema.

Example: malformed JSON in the request body

HTTP/1.1 200 OK

{
  "data": {},
  "responseCode": 400,
  "errorMessages": "Error - Input JSON is invalid",
  "pageNumber": 1,
  "productVersion": "2026-04-20"
}

Validate your payload locally before sending it — the PsoftQL Validator page accepts JSON or XML and runs the official schema in-browser.

Example: pagination attempted in aggregate mode

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
  "isAggregate": true,
  "pageNumber": 2,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          { "function": "COUNT(1)", "outputLabel": "role_count" }
        ]
      }
    }
  ]
}

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {},
  "responseCode": 400,
  "errorMessages": "Error - Pagination is not supported in aggregate mode",
  "isAggregate": true,
  "totalGroups": 0,
  "productVersion": "2026-04-20"
}

See Aggregate Queries below for the full list of features that do and do not work alongside isAggregate: true.

Silent behaviours that are not errors

Two responses look like errors but are not. Treat them as successful empty results, not failure modes:

SymptomWhat it means
fields: [] with responseCode: 200Your filter matched zero rows. See the zero-row example in the Operator Examples page (Test 5).
Unknown fieldName in criteriaFields is silently ignoredThe handler currently does not validate that criteriaFields[].fieldName exists on the record. A typo in the field name produces a fully populated, unfiltered result. Always sanity-check that the returned rows actually match the criterion you intended.

Error response cheat sheet

HTTPresponseCodeTypical causeWhat to fix
401(no body)Bad password, locked account, no service-op accessVerify credentials and that the OPRID is granted CHG_SWS_PSOFTQL
200400Record not whitelisted / does not existAdd the record to the whitelist for your permission list
200400Malformed JSONValidate locally with the PsoftQL Validator
200400Aggregate mode + pageNumber > 1Remove pageNumber, or drop isAggregate
200200, empty fieldsNo data matched filterNot an error — handle as zero-result success

Aggregate Queries

PsoftQL supports aggregate queries that allow you to group data and apply aggregate functions like COUNT, SUM, AVG, MIN, and MAX. This is useful for reporting and analytics scenarios where you need summarized data rather than individual rows.

Aggregate Mode

To enable aggregate mode, set isAggregate: true at the top level of your request. When aggregate mode is enabled:

  • Pagination is NOT supported - The pageNumber parameter must be omitted or set to 1
  • Child records are NOT supported - Only a single root record is allowed (no parentRecordName)
  • aggregateConfig is required - You must specify which fields to group by and which aggregate functions to apply

Aggregate Syntax

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "SOME_TABLE",
      "aggregateConfig": {
        "groupByFields": ["FIELD1", "FIELD2"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "total_count"
          },
          {
            "function": "SUM(AMOUNT)",
            "outputLabel": "total_amount"
          }
        ]
      }
    }
  ]
}

Aggregate Configuration

  • isAggregate - (boolean, required for aggregate queries) Set to true at the top level to enable aggregate mode.
  • aggregateConfig - (object, required on each record when isAggregate is true) Configuration for the aggregate query. This is specified at the record level to support future join capabilities.
    • groupByFields - (array of strings, optional) Fields to group results by. If empty or omitted, aggregates are computed across all matching rows.
    • aggregateFields - (array of objects, required) At least one aggregate function definition is required.
      • function - (string, required) The SQL aggregate function with the field or expression embedded (e.g., COUNT(*), COUNT(1), SUM(AMOUNT)).
      • outputLabel - (string, optional) Custom label for the output column. If not provided, a default label will be generated.

Supported Aggregate Functions

Function ExampleDescription
COUNT(*)Count all rows
COUNT(1)Count all rows (alternative syntax)
COUNT(FIELDNAME)Count non-null values in a field
COUNT(DISTINCT FIELDNAME)Count unique values in a field
SUM(FIELDNAME)Sum numeric values (must be a numeric field)
AVG(FIELDNAME)Average numeric values (must be a numeric field)
MIN(FIELDNAME)Minimum value (works with numeric, date, and string fields)
MAX(FIELDNAME)Maximum value (works with numeric, date, and string fields)

Aggregate Example 1: Count by Role Type

Count the number of roles grouped by role type:

{
  "isAggregate": true,
  "includeAllDescriptions": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          }
        ]
      }
    }
  ]
}

Response:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE"],
      "aggregateFields": ["role_count"],
      "results": [
        {
          "ROLETYPE": "U",
          "ROLETYPE_description": "User List",
          "role_count": 245
        },
        {
          "ROLETYPE": "Q",
          "ROLETYPE_description": "Query",
          "role_count": 12
        },
        {
          "ROLETYPE": "P",
          "ROLETYPE_description": "PeopleCode",
          "role_count": 8
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "isAggregate": true,
  "totalGroups": 3,
  "apiVersion": "2024-01-15"
}

Aggregate Example 2: Multiple Aggregates with Criteria

Calculate salary statistics by department for active employees:

{
  "isAggregate": true,
  "rowLimit": 50,
  "records": [
    {
      "recordName": "JOB",
      "criteriaFields": [
        {
          "fieldName": "EMPL_STATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["DEPTID"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "employee_count"
          },
          {
            "function": "SUM(ANNUAL_RT)",
            "outputLabel": "total_salary"
          },
          {
            "function": "AVG(ANNUAL_RT)",
            "outputLabel": "avg_salary"
          },
          {
            "function": "MIN(ANNUAL_RT)",
            "outputLabel": "min_salary"
          },
          {
            "function": "MAX(ANNUAL_RT)",
            "outputLabel": "max_salary"
          }
        ]
      }
    }
  ]
}

Aggregate Example 3: Total Count Without Grouping

Get a simple count of all active roles (no grouping):

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "criteriaFields": [
        {
          "fieldName": "ROLESTATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": [],
        "aggregateFields": [
          {
            "function": "COUNT(*)",
            "outputLabel": "total_active_roles"
          }
        ]
      }
    }
  ]
}

Response:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": [],
      "aggregateFields": ["total_active_roles"],
      "results": [
        {
          "total_active_roles": 265
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "isAggregate": true,
  "totalGroups": 1,
  "apiVersion": "2024-01-15"
}

Aggregate Response Structure

Aggregate responses differ from standard responses:

AspectStandard QueryAggregate Query
objectType"record""aggregateResult"
Data array"fields""results"
Row identifierrowNumber includedNot included
PaginationpageNumber, nextPageNumberNot present
Top-level indicatorNone"isAggregate": true
CountNone"totalGroups" count

Features That Work in Aggregate Mode

  • rowLimit - Limits the number of grouped result rows returned (useful for “top N” queries)
  • criteriaFields / sqlWhereClause - Filters rows before aggregation (equivalent to SQL WHERE clause)
  • noEffectiveDateLogic, effectiveDateOverride - Affects which rows are included in aggregation
  • includeAllDescriptions - Adds descriptions for GROUP BY field values (XLAT/prompt table translations)

Features Not Supported in Aggregate Mode

  • pageNumber > 1 - Pagination is not supported; returns an error
  • Multiple records with parentRecordName - Child records are not supported; returns an error

See Also

Use the PsoftQL Validator to check your PsoftQL syntax interactively.

  • includeFieldTypes, includeAllFieldLabels, includeKeyFieldIndicators - These are ignored in aggregate mode

3 - PsoftQL Validator/Builder

Validate your PsoftQL JSON or XML syntax against the official schema

Paste your PsoftQL JSON or XML below to validate it against the official schema. The format is auto-detected and validation runs automatically as you type.

1
Paste PsoftQL JSON or XML above to validate.

4 - Campus Solutions PsoftQL - Examples

PsoftQL Examples for PeopleSoft Campus Solutions (CS)

Here are some PsoftQL examples for PeopleSoft Campus Solutions. If you have some others you’ve created, please feel free to contact us and let me know, and we can update this document.

You can use these as a starting point. Setup-level examples (Institution, Subject, XLAT, etc.) below include a response sample so you can see the field shape before you wire one up. The parameterized examples (Get Person, Class Schedule, Service Indicators) are intentionally request-only — they depend on tenant-specific {{emplid}}, {{institution}}, and {{strm}} values you’ll substitute at integration time.

Academic Setup

Institution Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "INSTITUTION_TBL"
    }
  ]
}

Academic Organization Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

Sample response — ACAD_ORG_TBL is effective-dated, so by default each row is the currently-effective version per ACAD_ORG. First two rows shown:

{
  "data": {
    "ACAD_ORG_TBL": {
      "objectType": "record",
      "objectName": "ACAD_ORG_TBL",
      "fields": [
        {
          "rowNumber": 1,
          "ACAD_ORG": "A&S",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Faculty of Arts and Social Sci",
          "DESCRSHORT": "Arts&SoSci",
          "DESCRFORMAL": "Faculty of Arts and Social Sciences",
          "INSTITUTION": "PSGBR",
          "CAMPUS": "MAIN"
        },
        {
          "rowNumber": 2,
          "ACAD_ORG": "ACAD DIV",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Academic Division",
          "DESCRSHORT": "Acad Div",
          "INSTITUTION": "PSCCS"
        }
      ]
    }
  },
  "responseCode": 200,
  "nextPageNumber": 2
}

Academic Group Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_GROUP_TBL"
    }
  ]
}

Subject table

This is a setup table in PeopleSoft that defines the list of subjects.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 200,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "SUBJECT_TBL"
    }
  ]
}

Sample response — SUBJECT is the key, ACAD_ORG links each subject to the owning academic org. First two rows:

{
  "data": {
    "SUBJECT_TBL": {
      "objectType": "record",
      "objectName": "SUBJECT_TBL",
      "fields": [
        {
          "rowNumber": 1,
          "INSTITUTION": "GLAKE",
          "SUBJECT": "ACCOUNT",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Accounting",
          "ACAD_ORG": "ACCOUNTING",
          "CIP_CODE": "52.03"
        },
        {
          "rowNumber": 2,
          "INSTITUTION": "GLAKE",
          "SUBJECT": "ARTHIST",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Art & Art History",
          "ACAD_ORG": "ARTHISTORY",
          "CIP_CODE": "50.07"
        }
      ]
    }
  },
  "responseCode": 200,
  "nextPageNumber": 2
}

Instruction Mode

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "INSTRUCT_MODE"
    }
  ]
}

Component Xlat

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "PSXLATITEM",
      "sqlWhereClause": " FIELDNAME = 'SSR_COMPONENT'"
    }
  ]
}

Sample response — PSXLATITEM is the translate-values table; XLATLONGNAME is the human-readable label that decodes a code like CLN → Clinical or DIS → Discussion:

{
  "data": {
    "PSXLATITEM": {
      "objectType": "record",
      "objectName": "PSXLATITEM",
      "fields": [
        {
          "rowNumber": 1,
          "FIELDNAME": "SSR_COMPONENT",
          "FIELDVALUE": "CLN",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "XLATLONGNAME": "Clinical",
          "XLATSHORTNAME": "Clinical"
        },
        {
          "rowNumber": 2,
          "FIELDNAME": "SSR_COMPONENT",
          "FIELDVALUE": "CON",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "XLATLONGNAME": "Continuance",
          "XLATSHORTNAME": "Continuanc"
        },
        {
          "rowNumber": 3,
          "FIELDNAME": "SSR_COMPONENT",
          "FIELDVALUE": "DIS",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "XLATLONGNAME": "Discussion",
          "XLATSHORTNAME": "Discussion"
        }
      ]
    }
  },
  "responseCode": 200,
  "nextPageNumber": 2
}

PeopleSoft Terms

{
    "isDebugMode": false,
    "rowLimit": 9999,
    "pageNumber": 1,
    "noEffectiveDateLogic": false,
    "noEffectiveStatusLogic": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": true,
    "records": [
        {
            "recordName": "TERM_TBL",
            "sqlWhereClause": "INSTITUTION = '{{institution}}'",
            "criteriaFields": [
                {
                    "fieldName": "TERM_BEGIN_DT",
                    "fieldValue": "2020-01-01",
                    "operator": ">="
                }
            ]
        },
        {
            "recordName": "SESSION_TBL",
            "parentRecordName": "TERM_TBL"
        }
    ]
}

Campus Table

This is a setup table in PeopleSoft that defines the list of campuses.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "CAMPUS_TBL"
    }
  ]
}

Acad Career Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_CAR_TBL"
    }
  ]
}

Meeting Pattern Setup

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "STND_MTGPAT_TBL"
    }
  ]
}

Course Catalog

This will return all active courses. It will be paginated as we are limiting it to 10 rows per page (rowLimit)

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "noEffectiveDateLogic": false,
    "noEffectiveStatusLogic": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": true,
    "records": [
        {
            "recordName": "CRSE_CATALOG",
            "sqlWhereClause": ""
        },
        {
            "recordName": "CRSE_OFFER",
            "parentRecordName": "CRSE_CATALOG",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CRSE_ATTRIBUTES",
            "parentRecordName": "CRSE_CATALOG",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CRSE_COMPONENT",
            "parentRecordName": "CRSE_CATALOG",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CRSE_ATTENDANCE",
            "parentRecordName": "CRSE_COMPONENT",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CMPNT_CHRSTC",
            "parentRecordName": "CRSE_COMPONENT",
            "useParentEffectiveDate": true
        }
    ]
}

Course Catalog Change Detection (Incremental Sync)

Integration partners that mirror the PeopleSoft course catalog rarely want the whole catalog on every run. The SCC_ROW_UPD_DTTM column records the per-row last-update timestamp; compare it against the timestamp of the previous successful run to pull only records that changed since then.

{
  "isDebugMode": false,
  "rowLimit": 5,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "CRSE_CATALOG",
      "criteriaFields": [
        { "fieldName": "SCC_ROW_UPD_DTTM", "fieldValue": "1900-01-01", "operator": ">=" }
      ]
    }
  ]
}

Substitute 1900-01-01 for the timestamp of the previous successful run (in YYYY-MM-DD or YYYY-MM-DD-HH.MM.SS.NNNNNN format). On the first run, a low sentinel date returns everything; on subsequent runs, only rows whose SCC_ROW_UPD_DTTM has advanced past the previous watermark.

Grading Basis Setup


{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "GRADESCHEME_TBL"
    },
     {
      "recordName": "GRADE_BASIS_TBL",
      "parentRecordName":"GRADESCHEME_TBL"
    }
    ,
     {
      "recordName": "GRD_BASE_CHOICE",
      "parentRecordName":"GRADE_BASIS_TBL"
    }
    ,
     {
      "recordName": "GRADE_TBL",
      "parentRecordName":"GRADE_BASIS_TBL"
    }
  ]
}

Facilities

This is really from the “bottom” up and we are bringing in related tables that are defined independently.

{
  "isDebugMode": true,
  "includeFieldTypes": false,
  "rowLimit": 1,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "FACILITY_TBL"
    },
     {
      "recordName": "BLDG_TBL",
      "parentRecordName":"FACILITY_TBL",
      "sqlWhereClause": ""
    }
    ,
     {
      "recordName": "LOCATION_TBL",
      "parentRecordName":"FACILITY_TBL"
    }
    ,
     {
      "recordName": "FACILITY_CHRSTC",
      "parentRecordName":"FACILITY_TBL",
      "useParentEffectiveDate": true
    }
    ,
     {
      "recordName": "FACIL_BLACK_OUT",
      "parentRecordName":"FACILITY_TBL",
      "useParentEffectiveDate": true
    }
    ,
     {
      "recordName": "CAMPUS_LOC_TBL",
      "parentRecordName":"FACILITY_TBL",
            "useParentEffectiveDate": true
    }
  ]
}

PeopleSoft Classes

This will pull in a list of classes and we assume that you have two “path” or query string parameters to substitute in the {{institution}} and {{strm}}

{

 "isDebugMode": false,
    "rowLimit": 3,
    "pageNumber": 1,
    "noEffectiveDateLogic": false,
    "noEffectiveStatusLogic": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": true,
    "records": [
        {
            "recordName": "CLASS_TBL",
            "sqlWhereClause": " INSTITUTION = {{institution}} and STRM = {{strm}}"
        },
        {
            "recordName": "CLASS_ATTRIBUTE",
            "parentRecordName": "CLASS_TBL"
        },     
        {
            "recordName": "CLASS_MTG_PAT",
            "parentRecordName": "CLASS_TBL"
        },
        {
            "recordName": "CLASS_INSTR",
            "parentRecordName": "CLASS_MTG_PAT"
        },
        {
            "recordName": "CLASS_NOTES",
            "parentRecordName": "CLASS_TBL"
        },
        {
            "recordName": "CLASS_NOTES_TBL",
            "parentRecordName": "CLASS_NOTES"
        },
         {
            "recordName": "FACILITY_TBL",
            "parentRecordName": "CLASS_MTG_PAT"
        }
    ]
}

Student Data

Get Person Information

{
    "isDebugMode": "{{debugMode}}",
    "rowLimit": 100,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PERSON",
            "sqlWhereClause": "  EMPLID = {{emplid}}"
        },
        {
            "recordName": "NAMES",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "EMAIL_ADDRESSES",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "PERSONAL_PHONE",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "PERS_DATA_EFFDT",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "ADDRESSES",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "PERS_NID",
            "parentRecordName": "PERSON"
        }
    ]
}

Student Program and Plan Setup

When joining effective-dated child records to effective-dated parents, set useParentEffectiveDate: true on the child. This keeps the child’s EFFDT logic aligned with the parent row SWS selected, so you don’t end up with a child EFFDT newer or older than its parent. Without this flag the child record would run its own effective-date computation against SYSDATE, which can diverge from the parent.

{
    "isDebugMode": "{{debugMode}}",
    "rowLimit": 100,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "ACAD_PROG",
            "sqlWhereClause": "  EMPLID = {{emplid}}  "
        },
        {
            "recordName": "ACAD_PLAN",
            "parentRecordName": "ACAD_PROG",
            "useParentEffectiveDate": true
        }
    ]
}

More Complete Student Program and Plan Setup

Ported from a real Modern Campus integration, this walks the full student-career hierarchy: STDNT_CAREER (careers the student is pursuing) → ACAD_PROG (each program under that career) → ACAD_PLAN (majors/minors under each program), plus STDNT_CAR_TERM for per-term standing. Note useParentEffectiveDate: true on ACAD_PLAN so its EFFDT aligns with the ACAD_PROG row above it.

{
    "isDebugMode": "{{debugMode}}",
    "rowLimit": 100,
    "pageNumber": 1,
    "records": [

        {
            "recordName": "STDNT_CAREER",
            "sqlWhereClause": "  EMPLID = {{emplid}}"
        }
        ,
        {
            "recordName": "ACAD_PROG",
            "parentRecordName": "STDNT_CAREER"
        },
        {
            "recordName": "ACAD_PLAN",
            "parentRecordName": "ACAD_PROG",
            "useParentEffectiveDate": true
        }
        ,
        {
            "recordName": "STDNT_CAR_TERM",
            "parentRecordName": "STDNT_CAREER"
        }
    ]
}

Get A Students Service Indicators

{
    "isDebugMode": "{{debugMode}}",
    "includeFieldTypes": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
    {
        "recordName": "SRVC_IND_DATA",
        "sqlWhereClause": "EMPLID={{emplid}} and (SCC_SI_END_DT is null or SCC_SI_END_DT > %currentdatein) and (SCC_SI_END_TERM = '' OR NOT EXISTS ( SELECT 1 FROM PS_TERM_TBL B WHERE A.SCC_SI_END_TERM = B.STRM AND B.TERM_BEGIN_DT < %CURRENTDATEIN))",
        "includeDescriptionsFor": [
        "SRVC_IND_CD"
        ]
    },
    {
        "recordName": "SRVC_IND_CD_TBL",
        "parentRecordName": "SRVC_IND_DATA"
    },
    {
        "recordName": "SERVICE_IMPACT",
        "parentRecordName": "SRVC_IND_CD_TBL"
    },
    {
        "recordName": "SRVC_IN_RSN_TBL",
        "parentRecordName": "SRVC_IND_DATA"
    }
    ]
}

Get a Student’s Schedule for a Term

The student-schedule join is a textbook case for explicit joinFields. STDNT_ENRL and CLASS_TBL share three key fields (CLASS_NBR, STRM, INSTITUTION), and the default auto-join would attempt to match on every common key, which produces the wrong cardinality. Setting doNotAutoJoinToParent: true disables the default behavior so SWS uses only the joinFields you supply.


{
    "isDebugMode": "{{debugMode}}",
    "includeFieldTypes": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
    {
        "recordName": "STDNT_ENRL",
        "sqlWhereClause": "EMPLID= {{emplid}} AND STRM = {{strm}} AND STDNT_ENRL_STATUS = 'E'"
    },
    {
        "recordName": "CLASS_TBL",
        "parentRecordName": "STDNT_ENRL",
        "doNotAutoJoinToParent": true,
        "joinFields": [
        {
            "parentField": "CLASS_NBR",
            "childField": "CLASS_NBR"
        },
        {
            "parentField": "STRM",
            "childField": "STRM"
        },
        {
            "parentField": "INSTITUTION",
            "childField": "INSTITUTION"
        }
        ]
    },
    {
        "recordName": "CLASS_MTG_PAT",
        "parentRecordName": "CLASS_TBL"
    }
    ,
    {
        "recordName": "CAMPUS_MTG",
        "parentRecordName": "CLASS_TBL"
    }

]
}

5 - PsoftQL Operators & Filtering Examples

Examples showing every criteriaFields operator PsoftQL supports, LIKE wildcard patterns, and combined filtering with sqlWhereClause

PsoftQL’s criteriaFields clause lets you filter rows with any of the operators SQL supports for scalar comparisons. The examples below demonstrate each operator, the two wildcards supported by LIKE, how multiple criteria combine with implicit AND, and how criteriaFields can coexist with a free-form sqlWhereClause.

All examples here are drawn verbatim from the integration test suite in doc-internal/test/test-pql-criteria-operators.http. Each test is numbered to match the source file.

Supported Operators

OperatorMeaningNotes
=EqualDefault operator if omitted
<Less thanString or numeric comparison, depends on field type
>Greater than
<=Less than or equal
>=Greater than or equal
<>Not equalEquivalent to !=
!=Not equal (alternate syntax)Equivalent to <>
LIKEPattern match% matches any substring, _ matches exactly one character
INMembershipListed in schema, not currently honored — see note below

Multiple entries in the criteriaFields array are joined with AND. For OR logic, move the condition into sqlWhereClause.

Test 1: Greater Than (>) — PSROLEDEFN by last-update timestamp

Use > when you want rows strictly after a threshold. A common use case is fetching roles that have been edited since your last sync.

Request

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "rowLimit": 5,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "LASTUPDDTTM",
                    "fieldValue": "2020-01-01",
                    "operator": ">"
                }
            ]
        }
    ]
}

Response

Trimmed to two rows for readability. Both rows satisfy LASTUPDDTTM > '2020-01-01'; the presence of nextPageNumber: 2 confirms there is more data to page through.

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "ACM Administrator",
          "VERSION": 1,
          "ROLETYPE": "U",
          "DESCR": "ACM Administrator",
          "ROLESTATUS": "A",
          "DESCRLONG": "",
          "ALLOWNOTIFY": "Y",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2021-05-19-07.38.09.805472",
          "LASTUPDOPRID": "PPLSOFT"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "ADS Designer",
          "VERSION": 1,
          "ROLETYPE": "U",
          "DESCR": "ADS Designer",
          "ROLESTATUS": "A",
          "DESCRLONG": "Role for Application Dataset Designer",
          "ALLOWNOTIFY": "Y",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2021-05-19-07.38.09.805512",
          "LASTUPDOPRID": "PPLSOFT"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.61.03",
    "dbname": "CS92DEV",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "nextPageNumber": 2,
  "productVersion": "2026-04-20"
}

Test 2: Less Than (<) — PSXLATITEM by field name

Use < for rows strictly before a threshold. This example also shows how noEffectiveDateLogic and noEffectiveStatusLogic flags disable the default SWS filters — useful when you want raw rows from tables that would otherwise be filtered to the current effective row or active status.

Request

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "noEffectiveDateLogic": true,
    "noEffectiveStatusLogic": true,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSXLATITEM",
            "criteriaFields": [
                {
                    "fieldName": "FIELDNAME",
                    "fieldValue": "D",
                    "operator": "<"
                }
            ]
        }
    ]
}

Response

Both FIELDNAME values sort alphabetically before "D". Because noEffectiveDateLogic and noEffectiveStatusLogic are both true, you’ll see EFFDT and EFF_STATUS fields in the raw output rather than them being filtered behind the scenes.

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSXLATITEM": {
      "objectType": "record",
      "objectName": "PSXLATITEM",
      "fields": [
        {
          "rowNumber": 1,
          "FIELDNAME": "AAP_PLAN_TYPE",
          "FIELDVALUE": "E",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "XLATLONGNAME": "Establishment Level Plan",
          "XLATSHORTNAME": "Estab",
          "LASTUPDDTTM": "2001-09-14-10.23.47.000000",
          "LASTUPDOPRID": "PPLSOFT",
          "SYNCID": 2
        },
        {
          "rowNumber": 2,
          "FIELDNAME": "AAP_PLAN_TYPE",
          "FIELDVALUE": "J",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "XLATLONGNAME": "Job Group Level Plan",
          "XLATSHORTNAME": "Job Group",
          "LASTUPDDTTM": "2001-09-14-10.23.29.000000",
          "LASTUPDOPRID": "PPLSOFT",
          "SYNCID": 3
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.61.03",
    "dbname": "CS92DEV",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "nextPageNumber": 2,
  "productVersion": "2026-04-20"
}

Tests 3 & 4: Greater-or-Equal (>=) and Less-or-Equal (<=) — CRSE_CATALOG range

Range filters use the inclusive comparison operators. The pair below walks the Course Catalog: first everything from CRSE_ID = '000100' onward, then everything up to and including CRSE_ID = '000010'.

Request — Test 3: >=

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "rowLimit": 5,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "CRSE_CATALOG",
            "criteriaFields": [
                {
                    "fieldName": "CRSE_ID",
                    "fieldValue": "000100",
                    "operator": ">="
                }
            ]
        }
    ]
}

Request — Test 4: <=

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "CRSE_CATALOG",
            "criteriaFields": [
                {
                    "fieldName": "CRSE_ID",
                    "fieldValue": "000010",
                    "operator": "<="
                }
            ]
        }
    ]
}

Response — Test 3 (>=)

The first two rows returned. Both have CRSE_ID >= '000100'. Each row in the real response carries the full record field set; non-essential fields are elided here with ... for readability.

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "CRSE_CATALOG": {
      "objectType": "record",
      "objectName": "CRSE_CATALOG",
      "fields": [
        {
          "rowNumber": 1,
          "CRSE_ID": "000100",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Social Work Practice 2B",
          "SSR_COMPONENT": "LEC",
          "GRADING_BASIS": "GRD",
          "UNITS_MINIMUM": 15,
          "UNITS_MAXIMUM": 15
        },
        {
          "rowNumber": 2,
          "CRSE_ID": "000101",
          "EFFDT": "2009-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Chemistry 1",
          "SSR_COMPONENT": "LEC",
          "GRADING_BASIS": "GRD",
          "UNITS_MINIMUM": 15,
          "UNITS_MAXIMUM": 15
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "nextPageNumber": 2,
  "productVersion": "2026-04-20"
}

Response — Test 4 (<=)

Same record, opposite end. Both rows have CRSE_ID <= '000010':

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "CRSE_CATALOG": {
      "objectType": "record",
      "objectName": "CRSE_CATALOG",
      "fields": [
        {
          "rowNumber": 1,
          "CRSE_ID": "000001",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Basic Musical Techniques"
        },
        {
          "rowNumber": 2,
          "CRSE_ID": "000002",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Musicianship and Materials"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "nextPageNumber": 2,
  "productVersion": "2026-04-20"
}

Tests 5 & 6: Not-Equal (<> and !=) — PSROLEDEFN by role status

PsoftQL accepts both <> and != for inequality. They generate identical SQL — pick whichever reads more naturally for your team.

Request — Test 5: <>

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "ROLESTATUS",
                    "fieldValue": "A",
                    "operator": "<>"
                }
            ]
        }
    ]
}

Request — Test 6: !=

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "ROLESTATUS",
                    "fieldValue": "I",
                    "operator": "!="
                }
            ]
        }
    ]
}

Response — Test 5 (<>)

On the demo database every role is active, so filtering for ROLESTATUS <> 'A' returns zero rows. The response is still a successful 200 — an empty fields array is the normal “no matches” shape, not an error:

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": []
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "productVersion": "2026-04-20"
}

Two things worth noting in zero-row responses:

  • nextPageNumber is absent — the same indicator you use to detect “last page” also signals “no data at all.”
  • errorMessages is still empty. An empty result and an error are distinct outcomes.

Response — Test 6 (!=)

ROLESTATUS != 'I' returns every role whose status is not Inactive — i.e. all active roles. First two rows shown:

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "ACM Administrator",
          "ROLETYPE": "U",
          "DESCR": "ACM Administrator",
          "ROLESTATUS": "A",
          "LASTUPDDTTM": "2021-05-19-07.38.09.805472",
          "LASTUPDOPRID": "PPLSOFT"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "ADS Designer",
          "ROLETYPE": "U",
          "DESCR": "ADS Designer",
          "ROLESTATUS": "A",
          "LASTUPDDTTM": "2021-05-19-07.38.09.805512",
          "LASTUPDOPRID": "PPLSOFT"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "nextPageNumber": 2,
  "productVersion": "2026-04-20"
}

Test 8: Multiple Criteria (AND logic) — PSOPRDEFN

When you supply more than one entry in criteriaFields, PsoftQL joins them with AND. The example below finds users whose OPRID begins with A and whose account is locked, while using excludeFields to strip sensitive password columns from the response.

Request

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": true,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSOPRDEFN",
            "excludeFields": ["OPERPSWD", "PTOPERPSWDV2", "OPERPSWDSALT", "ENCRYPTED"],
            "criteriaFields": [
                {
                    "fieldName": "OPRID",
                    "fieldValue": "A%",
                    "operator": "LIKE"
                },
                {
                    "fieldName": "ACCTLOCK",
                    "fieldValue": "1",
                    "operator": "="
                }
            ]
        }
    ]
}

Response

Every row satisfies both conditions: OPRID starts with A and ACCTLOCK = 1. Note that OPERPSWD, PTOPERPSWDV2, OPERPSWDSALT, and ENCRYPTED are absent from each row — excludeFields filtered them out before serialization, so they were never on the wire:

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSOPRDEFN": {
      "objectType": "record",
      "objectName": "PSOPRDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "OPRID": "AADAMS",
          "OPRDEFNDESC": "Andrew Adams",
          "EMPLID": "PU310",
          "EMAILID": "",
          "OPRCLASS": "HCPPALL",
          "ROWSECCLASS": "HCDPALL",
          "ACCTLOCK": 1,
          "FAILEDLOGINS": 0,
          "LASTPSWDCHANGE": "2024-05-23",
          "LASTUPDDTTM": "2021-09-08-05.01.17.440969",
          "LASTUPDOPRID": "PPLSOFT"
        },
        {
          "rowNumber": 2,
          "OPRID": "ACARR",
          "OPRDEFNDESC": "Alan Carr",
          "EMPLID": "KU0150",
          "EMAILID": "",
          "OPRCLASS": "HCCPCSSA1010",
          "ROWSECCLASS": "HCCPCSSA1010",
          "ACCTLOCK": 1,
          "FAILEDLOGINS": 0,
          "LASTPSWDCHANGE": "2024-05-23",
          "LASTUPDDTTM": "2021-09-08-05.01.17.440969",
          "LASTUPDOPRID": "PPLSOFT"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "nextPageNumber": 2,
  "productVersion": "2026-04-20"
}

Tests 9, 10, 11: LIKE Wildcard Patterns

PsoftQL passes LIKE patterns through to SQL, so the standard wildcards apply:

  • % matches any sequence of zero or more characters
  • _ matches exactly one character

The three examples below demonstrate each placement pattern: trailing, surrounding, and single-character.

Test 9: Leading wildcard (%ADMIN) — roles ending in ADMIN

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "ROLENAME",
                    "fieldValue": "%ADMIN",
                    "operator": "LIKE"
                }
            ]
        }
    ]
}

Test 10: Wildcards on both sides (%USER%) — roles containing USER

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "ROLENAME",
                    "fieldValue": "%USER%",
                    "operator": "LIKE"
                }
            ]
        }
    ]
}

Test 11: Single-character wildcard (_lient) — 6-letter XLATs ending in “lient”

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "noEffectiveDateLogic": true,
    "noEffectiveStatusLogic": true,
    "rowLimit": 20,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSXLATITEM",
            "criteriaFields": [
                {
                    "fieldName": "XLATLONGNAME",
                    "fieldValue": "_lient",
                    "operator": "LIKE"
                }
            ]
        }
    ]
}

Response — Test 9 (%ADMIN)

Every returned ROLENAME ends in ADMIN:

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "CHG_SWS_ADMIN",
          "ROLETYPE": "U",
          "DESCR": "SWS Admin User",
          "ROLESTATUS": "A",
          "DESCRLONG": "Access to setup SWS Configurations\r\n\r\nSee Cedar Hills Group, INC. SWS Documentation.",
          "LASTUPDDTTM": "2024-08-01-01.22.35.757884",
          "LASTUPDOPRID": "PS"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "EOFD_ADMIN",
          "ROLETYPE": "U",
          "DESCR": "Fluid Discussions Admin",
          "ROLESTATUS": "A",
          "LASTUPDDTTM": "2021-05-19-07.38.10.000000",
          "LASTUPDOPRID": "PPLSOFT"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "nextPageNumber": 2,
  "productVersion": "2026-04-20"
}

Response — Test 10 (%USER%)

Every returned ROLENAME contains USER somewhere — leading text, embedded, or trailing:

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "CHG_PSOFT_LENS_API_USER",
          "ROLETYPE": "U",
          "DESCR": "API User",
          "ROLESTATUS": "A",
          "LASTUPDDTTM": "2026-01-18-16.56.00.000000",
          "LASTUPDOPRID": "PS"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "CHG_SWS_USER",
          "ROLETYPE": "U",
          "DESCR": "SWS User",
          "ROLESTATUS": "A",
          "LASTUPDDTTM": "2024-08-01-01.22.35.757884",
          "LASTUPDOPRID": "PS"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "nextPageNumber": 2,
  "productVersion": "2026-04-20"
}

Response — Test 11 (_lient)

The _ matches exactly one character. Here both matches are the literal XLAT value "Client" (_ matched the C):

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSXLATITEM": {
      "objectType": "record",
      "objectName": "PSXLATITEM",
      "fields": [
        {
          "rowNumber": 1,
          "FIELDNAME": "PTSF_NODE_TYPE",
          "FIELDVALUE": "2",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "XLATLONGNAME": "Client",
          "XLATSHORTNAME": "Client"
        },
        {
          "rowNumber": 2,
          "FIELDNAME": "RUNLOCATION",
          "FIELDVALUE": "1",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "I",
          "XLATLONGNAME": "Client",
          "XLATSHORTNAME": "Client"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "productVersion": "2026-04-20"
}

If your database has no 6-letter strings ending in lient, the same request returns "fields": [] and no nextPageNumber — a successful empty result, identical in shape to the zero-row example shown for Test 5 above.

Test 12: Combining criteriaFields with sqlWhereClause — TERM_TBL

You can mix structured criteria with a free-form SQL clause. They are joined with AND. Use sqlWhereClause for expressions that don’t fit the scalar comparison model — subqueries, functions, OR logic — and keep criteriaFields for straightforward column-value checks so the intent is obvious.

Request

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "TERM_TBL",
            "sqlWhereClause": "ACAD_CAREER = 'CNED'",
            "criteriaFields": [
                {
                    "fieldName": "INSTITUTION",
                    "fieldValue": "PSUNV",
                    "operator": "="
                }
            ]
        }
    ]
}

Response

Every row satisfies both INSTITUTION = 'PSUNV' (from criteriaFields) and ACAD_CAREER = 'CNED' (from sqlWhereClause):

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "TERM_TBL": {
      "objectType": "record",
      "objectName": "TERM_TBL",
      "fields": [
        {
          "rowNumber": 1,
          "INSTITUTION": "PSUNV",
          "ACAD_CAREER": "CNED",
          "STRM": "0290",
          "DESCR": "1997 Fall",
          "TERM_BEGIN_DT": "1997-08-27",
          "TERM_END_DT": "1997-12-15",
          "SESSION_CODE": "1",
          "WEEKS_OF_INSTRUCT": 15,
          "TERM_CATEGORY": "R",
          "ACAD_YEAR": "1997"
        },
        {
          "rowNumber": 2,
          "INSTITUTION": "PSUNV",
          "ACAD_CAREER": "CNED",
          "STRM": "0292",
          "DESCR": "1997 Fall Qtr",
          "TERM_BEGIN_DT": "1997-09-01",
          "TERM_END_DT": "1997-11-30",
          "SESSION_CODE": "1",
          "WEEKS_OF_INSTRUCT": 10,
          "TERM_CATEGORY": "R",
          "ACAD_YEAR": "1997"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "pageNumber": 1,
  "nextPageNumber": 2,
  "productVersion": "2026-04-20"
}

The PsoftQL engine builds a single SQL WHERE that ANDs the two conditions together. There is no precedence ambiguity: criteriaFields entries and sqlWhereClause always join with AND.

Where to go next

6 - PeopleTools PsoftQL - Examples

PsoftQL Examples for PeopleSoft PeopleTools Tables

In this section we will show detailed PsoftQL examples of how to extract PeopleTools information using SWS PsoftQL Syntax. Each example includes a sample response so you can see the field shape before deciding which to wire up.

Users and Their Roles

This example shows how to pull PSOPRDEFN and PSROLEUSER information and join them together to get a list of users and their roles. We also exclude some fields that are not needed. Additionally, we include the descriptions for the LANGUAGE_CD and CURRENCY_CD fields.

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "includeAllFieldLabels": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
    {
        "recordName": "PSOPRDEFN",
        "includeDescriptionsFor": ["LANGUAGE_CD", "CURRENCY_CD"],
        "excludeFields": ["VERSION", "EXPENT", "OPERPSWD", "PTOPERPSWDV2", "OPERPSWDSALT", "ENCRYPTED"],
        "criteriaFields": [

        ]
    },
    {
        "recordName": "PSROLEUSER",
        "parentRecordName": "PSOPRDEFN",
        "doNotAutoJoinToParent": true,
        "joinFields": [
            {"parentField": "OPRID", "childField": "ROLEUSER"}
        ],
        "excludeFields": ["ROLEUSER"]
    }

    ]
}

Sample response — one user with their nested role list. LANGUAGE_CD_description appears because LANGUAGE_CD was listed in includeDescriptionsFor; CURRENCY_CD_description would appear too if the field had a non-blank value. The child PSROLEUSER.fields array carries each role assignment for the parent OPRID, joined via OPRID = ROLEUSER:

{
  "data": {
    "PSOPRDEFN": {
      "objectType": "record",
      "objectName": "PSOPRDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "OPRID": "AADAMS",
          "OPRDEFNDESC": "Andrew Adams",
          "LANGUAGE_CD": "ENG",
          "LANGUAGE_CD_description": "English",
          "CURRENCY_CD": "",
          "LASTUPDDTTM": "2021-09-08-05.01.17.440969",
          "LASTUPDOPRID": "PPLSOFT",
          "PSROLEUSER": {
            "objectType": "record",
            "objectName": "PSROLEUSER",
            "fields": [
              { "ROLENAME": "CS - Student",                "DYNAMIC_SW": "N" },
              { "ROLENAME": "CS - Student Applicant",      "DYNAMIC_SW": "N" },
              { "ROLENAME": "EOPP_USER",                   "DYNAMIC_SW": "N" },
              { "ROLENAME": "PeopleSoft User",             "DYNAMIC_SW": "N" },
              { "ROLENAME": "Standard Query Permissions",  "DYNAMIC_SW": "N" }
            ]
          }
        }
      ]
    }
  },
  "responseCode": 200,
  "nextPageNumber": 2
}

PeopleSoft Record Definitions

Export PeopleSoft Record Definitions and their fields. This example shows how to pull PSRECDEFN, PSRECFIELDDB, and PSDBFIELD information and join them together to get a list of record definitions and their fields.


{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "includeAllDescriptions": true,
  "includeKeyFieldIndicators": false,
  "includeAllFieldLabels": false,
  "rowLimit": 2,
  "records": [
    {
      "recordName": "PSRECDEFN",
      "includeDescriptionsFor": []
    },
    {
      "recordName": "PSRECFIELDDB",
      "parentRecordName": "PSRECDEFN"
    },
    {
      "recordName": "PSDBFIELD",
      "parentRecordName": "PSRECFIELDDB"
    }
  ]
}

Sample response (heavily trimmed — one parent record, one child field, one grand-child field). The full response for two records is ~60 KB because every PSRECDEFN explodes into dozens of PSRECFIELDDB rows, each with its own PSDBFIELD child. Filter aggressively with criteriaFields on the parent (RECNAME = 'YOUR_RECORD') when you only need one record’s structure:

{
  "data": {
    "PSRECDEFN": {
      "objectType": "record",
      "objectName": "PSRECDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "RECNAME": "PSROLEDEFN",
          "RECTYPE": 0,
          "PSRECFIELDDB": {
            "objectType": "record",
            "objectName": "PSRECFIELDDB",
            "fields": [
              {
                "RECNAME": "PSROLEDEFN",
                "FIELDNAME": "ALLOWLOOKUP",
                "FIELDNUM": 17,
                "DEFFIELDNAME": "Y",
                "USEEDIT": 8396800,
                "PSDBFIELD": {
                  "objectType": "record",
                  "objectName": "PSDBFIELD",
                  "fields": [
                    {
                      "FIELDNAME": "ALLOWLOOKUP",
                      "FIELDTYPE": 0,
                      "LENGTH": 1,
                      "OBJECTOWNERID": "PPT",
                      "DESCRLONG": "Allow Recipient Lookup"
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "responseCode": 200
}

This three-level nest is a useful pattern for “schema discovery” integrations — building a client-side model of every field on a record, including its underlying type from PSDBFIELD.

Recently Modified Projects

This example shows how to use orderByFields to get the most recently modified App Designer projects. By ordering by LASTUPDDTTM in descending order, you get the most recently updated projects first.

{
  "isDebugMode": false,
  "rowLimit": 10,
  "records": [
    {
      "recordName": "PSPROJECTDEFN",
      "sqlWhereClause": "LASTUPDDTTM is not null",
      "orderByFields": [
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}

Sample response (top 3) — note the timestamps step backward as you walk the array, confirming the DESC order is applied. This pattern is the standard “audit/recent-changes” query for any record that carries LASTUPDDTTM:

{
  "data": {
    "PSPROJECTDEFN": {
      "objectType": "record",
      "objectName": "PSPROJECTDEFN",
      "fields": [
        { "rowNumber": 1, "PROJECTNAME": "CHG_CI_TEST",       "LASTUPDDTTM": "2026-05-08-23.22.42.036849" },
        { "rowNumber": 2, "PROJECTNAME": "CMALEK_NODE",       "LASTUPDDTTM": "2026-04-23-02.15.14.806879" },
        { "rowNumber": 3, "PROJECTNAME": "CHG_TOKEN_GEN_POC", "LASTUPDDTTM": "2026-04-22-16.53.29.852062" }
      ]
    }
  },
  "responseCode": 200,
  "nextPageNumber": 2
}

Recently Modified Roles

Get the most recently modified PeopleSoft roles, sorted by last update date descending. This is useful for auditing recent security changes.

{
  "isDebugMode": false,
  "rowLimit": 20,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": ["RECNAME", "FIELDNAME", "PC_EVENT_TYPE", "QRYNAME_SEC", "VERSION", "QRYNAME"],
      "orderByFields": [
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}

Roles Sorted by Status and Last Update

This example demonstrates compound sorting with multiple orderByFields. Roles are sorted first by status (Active roles first alphabetically), then by last update date with most recent first.

{
  "isDebugMode": false,
  "rowLimit": 50,
  "includeAllDescriptions": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": ["RECNAME", "FIELDNAME", "PC_EVENT_TYPE", "QRYNAME_SEC", "VERSION", "QRYNAME"],
      "orderByFields": [
        {
          "fieldName": "ROLESTATUS",
          "sortOrder": "ASC"
        },
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}

7 - Aggregate SQL Translation Examples

Internal reference showing how PsoftQL aggregate requests translate to SQL, with real response bodies for the most-used patterns

This document shows how PsoftQL aggregate requests translate to SQL queries — and what comes back from the wire. It is both an internal reference for implementation and a guide for callers assembling aggregate requests. Each example below shows three things: the PsoftQL request, the SQL it generates, and the actual JSON response captured against a live PeopleSoft Campus Solutions 9.2 / PeopleTools 8.61 environment. For the response shape in the abstract — objectType: "aggregateResult", results array instead of fields, the top-level isAggregate and totalGroups markers — see Aggregate Response Structure.

Example 1: Simple COUNT with GROUP BY

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    COUNT(1) AS role_count
FROM PS_PSROLEDEFN A
GROUP BY A.ROLETYPE
ORDER BY A.ROLETYPE

Response

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE"],
      "aggregateFields": ["role_count"],
      "results": [
        { "ROLETYPE": "Q", "role_count": 14 },
        { "ROLETYPE": "U", "role_count": 252 }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "isAggregate": true,
  "totalGroups": 2,
  "productVersion": "2026-04-20"
}

Example 2: COUNT with Criteria (WHERE clause)

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "criteriaFields": [
        {
          "fieldName": "ROLESTATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "active_role_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    COUNT(1) AS active_role_count
FROM PS_PSROLEDEFN A
WHERE A.ROLESTATUS = 'A'
GROUP BY A.ROLETYPE
ORDER BY A.ROLETYPE

Response

On this demo database every role is active, so the counts match Example 1 — but adding the filter is what guarantees that, in a database with both Active and Inactive roles, you’d only see the active ones in the result:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE"],
      "aggregateFields": ["active_role_count"],
      "results": [
        { "ROLETYPE": "Q", "active_role_count": 14 },
        { "ROLETYPE": "U", "active_role_count": 252 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 2
}

Example 3: Multiple GROUP BY Fields

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE", "ROLESTATUS"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    A.ROLESTATUS,
    COUNT(1) AS role_count
FROM PS_PSROLEDEFN A
GROUP BY A.ROLETYPE, A.ROLESTATUS
ORDER BY A.ROLETYPE, A.ROLESTATUS

Response

Each row in results represents one unique combination of the GROUP BY fields. With two groupings of size 2 there are up to 4 possible buckets — the demo database happens to have data in 2 of them:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE", "ROLESTATUS"],
      "aggregateFields": ["role_count"],
      "results": [
        { "ROLETYPE": "Q", "ROLESTATUS": "A", "role_count": 14 },
        { "ROLETYPE": "U", "ROLESTATUS": "A", "role_count": 252 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 2
}

Example 4: Multiple Aggregate Functions

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "JOB",
      "criteriaFields": [
        {
          "fieldName": "EMPL_STATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["DEPTID"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "employee_count"
          },
          {
            "function": "SUM(ANNUAL_RT)",
            "outputLabel": "total_salary"
          },
          {
            "function": "AVG(ANNUAL_RT)",
            "outputLabel": "avg_salary"
          },
          {
            "function": "MIN(ANNUAL_RT)",
            "outputLabel": "min_salary"
          },
          {
            "function": "MAX(ANNUAL_RT)",
            "outputLabel": "max_salary"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.DEPTID,
    COUNT(1) AS employee_count,
    SUM(A.ANNUAL_RT) AS total_salary,
    AVG(A.ANNUAL_RT) AS avg_salary,
    MIN(A.ANNUAL_RT) AS min_salary,
    MAX(A.ANNUAL_RT) AS max_salary
FROM PS_JOB A
WHERE A.EMPL_STATUS = 'A'
GROUP BY A.DEPTID
ORDER BY A.DEPTID

Response (first 4 of many groups, trimmed for readability via rowLimit: 5)

All five aggregate functions are evaluated for each group. Numeric fields come back as JSON numbers (not strings); preserve precision in your client by parsing into a decimal type rather than a 64-bit float when the values can exceed safe-integer range — note max_salary of 192132000 in row 2:

{
  "data": {
    "JOB": {
      "objectType": "aggregateResult",
      "objectName": "JOB",
      "groupByFields": ["DEPTID"],
      "aggregateFields": ["employee_count", "total_salary", "avg_salary", "min_salary", "max_salary"],
      "results": [
        {
          "DEPTID": "1000",
          "employee_count": 7,
          "total_salary": 2155000,
          "avg_salary": 307857.1428571429,
          "min_salary": 180000,
          "max_salary": 490000
        },
        {
          "DEPTID": "10000",
          "employee_count": 827,
          "total_salary": 602910821.362,
          "avg_salary": 729033.6413083434,
          "min_salary": 0,
          "max_salary": 192132000
        },
        {
          "DEPTID": "10001",
          "employee_count": 11,
          "total_salary": 183759.835,
          "avg_salary": 16705.43954545455,
          "min_salary": 1920,
          "max_salary": 36813
        }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 5
}

Example 5: No GROUP BY (Total Aggregation)

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": [],
        "aggregateFields": [
          {
            "function": "COUNT(*)",
            "outputLabel": "total_roles"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    COUNT(*) AS total_roles
FROM PS_PSROLEDEFN A

Note: No GROUP BY clause, no ORDER BY clause when there are no groupByFields.

Response

When there is no grouping the results array always has exactly one element. totalGroups is 1 even though there’s no real grouping happening — the single global aggregate is treated as one degenerate group:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": [],
      "aggregateFields": ["total_roles"],
      "results": [
        { "total_roles": 266 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 1
}

Example 6: COUNT DISTINCT

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "STDNT_ENRL",
      "criteriaFields": [
        {
          "fieldName": "STDNT_ENRL_STATUS",
          "fieldValue": "E",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["INSTITUTION", "STRM"],
        "aggregateFields": [
          {
            "function": "COUNT(DISTINCT EMPLID)",
            "outputLabel": "unique_students"
          },
          {
            "function": "COUNT(1)",
            "outputLabel": "total_enrollments"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.INSTITUTION,
    A.STRM,
    COUNT(DISTINCT A.EMPLID) AS unique_students,
    COUNT(1) AS total_enrollments
FROM PS_STDNT_ENRL A
WHERE A.STDNT_ENRL_STATUS = 'E'
GROUP BY A.INSTITUTION, A.STRM
ORDER BY A.INSTITUTION, A.STRM

Response (first 3 groups via rowLimit: 3)

Note unique_students (DISTINCT EMPLID) versus total_enrollments (raw COUNT(1)) — the gap tells you how many students were enrolled in more than one class for that term:

{
  "data": {
    "STDNT_ENRL": {
      "objectType": "aggregateResult",
      "objectName": "STDNT_ENRL",
      "groupByFields": ["INSTITUTION", "STRM"],
      "aggregateFields": ["unique_students", "total_enrollments"],
      "results": [
        { "INSTITUTION": "GLAKE", "STRM": "0370", "unique_students": 1, "total_enrollments": 2 },
        { "INSTITUTION": "GLAKE", "STRM": "0410", "unique_students": 1, "total_enrollments": 1 },
        { "INSTITUTION": "GLAKE", "STRM": "0430", "unique_students": 1, "total_enrollments": 2 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 3
}

Example 7: With Effective Date Logic

When noEffectiveDateLogic is false (default) and the record has EFFDT:

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL",
      "aggregateConfig": {
        "groupByFields": ["INSTITUTION"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "org_count"
          }
        ]
      }
    }
  ]
}

Generated SQL (Oracle)

SELECT
    A.INSTITUTION,
    COUNT(1) AS org_count
FROM PS_ACAD_ORG_TBL A
WHERE A.EFFDT = (
    SELECT MAX(A2.EFFDT)
    FROM PS_ACAD_ORG_TBL A2
    WHERE A2.ACAD_ORG = A.ACAD_ORG
    AND A2.EFFDT <= SYSDATE
)
GROUP BY A.INSTITUTION
ORDER BY A.INSTITUTION

Response (first 4 of 12 institutions)

The correlated sub-select restricts each row to its current effective-dated version before grouping, so org_count is the count of currently effective org rows per institution — not the count of every historical version. Set noEffectiveDateLogic: true to count every historical row instead.

{
  "data": {
    "ACAD_ORG_TBL": {
      "objectType": "aggregateResult",
      "objectName": "ACAD_ORG_TBL",
      "groupByFields": ["INSTITUTION"],
      "aggregateFields": ["org_count"],
      "results": [
        { "INSTITUTION": "GLAKE", "org_count": 18 },
        { "INSTITUTION": "PSAUS", "org_count": 25 },
        { "INSTITUTION": "PSCCS", "org_count": 37 },
        { "INSTITUTION": "PSESP", "org_count": 26 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 12
}

Example 8: With rowLimit (Top N)

PsoftQL Request

{
  "isAggregate": true,
  "rowLimit": 10,
  "records": [
    {
      "recordName": "JOB",
      "criteriaFields": [
        {
          "fieldName": "EMPL_STATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["DEPTID"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "employee_count"
          }
        ]
      }
    }
  ]
}

Generated SQL (Oracle 12c+)

SELECT
    A.DEPTID,
    COUNT(1) AS employee_count
FROM PS_JOB A
WHERE A.EMPL_STATUS = 'A'
GROUP BY A.DEPTID
ORDER BY A.DEPTID
FETCH FIRST 10 ROWS ONLY

Generated SQL (SQL Server)

SELECT TOP 10
    A.DEPTID,
    COUNT(1) AS employee_count
FROM PS_JOB A
WHERE A.EMPL_STATUS = 'A'
GROUP BY A.DEPTID
ORDER BY A.DEPTID

Response

Same shape as Example 4 but capped at 10 groups. The response’s totalGroups reflects the truncated count, not the underlying total — so for true “top N” queries you typically add an explicit ORDER BY (via orderByFields) on the aggregated column, otherwise rowLimit just trims the first N alphabetical DEPTID buckets.

Example 9: With sqlWhereClause

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "sqlWhereClause": "A.ROLENAME LIKE 'SA%' AND A.ROLESTATUS = 'A'",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          },
          {
            "function": "MAX(LASTUPDDTTM)",
            "outputLabel": "last_updated"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    COUNT(1) AS role_count,
    MAX(A.LASTUPDDTTM) AS last_updated
FROM PS_PSROLEDEFN A
WHERE A.ROLENAME LIKE 'SA%' AND A.ROLESTATUS = 'A'
GROUP BY A.ROLETYPE
ORDER BY A.ROLETYPE

Response

The MAX(LASTUPDDTTM) aggregate returns the most recent edit timestamp for each group as a string in PeopleSoft’s standard format. Mixing a numeric aggregate (COUNT) with a non-numeric one (MAX on a datetime) in the same request is fine — each column is computed independently:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE"],
      "aggregateFields": ["role_count", "last_updated"],
      "results": [
        { "ROLETYPE": "U", "role_count": 2, "last_updated": "2007-07-11-16.45.39.000000" }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 1
}

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEUSER",
      "aggregateConfig": {
        "groupByFields": ["ROLENAME"],
        "aggregateFields": [
          {
            "function": "COUNT(DISTINCT ROLEUSER)",
            "outputLabel": "user_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLENAME,
    COUNT(DISTINCT A.ROLEUSER) AS user_count
FROM PS_PSROLEUSER A
GROUP BY A.ROLENAME
ORDER BY A.ROLENAME

Response (first 5 of many groups via rowLimit: 5)

{
  "data": {
    "PSROLEUSER": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEUSER",
      "groupByFields": ["ROLENAME"],
      "aggregateFields": ["user_count"],
      "results": [
        { "ROLENAME": "ACM Administrator",         "user_count": 1 },
        { "ROLENAME": "AG Composer Administrator", "user_count": 1 },
        { "ROLENAME": "AG Composer User",          "user_count": 1 },
        { "ROLENAME": "AWE Administrator",         "user_count": 2 },
        { "ROLENAME": "AppServer Administrator",   "user_count": 4 }
      ]
    }
  },
  "responseCode": 200,
  "isAggregate": true,
  "totalGroups": 5
}

Implementation Notes

SQL Generation Pattern

SELECT
    {groupByFields with A. prefix},
    {aggregateFields with A. prefix on field references}
FROM PS_{recordName} A
WHERE {criteriaFields converted to SQL}
    {AND sqlWhereClause if provided}
    {AND effective date logic if applicable}
GROUP BY {groupByFields with A. prefix}
ORDER BY {groupByFields with A. prefix}
{FETCH FIRST rowLimit ROWS ONLY if rowLimit provided}

Field Prefix Handling

The aggregate function string needs to be parsed to add the A. alias prefix to field names:

InputOutput
COUNT(*)COUNT(*) (no change)
COUNT(1)COUNT(1) (no change)
COUNT(EMPLID)COUNT(A.EMPLID)
COUNT(DISTINCT EMPLID)COUNT(DISTINCT A.EMPLID)
SUM(ANNUAL_RT)SUM(A.ANNUAL_RT)
AVG(SALARY)AVG(A.SALARY)
MIN(HIRE_DT)MIN(A.HIRE_DT)
MAX(LASTUPDDTTM)MAX(A.LASTUPDDTTM)

Validation Checks

  1. If isAggregate: true and pageNumber > 1 → Error
  2. If isAggregate: true and multiple records with parentRecordName → Error
  3. If aggregateConfig missing on the record when isAggregate: true → Error
  4. If aggregateFields empty when isAggregate: true → Error
  5. Validate field names in groupByFields exist on the record
  6. Parse and validate field names in function expressions exist on the record (except * and literals like 1)