SQL-016: Retrieve external image info¶

This statement retrieves a joined result set of imaging code table data — file code, category code, and document type code — with culture-specific external descriptions sourced from the corresponding GCODE tables. Only currently effective document types are returned (filtered by effective_date <= sysdate and expiration_date >= sysdate or null). Called from clsExternalImageInfo.GetExternalDocumentInfo.

Classification¶

Field	Value
SQL ID	SQL-016
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsExternalImageInfo`
Criteria property	`RETRIEVE_SQL`
Lifecycle method	`GetExternalDocumentInfo`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:culture_code`	Input	[not specified]**	5	Culture code used for localized description lookup (upper-cased before binding).
`:file_code`	Input	[not specified]**	5	Image file code to filter results.

Tables¶

Table	Access	Columns used
IMAGE_FILE_CODE	SELECT / JOIN	CODE, CODE_DESCR
IMAGE_CATEGORY_CODE	SELECT / JOIN	CODE, IMAGE_FILE_CODE, EXTERNAL_DESCR
IMAGE_DOCUMENT_TYPE_CODE	SELECT / JOIN	CODE, IMAGE_FILE_CODE, IMAGE_CATEGORY_CODE, EXTERNAL_DESCR, SORT_ORDER_NUM, EFFECTIVE_DATE, EXPIRATION_DATE
IMAGE_CATEGORY_GCODE	SELECT / LEFT JOIN	CODE, IMAGE_FILE_CODE, CULTURE_CODE, EXTERNAL_DESCR
IMAGE_DOCUMENT_TYPE_GCODE	SELECT / LEFT JOIN	CODE, IMAGE_FILE_CODE, IMAGE_CATEGORY_CODE, CULTURE_CODE, EXTERNAL_DESCR

Sample SQL¶

select
    f.code as file_code,
    cat.code as category_code,
    doc.code as document_type_code,
    f.code_descr as file_description,
    case when gcat.code is not null
         then gcat.external_descr
         else cast(cat.external_descr as nvarchar2(200))
    end as category_ext_description,
    cat.external_descr as category_ext_description_code,
    doc.external_descr as doc_type_ext_description_code,
    case when gdoc.code is not null
         then gdoc.external_descr
         else cast(doc.external_descr as nvarchar2(200))
    end as document_type_ext_description,
    doc.sort_order_num,
    doc.effective_date,
    doc.expiration_date
from image_file_code f
join image_category_code cat
    on cat.image_file_code = f.code
join image_document_type_code doc
    on doc.image_file_code = f.code
    and doc.image_category_code = cat.code
    and doc.image_file_code = cat.image_file_code
left join image_category_gcode gcat
    on gcat.code = cat.code
    and gcat.image_file_code = cat.image_file_code
    and gcat.culture_code = :culture_code
left join image_document_type_gcode gdoc
    on gdoc.code = doc.code
    and gdoc.image_file_code = doc.image_file_code
    and gdoc.image_category_code = doc.image_category_code
    and gdoc.culture_code = :culture_code
where f.code = :file_code
  and doc.effective_date <= sysdate
  and (doc.expiration_date is null or doc.expiration_date >= sysdate)
order by doc.image_file_code, doc.image_category_code, doc.sort_order_num

Operations and processes¶

Image collection retrieval

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

Sources¶

Business\SAIF.Business.Imaging\clsExternalImageInfo.vb