IBM Sterling B2B: SELECT a Code List

From Wiki

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_ID RECEIVER_ID SENDER_ITEM RECEIVE_ITEM DESCRIPTION TEXT1 LIST_VERSION
CL_L1 XYX_AC /Partner01/Inbox Delivery to Partner 01 Example 2
CL_L1 ZYZ_AA_(\d{8})_(\d{2}).TXT /Partner02/Inbox Delivery to Partner 02 at Inbox Example 2
CL_L1 ZYZ_AA_(\d{8})_(\d{4}).TXT /Partner02/Error Delivery to Partner 02 at Error mailbox Example 2
CL_L1 ˆTMN_BR_(\d{8})_(\d{4}).TXT$ /Partner03/Inbox Delivery to Partner 03 at Inbox mailbox Example 2

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