Question
Scott Roth · Jan 14, 2021

SQL Inbound Adapter settings

In the Inbound SQL Adapter settings, is it possible to specify more than 1 field as the Key Field Name? 

Because of the way the Query is being index in Ensemble by the Key Field Name, sometimes transactions get missed and I would like to see if we can add an additional key to the mix to ensure all the transactions are picked up. In this case the InterfaceTrigger is an ID that is auto generated by the table, and I would like to use that as well to ensure we don't miss transactions, and it does not throw any warning messages when it executes the Delete Query.

Thanks

Scott

#Business Service #SQL #Caché #Ensemble
$ZV: Cache for UNIX (IBM AIX for System Power System-64) 2018.1.3 (Build 414U) Mon Oct 28 2019 11:24:02 EDT
Product version:
Ensemble 2018.1
00
2 0 1 68

Replies

Nicky Zhu · Jan 20, 2021

Hi Scott, I'm not sure whether I captured your question exactly. Say, if you want two fields to be taken at the same time to compose a key field, it can be done by combine the two fields into one "virtual" field then use it as the key field. For example

SELECT HQ_ORG_CODE||'|'||ORDER_PROJ_CODE||'|'||FORM_DATE As OID,* FROM tableX WHERE FORM_DATE >= ?

In this SQL, we concatenated the three fields with the splitter "|" into one "virtual" filed OID, then we can directly use OID as the key field because it will exist in the result set. Also, we can use SQL function to concatenate the fields, as below

SELECT STRING(HQ_ORG_CODE,ORDER_PROJ_CODE,FORM_DATE) As OID,* FROM tableX

Hope that helps

00
Accepted answer