EIM update for complex query conditions
Siebel EIM provides EXPORT MATCHES clause which allows us to export data on the basis of some column values like:
EXPORT MATCHES = S_EVT_ACT, (ROW IN (‘1-1E5UW’,’1-4R6HD’))
But it doesn’t provide any method to export data which comes from a complex query with multiple tables involved. For e.g. if you want to export data results of this query:
SELECT * from S_EVT_ACT activity ,S_ORG_EXT account WHERE activity.ACCNT_ID = account.ROW_ID and activity.STATUS_CD = ‘Done’ and account.ROW_ID = ‘1-2ET6Y’
For this condition, you won’t be able to export the data by normal EXPORT MATCHES clause. So how do we manage this condition. Here’s the solution:
Divide the conditions based on tables. In your export ifb, first export all rows with conditions of base tables like for above case, run the ifb with following clause:
EXPORT MATCHES = S_EVT_ACT, (STATUS_CD = ‘Done’)
Batch = 10000
TABLE = EIM_ACTIVITY
ONLY BASE TABLES = S_EVT_ACT
Once you get the data in batch number 10000, you can get the required data by further changing the batch number of data required. For e.g.
UPDATE EIM_ACTIVITY activity, S_ORG_EXT account set IF_ROW_BATCH_NUM = 10001 WHERE activity.EVT_ACCNT_ID = account.ROW_ID and account.ROW_ID = ‘1-2ET6Y’
Please note that the activity column names to be used in above query(highlighted in red) are the EIM_ACTIVITY column names which need not to be the same as S_EVT_ACT columns. You might need to see the attribute mapping in interface table EIM_ACTIVITY to get the required column name.
Now you have got the required data in batch 10001 and you can perform the required update and import it back to the base table. Don’t forget to delete data in both batches after successful import.
Also See :