Does (SELECT LPAD(COUNT(*) + 1, 6, '0') FROM CURRENTUSERS) work if users created in bulk?

[vivekrajan1]

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.]

[rushikeshvartak]

You can validate let us know if you face issue.. with new thread

[vivekrajan1]

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)"

[rushikeshvartak]

Please share logs

[NM]

@vivekrajan1 try this s

UPDATE NEWUSERDATA SET newuserdata.customproperty15 = (SELECT LPAD((select count(*) from currentusers) + (select count(customproperty15) from newuserdata)+ 1, 5, '0'))"

[vivekrajan1]

@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

[rushikeshvartak]

UPDATE NEWUSERDATA 
SET customproperty15 = (
    SELECT LPAD(new_count, 5, '0') 
    FROM (
        SELECT (SELECT COUNT(*) FROM currentusers) + 
               (SELECT COUNT(customproperty15) FROM NEWUSERDATA) + 1 AS new_count
    ) AS derived_table
);