# PsoftQL Syntax 🌟 > Detailed PsoftQL syntax documentation. PsoftQL serves as the "language" of SWS --- LLMS index: [llms.txt](/llms.txt) --- 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.](https://sws.books.cedarhillsgroup.com/docs/config/sws-config/) 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. ```http 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 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` ```http 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 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. ```http 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 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. ```http 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 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. ```json { "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 true true true false false true PSOPRDEFN PSROLEUSER PSOPRDEFN true 30 ``` ### 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](https://sws.books.cedarhillsgroup.com/docs/psoftql/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: ```json { "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 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](https://sws.books.cedarhillsgroup.com/docs/psoftql/operator-examples/#test-8-multiple-criteria-and-logic--psoprdefn) 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:** ```json { "rowLimit": 1, "records": [ { "recordName": "CRSE_CATALOG", "criteriaFields": [ { "fieldName": "CRSE_ID", "fieldValue": "000101", "operator": "=" } ] } ] } ``` ```json { "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:** ```json { "rowLimit": 1, "effectiveDateOverride": "2005-01-01", "records": [ { "recordName": "CRSE_CATALOG", "criteriaFields": [ { "fieldName": "CRSE_ID", "fieldValue": "000101", "operator": "=" } ] } ] } ``` ```json { "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: ```json { "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 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. ```json { "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 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: ```json { "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. ```json { "isDebugMode": false, "includeFieldTypes": false, "rowLimit": 2, "pageNumber": 1, "records": [ { "recordName": "ACAD_ORG_TBL" } ] } ``` The response coming back will look like this: ```json { "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. ```json { "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. ```json { "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: ```json { "isDebugMode": false, "includeFieldTypes": false, "rowLimit": 2, "pageNumber": 999, "records": [ { "recordName": "ACAD_ORG_TBL" } ] } ``` The response will look similar to this: ```json { "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 | Property | Type | When present | Meaning | | ---------------- | ------- | -------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `data` | object | Always | Container for the actual record data, keyed by record name. Empty `{}` on error. | | `responseCode` | integer | Always | Application-level status. `200` = success, `400` = handler-detected validation error. Check this before parsing `data`. See [Error Responses](#error-responses). | | `errorMessages` | string | Always | Empty string on success; a single human-readable error string when `responseCode` is `400`. Reserved name; production code should not depend on the exact wording. | | `pageNumber` | integer | Standard queries | The page you just received. Equals the `pageNumber` you sent (defaults to `1`). | | `nextPageNumber` | integer | Standard queries when more pages exist | The next page to request. **Absence of this property is how you detect "last page" or "zero results."** | | `apiVersion` | string | Older builds | Legacy version stamp. Newer builds emit `productVersion` instead. Treat as informational; do not gate behavior on it. | | `productVersion` | string | Newer builds | Replaces `apiVersion`. Build/release stamp of the SWS code that served the request. | | `isAggregate` | boolean | Aggregate queries only | Always `true` when present. Echoes back the request's `isAggregate` flag so the caller can tell aggregate responses apart from standard ones at a glance. | | `totalGroups` | integer | Aggregate queries only | Count 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) | Property | Type | Meaning | | ------------ | ------ | ---------------------------------------------------------------------------------- | | `objectType` | string | Always `"record"` for standard queries; `"aggregateResult"` for aggregate queries. | | `objectName` | string | The record name. Echoes what you asked for in `recordName`. | | `fields` | array | The 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: | Property | Type | When present | | ---------------------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `rowNumber` | integer | Standard queries. **Absolute row index across the whole result set** — continues counting page-to-page rather than restarting per page. | | `{FIELD}_description` | string | When `includeAllDescriptions: true` or when `{FIELD}` is in `includeDescriptionsFor`. Decoded XLAT or prompt-table value. | | `{FIELD}_defaultLabel` | string | When `includeAllFieldLabels: true`. Default field label from the record definition. | | `{FIELD}_fieldType` | string | When `includeFieldTypes: true`. PeopleSoft data type (e.g. `CHAR`, `NUMBER`). | | `{FIELD}_fieldLength` | integer | When `includeFieldTypes: true`. Field length declared on the record. | | `{FIELD}_isKey` | boolean | When `includeKeyFieldIndicators: true`. Only emitted for fields that are part of the record's key list; not emitted when `false`. | | `{ChildRecord}` | object | When a child record is configured. Nested `{ objectType, objectName, fields: [...] }` object — see the [parent/child examples](#joining-tables---automatically). | ### Inside `data.{recordName}` (aggregate queries) Aggregate responses use a different shape — see [Aggregate Response Structure](#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. | Property | Type | Use | | ------------------- | ------ | ----------------------------------------------------------------------------------------------------------------------------------------------- | | `toolsVer` | string | PeopleTools version that served the request (e.g. `"8.61.03"`). Useful when reproducing issues across environments. | | `dbname` | string | Database name (e.g. `"CS92DEV"`). Sanity-check when configurations span multiple databases. | | `dbType` | string | `"ORACLE"` or `"MICROSOFT"` — drives the SQL dialect SWS generated (e.g. `FETCH FIRST` vs `TOP`). | | `serverTimeZone` | string | App server's local time zone. The `responseDTTM` field is in this zone, not UTC. | | `responseDTTM` | string | Server timestamp when the response was assembled, format `YYYY-MM-DD-HH.MM.SS.NNNNNN`. | | `psftTransactionId` | string | Per-call GUID issued by PeopleSoft IB. **Use this when filing support tickets** — it links the response to logs in PSAPPSRV and the IB monitor. | | `currentUser` | string | OPRID of the authenticated caller. Older builds only; newer builds omit it. | | `serverDirectory` | string | App-server install path. Older builds only. | | `debugMessages` | string | Empty 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](https://sws.books.cedarhillsgroup.com/docs/config/sws-config/#sql-response-metadata) 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: ```http POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1 Authorization: Basic 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 ```http 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 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](https://sws.books.cedarhillsgroup.com/docs/psoftql/psoftql-validator/) page accepts JSON or XML and runs the official schema in-browser. #### Example: pagination attempted in aggregate mode ```http 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](#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: | Symptom | What it means | | ----------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `fields: []` with `responseCode: 200` | Your filter matched zero rows. See the zero-row example in the [Operator Examples](https://sws.books.cedarhillsgroup.com/docs/psoftql/operator-examples/) page (Test 5). | | Unknown `fieldName` in `criteriaFields` is silently ignored | The 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 | HTTP | `responseCode` | Typical cause | What to fix | | ---- | --------------------- | -------------------------------------------------- | --------------------------------------------------------------------------------- | | 401 | (no body) | Bad password, locked account, no service-op access | Verify credentials and that the OPRID is granted `CHG_SWS_PSOFTQL` | | 200 | `400` | Record not whitelisted / does not exist | Add the record to the whitelist for your permission list | | 200 | `400` | Malformed JSON | Validate locally with the [PsoftQL Validator](https://sws.books.cedarhillsgroup.com/docs/psoftql/psoftql-validator/) | | 200 | `400` | Aggregate mode + `pageNumber > 1` | Remove `pageNumber`, or drop `isAggregate` | | 200 | `200`, empty `fields` | No data matched filter | Not 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 ```json { "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 Example | Description | | --------------------------- | ----------------------------------------------------------- | | `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: ```json { "isAggregate": true, "includeAllDescriptions": true, "records": [ { "recordName": "PSROLEDEFN", "aggregateConfig": { "groupByFields": ["ROLETYPE"], "aggregateFields": [ { "function": "COUNT(1)", "outputLabel": "role_count" } ] } } ] } ``` Response: ```json { "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: ```json { "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): ```json { "isAggregate": true, "records": [ { "recordName": "PSROLEDEFN", "criteriaFields": [ { "fieldName": "ROLESTATUS", "fieldValue": "A", "operator": "=" } ], "aggregateConfig": { "groupByFields": [], "aggregateFields": [ { "function": "COUNT(*)", "outputLabel": "total_active_roles" } ] } } ] } ``` Response: ```json { "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: | Aspect | Standard Query | Aggregate Query | | ------------------- | ------------------------------ | --------------------- | | `objectType` | `"record"` | `"aggregateResult"` | | Data array | `"fields"` | `"results"` | | Row identifier | `rowNumber` included | Not included | | Pagination | `pageNumber`, `nextPageNumber` | Not present | | Top-level indicator | None | `"isAggregate": true` | | Count | None | `"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](https://sws.books.cedarhillsgroup.com/docs/psoftql/psoftql-validator/) to check your PsoftQL syntax interactively. - `includeFieldTypes`, `includeAllFieldLabels`, `includeKeyFieldIndicators` - These are ignored in aggregate mode