---
title: "SQL-005: Retrieve agency office by agent number"
summary: Retrieve one agency-office row by agent number.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Agency
reference_type: package
---

# SQL-005: Retrieve agency office by agent number

This statement loads a single office view from `AGENT` for `clsAgencyOffice`. It applies the office-only predicate and then narrows the result to one agent number.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-005 |
| **Operation type** | Read |
| **Execution method** | Inline SQL |
| **Database** | Oracle |
| **Schema** | `[unqualified]` |
| **Command type** | Text |
| **Business object** | `clsAgencyOffice` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:AgentNum` | Input | Decimal (`NUMBER`) | N/A | Agent number used as the agency-office identity key. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [AGENT](../database-tables.md#agent) | SELECT | `AGENT_NUM`, `AGENCY_SEQ_NUM`, `AGENCY_LOCATION_FLAG`, `EXCLUDE_LOV_FLAG`, `EXCLUDE_SVC_CTR_FLAG`, effective and expiration dates, audit columns |

## Sample SQL

```sql
Select
  AGENT.AGENCY_LOCATION_FLAG as AgencyLocationFlag,
  AGENT.AGENCY_SEQ_NUM as AgencySeqNum,
  AGENT.AGENT_EFF_DATE as AgentEffDate,
  AGENT.AGENT_EXP_DATE as AgentExpDate,
  AGENT.AGENT_NAME as AgentName,
  AGENT.AGENT_NUM as AgentNum,
  AGENT.CREATE_DATE as CreateDate,
  AGENT.CREATE_USER as CreateUser,
  AGENT.EXCLUDE_LOV_FLAG as ExcludeLovFlag,
  AGENT.EXCLUDE_SVC_CTR_FLAG as ExcludeSvcCtrFlag,
  AGENT.INTERFACE_DATE as InterfaceDate,
  AGENT.MAIL_ADDR_LOC_SEQ_NUM as MailAddrLocSeqNum,
  AGENT.UPDATE_DATE as UpdateDate,
  AGENT.UPDATE_USER as UpdateUser
From AGENT
Where
  (AGENT.EXCLUDE_LOV_FLAG = 'N'
   AND (AGENT.AGENCY_SEQ_NUM = AGENT.AGENT_NUM OR AGENCY_LOCATION_FLAG = 'Y'))
  AND AGENT.AGENT_NUM = :AgentNum
```

## Operations and processes
- [Retrieve agency offices](../operations/retrieve-agency-offices.md)

## Business rules
- [BR-001: Missing-key retrieval returns a default object](../business-rules.md#br-001-missing-key-retrieval-returns-a-default-object)
- [BR-015: Agency-office retrieval requires non-LOV-excluded office rows](../business-rules.md#br-015-agency-office-retrieval-requires-non-lov-excluded-office-rows)
- [BR-016: Agency office number is required](../business-rules.md#br-016-agency-office-number-is-required)
- [BR-024: The library is retrieve-only](../business-rules.md#br-024-the-library-is-retrieve-only)
- [BR-025: Identity keys are agency seq, contract seq, and agent number](../business-rules.md#br-025-identity-keys-are-agency-seq-contract-seq-and-agent-number)

## Sources
- `Business\SAIF.Business.Policy.Agency\clsAgencyOffice.vb`