---
title: "SQL-010: Retrieve prison-code list with blank option"
summary: Retrieve prison-code list with blank option
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-28
library: SAIF.Business.View.Claim
reference_type: package
---

# SQL-010: Retrieve prison-code list with blank option

This SQL document describes how `Criteria` uses an inline Oracle query to support claim-domain read behavior.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-010 |
| **Operation type** | Read |
| **Execution method** | Inline |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `Criteria` |
| **Criteria property** | ``retrievesql`` |
| **Lifecycle method** | ``Criteria read`` |

## Parameters

This SQL statement exposes no explicit input parameters in the analyzed source.


## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CL_PRISON_CODE](../database-tables.md#cl-prison-code) | SELECT | See sample SQL for referenced columns. |

## Sample SQL

```sql
Select
    '' AS AddressLine1,
    '' AS AddressLine2,
    '' AS AddressLine3,
    '' AS AddressLine4,
    '' AS CityName,
    '00' AS Code,
    '' as ForeignAddressFlag,
    '<blank>' AS PrisonName,
    '' as StateCode,
    null as ZipCode,
    '' as ZipCodeExt
FROM CL_PRISON_CODE UNION SELECT CL_PRISON_CODE.ADDRESS_LINE1 as AddressLine1, CL_PRISON_CODE.ADDRESS_LINE2 as AddressLine2, CL_PRISON_CODE.ADDRESS_LINE3 as AddressLine3, CL_PRISON_CODE.ADDRESS_LINE4 as AddressLine4, CL_PRISON_CODE.CITY_NAME as CityName, CL_PRISON_CODE.CODE as Code, CL_PRISON_CODE.FOREIGN_ADDRESS_FLAG as ForeignAddressFlag, CL_PRISON_CODE.PRISON_NAME as PrisonName, CL_PRISON_CODE.STATE_CODE as StateCode, CL_PRISON_CODE.ZIP_CODE as ZipCode, CL_PRISON_CODE.ZIP_CODE_EXT as ZipCodeExt FROM CL_PRISON_CODE
```

## Operations and processes
- [Prison code retrieval](../operations/prison-code-retrieval.md)

## Business rules
- [BR-003: Prison lookup prepends a synthetic blank choice](../business-rules.md#br-003-prison-lookup-prepends-a-synthetic-blank-choice)

## Sources
- `Business\SAIF.Business.View.Claim\clsPrisonCodeCollection.vb`