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	SELECT	See sample SQL for referenced columns.

Sample 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

Business rules¶

BR-003: Prison lookup prepends a synthetic blank choice

Sources¶

Business\SAIF.Business.View.Claim\clsPrisonCodeCollection.vb