PsoftQL Operators & Filtering Examples

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

Categories:

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.

Note

These examples assume the target records are whitelisted for the calling service — see the PsoftQL Web Service documentation for configuration.

Supported Operators

Operator	Meaning	Notes
`=`	Equal	Default operator if omitted
`<`	Less than	String or numeric comparison, depends on field type
`>`	Greater than
`<=`	Less than or equal
`>=`	Greater than or equal
`<>`	Not equal	Equivalent to `!=`
`!=`	Not equal (alternate syntax)	Equivalent to `<>`
`LIKE`	Pattern match	`%` matches any substring, `_` matches exactly one character
`IN`	Membership	Supplied via `fieldValues` array (see syntax reference for details)

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

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

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

Every returned row has LASTUPDDTTM > '2020-01-01'.

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

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

{
  "data": {
    "PSXLATITEM": {
      "objectType": "record",
      "objectName": "PSXLATITEM",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

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

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

{
  "data": {
    "CRSE_CATALOG": {
      "objectType": "record",
      "objectName": "CRSE_CATALOG",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

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

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

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

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.

Security

Always use excludeFields to drop OPERPSWD, PTOPERPSWDV2, OPERPSWDSALT, and ENCRYPTED when querying PSOPRDEFN. These fields contain hashed password material and should never leave the database.

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

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

{
  "data": {
    "PSOPRDEFN": {
      "objectType": "record",
      "objectName": "PSOPRDEFN",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

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 (common shape)

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

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

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": "="
                }
            ]
        }
    ]
}

Where to go next

PsoftQL Syntax — the full reference for every request field
Aggregate SQL Examples — filtering combined with isAggregate: true
Campus Solutions Examples — filtering applied to real Campus records

PsoftQL Operators & Filtering Examples

Supported Operators

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

Request

Response

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

Request

Response

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

Request — Test 3: >=

Request — Test 4: <=

Response

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

Request — Test 5: <>

Request — Test 6: !=

Response

Test 8: Multiple Criteria (AND logic) — PSOPRDEFN

Request

Response

Tests 9, 10, 11: LIKE Wildcard Patterns

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

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

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

Response (common shape)

Test 12: Combining criteriaFields with sqlWhereClause — TERM_TBL

Request

Where to go next

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

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

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

Request — Test 3: `>=`

Request — Test 4: `<=`

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

Request — Test 5: `<>`

Request — Test 6: `!=`

Tests 9, 10, 11: `LIKE` Wildcard Patterns

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

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

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

Test 12: Combining `criteriaFields` with `sqlWhereClause` — TERM_TBL