SQL-011: 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-011
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`Criteria`
Criteria property	`retrievesql`
Lifecycle method	`Criteria read`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:Code`	Input	N/A	N/A	Lookup code used to select a single reference-data row.

Tables¶

Table	Access	Columns used
CL_PRISON_CODE	SELECT	See sample SQL for referenced columns.

Sample SQL¶

Select
    ADDRESS_LINE1 as AddressLine1,
    ADDRESS_LINE2 as AddressLine2,
    ADDRESS_LINE3 as AddressLine3,
    ADDRESS_LINE4 as AddressLine4,
    CITY_NAME as CityName,
    CODE as Code,
    FOREIGN_ADDRESS_FLAG as ForeignAddressFlag,
    PRISON_NAME as PrisonName,
    STATE_CODE as StateCode,
    ZIP_CODE as ZipCode,
    ZIP_CODE_EXT as ZipCodeExt
From CL_PRISON_CODE
WHERE
    CL_PRISON_CODE.CODE = :Code

Operations and processes¶

Prison code retrieval

Sources¶

Business\SAIF.Business.View.Claim\clsPrisonCode.vb