A
Anverion
Guest
Hello-- I have noticed that when a batch runs and I'm doing anything OTHER than read from the database, the IN indicator doesn't seem to work. now, let me explain. 1. Created a basic ruleflow with a single service connector that updates a detail's status. 2. Created a BATCH_READ query that pulls all details based on an earnings file id. EXAMPLE CORTICON_ADC_BATCH_READ 1 DetailsByFileId select detail_record_id from cms.cms_efd WHERE earnings_file_id = {CmsEfd.earningsFileId} CmsEfd true This works and returns all of the distinct detail_record_ids based on the earnings_file_id. Next, the service connector is called and the WRITE_DEF is the following: UPDATE cms.cms_efd SET cms_efd.detail_status_id=1, cms_efd.updated_by='John Wick', cms_efd.updated_at=now() where cms_efd.detail_record_id IN ({CmsEfd.detailRecordId}) I would expect the following: UPDATE cms.cms_efd SET cms_efd.detail_status_id=1, cms_efd.updated_by='John Wick', cms_efd.updated_at=now() where cms_efd.detail_record_id IN ('613P1','613P2','613P3','613P4','613P5','613P6') Instead, I get six UPDATE statements like the following: UPDATE cms.cms_efd SET cms_efd.detail_status_id=1, cms_efd.updated_by='John Wick', cms_efd.updated_at=now() where cms_efd.detail_record_id IN ('613P1') UPDATE cms.cms_efd SET cms_efd.detail_status_id=1, cms_efd.updated_by='John Wick', cms_efd.updated_at=now() where cms_efd.detail_record_id IN ('613P2') UPDATE cms.cms_efd SET cms_efd.detail_status_id=1, cms_efd.updated_by='John Wick', cms_efd.updated_at=now() where cms_efd.detail_record_id IN ('613P3') UPDATE cms.cms_efd SET cms_efd.detail_status_id=1, cms_efd.updated_by='John Wick', cms_efd.updated_at=now() where cms_efd.detail_record_id IN ('613P4') UPDATE cms.cms_efd SET cms_efd.detail_status_id=1, cms_efd.updated_by='John Wick', cms_efd.updated_at=now() where cms_efd.detail_record_id IN ('613P5') UPDATE cms.cms_efd SET cms_efd.detail_status_id=1, cms_efd.updated_by='John Wick', cms_efd.updated_at=now() where cms_efd.detail_record_id IN ('613P6') What's the point of using an IN indicator if it's going to add an update statement for each detail that's processed? Is this how batch works? For a few details, this isn't a big deal, however, we are processing thousands of details. I've seen as many as 500k in our production environment so, 500k individual update statements isn't going to work. Thanks for your time, Branden
Continue reading...
Continue reading...