We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

UserImport endDate validation

HarishG
Regular Contributor
Regular Contributor

Hi,

Can we do validations on user attributes(endDate) during the user import?

Example:

If endDate of more the 1000 existing users is getting changed during the import, can we validate this condition before updating the endDate 1000 users to: avoid updating the enddate? or stop the import? or ignore stop those 1000 records and update the remaining?

what would be the best practice?

is this possible with preprocessor query? or do we need to have external jar? or any other best options?

Thanks in advance!

 

Best regards

Harish

2 REPLIES 2

rushikeshvartak
All-Star
All-Star

You can handle this in preprocessor


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

HarishG
Regular Contributor
Regular Contributor

Hi @rushikeshvartak ,

Thanks for the info.

I tried to use the below query to handle this usecase. But getting the below errors in logs. Any suggestion?

"UPDATE NEWUSERDATA INNER JOIN CURRENTUSERS ON NEWUSERDATA.customproperty42 = CURRENTUSERS.customproperty42 SET NEWUSERDATA.customproperty3=(case when COUNT>= 1000 then CURRENTUSERS.customproperty3 else NEWUSERDATA.customproperty3 end) "

COUNT= number of customproperty3 changes - when compared to CURRENTUSERS table and NEWUSERDATA table

Final query after replacing count with inner select:

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.customproperty42 = CURRENTUSERS.customproperty42 SET NEWUSERDATA.customproperty3=(case when ((SELECT count(*) FROM CURRENTUSERS INNER JOIN NEWUSERDATA ON CURRENTUSERS.customproperty42 =NEWUSERDATA.customproperty42 where CURRENTUSERS.customproperty3 != NEWUSERDATA.customproperty3)>= 1000) then CURRENTUSERS.customproperty3 else NEWUSERDATA.customproperty3 end) "

 

is this the way you are suggesting or is there any simple way to build this usecase?

Error from logs:

2024-02-16T13:22:39+01:00-ecm-worker-jobs.SchemaUserJob-quartzScheduler_Worker-11-778lj-ERROR-DATA FILE sftpuserimport.csv MOVED TO FAILED DIR = true. JOB NOT COMPLETED SUCESSFULLY. Error in Users Import - Error while processing data: You can't specify target table 'TEMPNEWUSERS_12875' for update in FROM clause
 
2024-02-16T13:21:39+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-11-778lj-ERROR-Error while processing data:
 
2024-02-16T13:21:39+01:00-ecm-worker--null-778lj--java.sql.SQLException: You can't specify target table 'TEMPNEWUSERS_12875' for update in FROM clause at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:416) at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:405) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:733) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:686) at SchemaUserJob$_execute_closure2_closure8.doCall(SchemaUserJob.groovy:223) at SchemaUserJob$_execute_closure2.doCall(SchemaUserJob.groovy:182) at SchemaUserJob.execute(SchemaUserJob.groovy:77) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)
 
2024-02-16T13:21:39+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-11-778lj-ERROR-Error in Users Import - Import failed as no Users were inserted or updated
 
 
Best regards
Harish