UserImport endDate validation

[HarishG]

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

[rushikeshvartak]

You can handle this in preprocessor

[HarishG]

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