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

# SQL-007: Retrieve producer by agent number

This statement loads a single producer view from `AGENT` for `clsProducer`, keyed by agent number.

## Classification

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

## Parameters

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

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [AGENT](../database-tables.md#agent) | SELECT | `AGENT_NUM`, `AGENCY_SEQ_NUM`, name columns, `AGENCY_LOCATION_FLAG`, exclusion flags, 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.AGENT_NUM = :AgentNum
```

## Operations and processes
- [Retrieve producers](../operations/retrieve-producers.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-023: Producer agencies are detected when agency sequence number equals agent number](../business-rules.md#br-023-producer-agencies-are-detected-when-agency-sequence-number-equals-agent-number)
- [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\clsProducer.vb`