We have a use case to update an User attribute on the employee state based on the user's start date , end date and term date . Im trying to achieve it using preprocessor queries using case statement but it isnt working as expected. There is no syntax error I checked every case statement but still the condition doesnt seems to be working. Here is screeenshot when I tried using Data Analyser . Help me if Im missing anything here .
Actual Preprocessor Query:
UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY16 = case WHEN(CURRENTUSERS.CUSTOMPROPERTY33 = 'Active' and DATEDIFF(CURRENTUSERS.STARTDATE,now()) > 21) THEN 'Registered' WHEN(CURRENTUSERS.CUSTOMPROPERTY33 = 'Active' and DATEDIFF(CURRENTUSERS.STARTDATE,now()) between 1 and 21) THEN 'Prehire' WHEN (CURRENTUSERS.CUSTOMPROPERTY33 = 'Active' and (now() >= CURRENTUSERS.STARTDATE) ) THEN 'Active' WHEN (CURRENTUSERS.CUSTOMPROPERTY33 = 'Active' and ((DATEDIFF(CURRENTUSERS.TERMDATE,now()) between 0 and 10) or (DATEDIFF(CURRENTUSERS.ENDDATE,now()) between 0 and 10))) THEN 'Activeleaving' WHEN (CURRENTUSERS.CUSTOMPROPERTY33 = 'Active' and (now()>= CURRENTUSERS.TERMDATE or (now() >= CURRENTUSERS.ENDDATE and CURRENTUSERS.TERMDATE IS NULL ))) THEN 'Inactive' WHEN (CURRENTUSERS.CUSTOMPROPERTY33 = 'Terminated' and ((CURRENTUSERS.ENDDATE IS NULL or CURRENTUSERS.TERMDATE IS NULL ) or (DATEDIFF(now(),CURRENTUSERS.TERMDATE) > 10))) THEN 'Terminated' END
Error while using the preprcessor:
| java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';= TEMPUSERS_27080.TERMDATE or (now() >= TEMPUSERS_27080.ENDDATE and TEMPUSERS_' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) 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 com.saviynt.ecm.services.BulkUserUploadService.importUserFromRequest(BulkUserUploadService.groovy:265) at com.saviynt.workflowmgt.grantaccess.doaccess(grantaccess.groovy:50) at com.saviynt.workflowmgt.grantaccess$_createAccess_closure1.doCall(grantaccess.groovy:95) at com.saviynt.workflowmgt.grantaccess.createAccess(grantaccess.groovy:94) at org.jbpm.pvm.internal.util.ReflectUtil.invoke(ReflectUtil.java:184) at org.jbpm.pvm.internal.wire.descriptor.ObjectDescriptor.invokeMethod(ObjectDescriptor.java:209) at org.jbpm.jpdl.internal.activity.JavaActivity.perform(JavaActivity.java:76) at org.jbpm.jpdl.internal.activity.JpdlAutomaticActivity.execute(JpdlAutomaticActivity.java:15) at org.jbpm.pvm.internal.model.op.ExecuteActivity.perform(ExecuteActivity.java:60) at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperationSync(ExecutionImpl.java:672) at org.jbpm.pvm.internal.model.ExecutionImpl.fire(ExecutionImpl.java:582) at org.jbpm.pvm.internal.model.ExecutionImpl.take(ExecutionImpl.java:487) at org.jbpm.jpdl.internal.activity.ForEachActivity.execute(ForEachActivity.java:123) at org.jbpm.jpdl.internal.activity.ForEachActivity.execute(ForEachActivity.java:53) at org.jbpm.pvm.internal.model.op.ExecuteActivity.perform(ExecuteActivity.java:60) at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperationSync(ExecutionImpl.java:672) at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperation(ExecutionImpl.java:632) at org.jbpm.pvm.internal.model.ExecutionImpl.start(ExecutionImpl.java:217) at org.jbpm.pvm.internal.cmd.StartProcessInstanceInLatestCmd.execute(StartProcessInstanceInLatestCmd.java:63) at org.jbpm.pvm.internal.cmd.StartProcessInstanceInLatestCmd.execute(StartProcessInstanceInLatestCmd.java:36) at org.jbpm.pvm.internal.svc.DefaultCommandService.execute(DefaultCommandService.java:42) at org.jbpm.pvm.internal.tx.SpringCommandCallback.doInTransaction(SpringCommandCallback.java:45) at org.jbpm.pvm.internal.tx.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:49) at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.executeInNewEnvironment(EnvironmentInterceptor.java:53) at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.execute(EnvironmentInterceptor.java:40) at org.jbpm.pvm.internal.svc.RetryInterceptor.execute(RetryInterceptor.java:56) at org.jbpm.pvm.internal.svc.ExecutionServiceImpl.startProcessInstanceByKey(ExecutionServiceImpl.java:71) at com.saviynt.ecm.services.WorkflowService.workflowaccessreqStart(WorkflowService.groovy:1065) at com.saviynt.ecm.services.BulkUserUploadService.createRequestForBulkUserUpload(BulkUserUploadService.groovy:91) at com.saviynt.ecm.services.UsersService.uploadUserFinalStep(UsersService.groovy:8687) at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1733) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:159) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750) |
Regards,
Mansoor
