Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/14/2024 07:22 AM - last edited on 10/14/2024 10:26 AM by Dave
Hi All,
Does the below condition work if Users were created in Bulk fashion?, lets say 8 users onboarded into EIC in single a UserImport, does it increment 1 by 1?
(SELECT LPAD(COUNT(*) + 1, 6, '0') FROM CURRENTUSERS)
[This post has been edited by a Moderator to move to its own thread.]
10/14/2024 10:03 AM
You can validate let us know if you face issue.. with new thread
10/14/2024 10:13 AM
Hi @rushikeshvartak ,
I validated it using below query,
lets say, User Count(*) = 01000,
Next, 5 users onboarded in a UserImport, now CP15 becomes 01001 to all the 5 onboarded users(My guess is because Count(*) is still 01000)
Next, USER COUNT(*) becomes 1005, On the Next userimport, lets say 1 user gets onboarded, then CP15 becomes 01006
"UPDATE NEWUSERDATA SET newuserdata.customproperty15 = (SELECT LPAD(COUNT(*)+ 1, 5, '0') FROM CURRENTUSERS)"
10/14/2024 10:27 AM
Please share logs
10/15/2024 01:27 AM
@vivekrajan1 try this s
UPDATE NEWUSERDATA SET newuserdata.customproperty15 = (SELECT LPAD((select count(*) from currentusers) + (select count(customproperty15) from newuserdata)+ 1, 5, '0'))"
10/15/2024 08:22 AM
@NM This is the error i get if I try the above query.
Processing query..
15-Oct-2024 15:11:43.210 WARNING [quartzScheduler_Worker-7] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_632147 SET TEMPNEWUSERS_632147.customproperty15 = (SELECT LPAD((select count(*) from TEMPUSERS_632147) + (select count(customproperty15) from TEMPNEWUSERS_632147)+ 1, 5, '0')) because: You can't specify target table 'TEMPNEWUSERS_632147' for update in FROM clause
Error while processing data:
"java.sql.SQLException: You can't specify target table 'TEMPNEWUSERS_632147' 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.provisoning.rest.RestProvisioningService.processUsers(RestProvisioningService.groovy:3119) at com.saviynt.provisoning.rest.RestProvisioningService.importUsers(RestProvisioningService.groovy:2971) at com.saviynt.ecm.integration.ExternalConnectionCallService.importUserUsingExternalConnection(ExternalConnectionCallService.groovy:1251) at UserImportJob.execute(UserImportJob.groovy:108) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)"
Start dropTempTables
Dropping table TEMPNEWUSERS_632147