Query on preprocessor Additionaltable execution

[rameshm]

Hello Experts,

Can you please help me to understand, how many times the ADDITIONALTABLES gets executed during a single import job. Does it executes again and again per record?

In case any customProperty is getting updated and if we are having same customproperty used as part of ADDITIONALTABLES, would the currentUsers table will have updated value?

Thanks in advance.

Regards,

Ramesh

[itinjic]

During a single import job, the ADDITIONALTABLES section is executed only once. It is used to prepare the dataset on which the preprocessor queries will be run. The result of the queries is stored in temporary tables, which are then used in the preprocessor queries.

In the case of updating a customProperty that is also used in the ADDITIONALTABLES section, the value in the currentUsers table will not be automatically updated. The ADDITIONALTABLES section is executed before the preprocessor queries, so any updates made during the import job will not be reflected in the temporary tables.

If you want to update the value in the currentUsers table, you will need to include a preprocessor query that updates the customProperty in the currentUsers table based on the updated value in the imported data.

[rushikeshvartak]

• It creates temporary tables and select query is executed once but update query is ran per user

[rameshm]

Could you please help me with some update statement preprocessor query to update "enddate" in CURRENTUSERS table, any sample would be helpful.

[rushikeshvartak]

Refer samples https://forums.saviynt.com/t5/tag/Modifyuserdatajson/tg-p 

[PremMahadikar]

Hi @rameshm ,

Try below:

{ 
"ADDITIONALTABLES" :{ 
"USERS" : "SELECT username, enddate FROM USERS" 
},
"COMPUTEDCOLUMNS" : [ 
"enddate"
],
"PREPROCESSQUERIES" :[
"UPDATE CURRENTUSERS LEFT JOIN NEWUSERDATA ON CURRENTUSERS.USERNAME=NEWUSERDATA.USERNAME SET NEWUSERDATA.enddate = CASE WHEN Date(NEWUSERDATA.startdate) > Date(CURRENTUSERS.enddate) THEN NULL ELSE CURRENTUSERS.enddate END"
]
}

Note: The above script works when you have username, startdate, enddate columns as mandatory with/without additional columns.

If this helps, please select Accept As Solution and hit Kudos