SELECT a code List

SELECT *
FROM CODE_LIST_XREF

SELECT Code List codes

SELECT *
FROM CODELIST_XREF_ITEM
WHERE CODELIST_XREF_ITEM.LIST_NAME = '<LIST NAME>'

SELECT Code List Default Version

SELECT * 
FROM CODELIST_XREF_ITEM 
WHERE CODELIST_XREF_ITEM.LIST_NAME = '<LIST NAME>'
AND CODELIST_XREF_ITEM.LIST_VERSION = (SELECT DEFAULT_VERSION 
                                       FROM CODELIST_XREF_VERS 
                                       WHERE CODELIST_XREF_VERS.LIST_NAME = '<LIST NAME>')

SELECT Code List Samples

LIST_NAME	SENDER_ITEM	RECEIVE_ITEM	DESCRIPTION	TEXT1
CL_L1	XYX_AC	/Partner01/Inbox	Delivery to Partner 01	Example
CL_L1	ZYZ_AA_(\d{8})_(\d{2}).TXT	/Partner02/Inbox	Delivery to Partner 02 at Inbox	Example
CL_L1	ZYZ_AA_(\d{8})_(\d{4}).TXT	/Partner02/Error	Delivery to Partner 02 at Error mailbox	Example
CL_L1	ˆTMN_BR_(\d{8})_(\d{4}).TXT$	/Partner03/Inbox	Delivery to Partner 03 at Inbox mailbox	Example

Search for a mailbox using the filename in a code list

SELECT * 
FROM CODELIST_XREF_ITEM 
WHERE CODELIST_XREF_ITEM.LIST_NAME = '<LIST NAME>'
AND CODELIST_XREF_ITEM.LIST_VERSION = (SELECT DEFAULT_VERSION 
                                       FROM CODELIST_XREF_VERS 
                                       WHERE CODELIST_XREF_VERS.LIST_NAME = '<LIST NAME>')
AND CODELIST_XREF_ITEM.SENDER_ITEM = 'XYX_AC';

Search for a mailbox using the filename and a regular expression in a code list

SELECT * 
FROM CODELIST_XREF_ITEM 
WHERE CODELIST_XREF_ITEM.LIST_NAME = '<LIST NAME>'
AND CODELIST_XREF_ITEM.LIST_VERSION = (SELECT DEFAULT_VERSION 
                                       FROM CODELIST_XREF_VERS 
                                       WHERE CODELIST_XREF_VERS.LIST_NAME = '<LIST NAME>')
AND REGEXP_LIKE('ZYZ_AA_20210401_04.TXT', CODELIST_XREF_ITEM.SENDER_ITEM);

Ver também