---
title: "SQL-020: Search users with optional filters"
summary: Dynamic admin search for external users with optional predicates for name, email, type, status, and access group.
status: draft
owner: <owner>
last_reviewed: 2026-07-24
library: SAIF.Business.External.Users
reference_type: package
---

# SQL-020: Search users with optional filters

Assembles a dynamic search query against `EXT_USER` with outer joins to related tables and optional predicates appended at runtime based on caller-supplied criteria. Supports filtering by test-user flag, name, email, user type, status, access group, and customer ID.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-020 |
| **Operation type** | Read |
| **Execution method** | Inline SQL + conditional assembly |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `clsUser` |
| **Criteria property** | Local SQL |
| **Lifecycle method** | `GetUsers(...)` search overload |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:TestUserFlag` | Input | String | 1 | Required: filter by test-user status. |
| `:AccessGroupCode` | Input | String | 5 | Optional: filter by access group code. |
| `:CustomerID` | Input | String | 30 | Optional: filter by customer ID. |
| `:Email` | Input | String | 100 | Optional: filter by email address. |
| `:Name` | Input | String | 50 | Optional: filter by person name. |
| `:StatusCode` | Input | String | 5 | Optional: filter by user status. |
| `:UserTypeCode` | Input | String | 5 | Optional: filter by user type code. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [EXT_USER](../database-tables.md#ext_user) | SELECT | All EXT_USER columns, `TEST_USER_FLAG` |
| [PERSON](../database-tables.md#person) | LEFT JOIN | `PERSON_SEQ_NUM`, `NAME` |
| [EXT_USER_TYPE](../database-tables.md#ext_user_type) | LEFT JOIN | `EXT_USER_SEQ_NUM`, `EXT_USER_TYPE_CODE`, `COMPANY_SEQ_NUM`, `CUSTOMER_ID` |
| [EXT_USER_ACCESS_GROUP](../database-tables.md#ext_user_access_group) | LEFT JOIN | `EXT_USER_TYPE_SEQ_NUM`, `EXT_USER_ACCESS_GROUP_CODE` |
| [COMPANY](../database-tables.md#company) | LEFT OUTER JOIN | `COMPANY_SEQ_NUM`, `COMPANY_NAME` |
| [PERSON_EMAIL](../database-tables.md#person_email) | LEFT OUTER JOIN | `PERSON_SEQ_NUM`, `EMAIL_ADDRESS_SEQ_NUM` |
| [EMAIL_ADDRESS](../database-tables.md#email_address) | LEFT OUTER JOIN | `EMAIL_ADDRESS_SEQ_NUM`, `EMAIL_ADDRESS` |

## Sample SQL

```sql
SELECT * FROM (
    SELECT DISTINCT
        -- (all EXT_USER columns with aliases as in SQL-015),
        c.company_name AS SearchForCompanyName
    FROM EXT_USER
    LEFT JOIN person p ON p.person_seq_num = ext_user.person_seq_num
    LEFT JOIN ext_user_type t ON t.ext_user_seq_num = ext_user.ext_user_seq_num
    LEFT JOIN ext_user_access_group g
        ON g.EXT_USER_TYPE_SEQ_NUM = t.EXT_USER_TYPE_SEQ_NUM
    LEFT OUTER JOIN company c ON c.company_seq_num = t.company_seq_num
    LEFT OUTER JOIN person_email pe ON pe.person_seq_num = p.person_seq_num
    LEFT OUTER JOIN email_address e
        ON e.email_address_seq_num = pe.email_address_seq_num
    WHERE EXT_USER.test_user_flag = :TestUserFlag
    -- Optional predicates appended at runtime:
    -- AND g.ext_user_access_group_code = :AccessGroupCode
    -- AND (UPPER(t.customer_id) = :CustomerID OR UPPER(t.credential_1_id) = :CustomerID2)
    -- AND LOWER(e.email_address) LIKE :Email
    -- AND UPPER(p.name) LIKE :Name
    -- AND t.ext_user_type_code = :UserTypeCode
    -- AND ext_user.status_code = :StatusCode
)
-- Optional outer row limit: WHERE ROWNUM <= <limit>
```

## Operations and processes
- [User search](../operations/user-search.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with `**` could not be confirmed; treat as unknown until verified.

## Sources
- `Business\SAIF.Business.External.Users\clsUser.vb`