# PsoftQL Operators & Filtering Examples

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

---

LLMS index: [llms.txt](/llms.txt)

---

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.

<div class="alert alert-info" role="alert"><div class="h4 alert-heading" role="heading">Note</div>


These examples assume the target records are whitelisted for the calling service — see the [PsoftQL Web Service](https://sws.books.cedarhillsgroup.com/docs/psoftql/chg_sws_psoftql/) documentation for configuration.
</div>


## 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                   | Listed 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`.

<div class="alert alert-warning" role="alert"><div class="h4 alert-heading" role="heading">IN operator status</div>


The `IN` operator appears in the JSON schema's example list but the server currently does not produce a matching `WHERE … IN (…)` clause — every attempted form (comma-separated string in `fieldValue`, parenthesized list, or a sibling `fieldValues` array) silently returns zero rows with a `200`/`responseCode: 200`. Until this gap is closed, express membership filters with `sqlWhereClause`, for example:

```json
{
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "sqlWhereClause": "ROLENAME IN ('SA Administrator','SAIP Administrator','ACM Administrator')"
    }
  ]
}
```

This route uses the raw SQL `IN` operator and works today. The PsoftQL-native `IN` operator is tracked on the road map.
</div>


---

## 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

```http
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
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

```http
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
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: `>=`

```http
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: `<=`

```http
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
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
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: `<>`

```http
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: `!=`

```http
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
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
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.

<div class="alert alert-warning" role="alert"><div class="h4 alert-heading" role="heading">Security</div>


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.
</div>


### Request

```http
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
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

```http
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

```http
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"

```http
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
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
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
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

```http
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
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

- [PsoftQL Syntax](https://sws.books.cedarhillsgroup.com/docs/psoftql/psoftql-syntax/) — the full reference for every request field
- [Aggregate SQL Examples](https://sws.books.cedarhillsgroup.com/docs/psoftql/aggregate-sql-examples/) — filtering combined with `isAggregate: true`
- [Campus Solutions Examples](https://sws.books.cedarhillsgroup.com/docs/psoftql/campus-psoftql-examples/) — filtering applied to real Campus records