SQL-028: Retrieve single IMAGE record with code descriptions

This statement retrieves a single IMAGE row by IMAGE_SEQ_NUM, joining IMAGE_CATEGORY_CODE, IMAGE_DOCUMENT_TYPE_CODE, and their corresponding GCODE globalization tables via LEFT OUTER JOINs. The CASE expressions in the SELECT use the GCODE descriptions when available, falling back to the base table descriptions. Executed by clsImageIdx.BeforeRetrieve.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:ImageSeqNum	Input	[not specified]**	N/A	Primary key of the IMAGE row to retrieve.

Tables

Table	Access	Columns used
IMAGE	SELECT	All key metadata columns (see sample SQL)
IMAGE_CATEGORY_CODE	SELECT / JOIN	CODE, CODE_DESCR, IMAGE_FILE_CODE, EXTERNAL_DESCR
IMAGE_DOCUMENT_TYPE_CODE	SELECT / JOIN	CODE, CODE_DESCR, IMAGE_CATEGORY_CODE, IMAGE_FILE_CODE, EXTERNAL_DESCR, EXTERNAL_LONG_DESCR
IMAGE_CATEGORY_GCODE	SELECT / LEFT OUTER JOIN	CODE, EXTERNAL_DESCR
IMAGE_DOCUMENT_TYPE_GCODE	SELECT / LEFT OUTER JOIN	CODE, EXTERNAL_DESCR, EXTERNAL_LONG_DESCR

Sample SQL

Select
    IMAGE.CLAIM_ID as ClaimID,
    IMAGE.CLAIM_NUM as ClaimNum,
    IMAGE.COMMENT_TEXT as CommentText,
    IMAGE.CREATE_DATE as CreateDate,
    IMAGE.CREATE_USER as CreateUser,
    IMAGE.DISCOVERY_FLAG as DiscoveryFlag,
    IMAGE.DISPLAY_FIELD_1 as DisplayField1,
    IMAGE.DISPLAY_FIELD_2 as DisplayField2,
    IMAGE.DISPLAY_FIELD_3 as DisplayField3,
    IMAGE.DISPLAY_SORT_NUM as DisplaySortNum,
    IMAGE.DOCUMENT_DATE as DocumentDate,
    IMAGE.IMAGE_CATEGORY_CODE as ImageCategoryCode,
    ICC.CODE_DESCR as ImageCategoryDescr,
    CASE WHEN IDTG.external_long_descr IS NULL
         THEN CAST(IDTC.EXTERNAL_LONG_DESCR AS NVARCHAR2(100))
         ELSE IDTG.external_long_descr END as ImageDocTypeExternalLongDescr,
    CASE WHEN ICG.external_descr IS NULL
         THEN CAST(ICC.CODE_DESCR AS NVARCHAR2(65))
         ELSE ICG.external_descr END as ImageCategoryExternalDescr,
    CASE WHEN IDTG.external_descr IS NULL
         THEN CAST(IDTC.CODE_DESCR AS NVARCHAR2(65))
         ELSE IDTG.external_descr END as ImageDocumentTypeExternalDescr,
    ICC.EXTERNAL_DESCR as ImageCategoryExternalDescr,
    IDTC.EXTERNAL_DESCR as ImageDocumentTypeExternalDescr,
    IDTC.EXTERNAL_LONG_DESCR as ImageDocTypeExternalLongDescr,
    IMAGE.IMAGE_DELETE_CODE as ImageDeleteCode,
    IMAGE.IMAGE_DOCUMENT_TYPE_CODE as ImageDocumentTypeCode,
    IDTC.CODE_DESCR as ImageDocumentTypeDescr,
    IMAGE.IMAGE_DOCUMENT_NAME_CODE as ImageDocumentNameCode,
    IMAGE.IMAGE_FILE_CODE as ImageFileCode,
    IMAGE.IMAGE_SEQ_NUM as ImageSeqNum,
    IMAGE.POLICY_NUM as PolicyNum,
    IMAGE.SECURITY_LEVEL_NUM as SecurityLevelNum,
    IMAGE.TOWID as Towid,
    IMAGE.UPDATE_DATE as UpdateDate,
    IMAGE.UPDATE_USER as UpdateUser,
    IMAGE.RECEIVED_DATE as ReceivedDate,
    IMAGE.EXTERNAL_VIEW_CODE as ExternalViewCode,
    IMAGE.EXTERNAL_VIEW_UPDATE_DATE as ExternalViewUpdateDate,
    IMAGE.SYSTEM_GENERATED_FLAG as SystemGeneratedFlag
From IMAGE
JOIN IMAGE_CATEGORY_CODE ICC ON (ICC.CODE = IMAGE.IMAGE_CATEGORY_CODE)
JOIN IMAGE_DOCUMENT_TYPE_CODE IDTC ON (IDTC.CODE = IMAGE.IMAGE_DOCUMENT_TYPE_CODE)
LEFT OUTER JOIN IMAGE_CATEGORY_GCODE ICG ON (ICC.CODE = ICG.CODE)
LEFT OUTER JOIN IMAGE_DOCUMENT_TYPE_GCODE IDTG ON (IDTC.CODE = IDTG.code)
Where IMAGE.IMAGE_SEQ_NUM = :ImageSeqNum

Operations and processes

• Image indexing

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Business\SAIF.Business.Imaging\clsImageIdx.vb