---
title: "SQL-028: Retrieve single IMAGE record with code descriptions"
summary: Retrieves a single IMAGE row by IMAGE_SEQ_NUM, joined to category and document type code tables with culture-specific descriptions via GCODE tables.
status: draft
owner: <owner>
last_reviewed: 2026-07-24
library: SAIF.Business.Imaging
reference_type: package
---

# 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](../database-tables.md#image) | SELECT | All key metadata columns (see sample SQL) |
| [IMAGE_CATEGORY_CODE](../database-tables.md#image_category_code) | SELECT / JOIN | CODE, CODE_DESCR, IMAGE_FILE_CODE, EXTERNAL_DESCR |
| [IMAGE_DOCUMENT_TYPE_CODE](../database-tables.md#image_document_type_code) | SELECT / JOIN | CODE, CODE_DESCR, IMAGE_CATEGORY_CODE, IMAGE_FILE_CODE, EXTERNAL_DESCR, EXTERNAL_LONG_DESCR |
| [IMAGE_CATEGORY_GCODE](../database-tables.md#image_category_gcode) | SELECT / LEFT OUTER JOIN | CODE, EXTERNAL_DESCR |
| [IMAGE_DOCUMENT_TYPE_GCODE](../database-tables.md#image_document_type_gcode) | SELECT / LEFT OUTER JOIN | CODE, EXTERNAL_DESCR, EXTERNAL_LONG_DESCR |

## Sample SQL

```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](../operations/image-indexing.md)

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

## Sources
- `Business\SAIF.Business.Imaging\clsImageIdx.vb`