Dynamic attribute "Subquery returns more than 1 row"

[RajeshA]

Hello Saviynt Team,

I have two dynamic attributes where second dynamic attribute will show values from first dynamic attribute. below are those settings

when I select "kontoorBrands" I am not getting any result and screen shows error. But when I execute (below query) in data analyzer I am getting 13 entries 

select distinct customproperty1 from entitlement_values where entitlementtypekey=656 and entitlement_value like '%KontoorBrands%'

Can you let me know where I am doing wrong. 

[rushikeshvartak]

Please try below query for organization 

select distinct customproperty1 as ID from entitlement_values where entitlementtypekey=656 and entitlement_value like '%${Organization}%'

Your query have below error  java.sql.SQLException: Subquery returns more than 1 row

[RajeshA]

Hello @rushikeshvartak 

Thanks for responding back. I had tried your proposed solution and seems we still have an error. when I select kontoorEcom from organization dropdown, looks like ${organization} is taking the value with quotes and when you combined it with  one more quotes for like statement '%%' it is not working.

so I have kept those values in customproperty3 and it is working now

Below are the error logs

2023-11-28/14:10:22.487 [{}] [https-jsse-nio-443-exec-46] DEBUG services.WorkflowmanagementService - Entitlement_values count qry executed:: 1
2023-11-28/14:10:22.489 [{}] [https-jsse-nio-443-exec-46] DEBUG services.WorkflowmanagementService - exit showTableMultipleJson
2023-11-28/14:10:22.492 [{}] [https-jsse-nio-443-exec-46] DEBUG workflow.WorkflowmanagementController - Returning from controllers records-Total-0, resultList-0
2023-11-28/14:10:25.091 [{}] [https-jsse-nio-443-exec-20] DEBUG workflow.WorkflowmanagementController - enter recreatedyna attrid = 601
2023-11-28/14:10:25.091 [{}] [https-jsse-nio-443-exec-20] DEBUG workflow.WorkflowmanagementController - attrToHide = []
2023-11-28/14:10:25.147 [{}] [https-jsse-nio-443-exec-20] DEBUG workflow.WorkflowmanagementController - processing attribute project_level_accounts
2023-11-28/14:10:25.147 [{}] [https-jsse-nio-443-exec-20] DEBUG workflow.WorkflowmanagementController - attribute project_level_accounts need to be displayed
2023-11-28/14:10:25.150 [{}] [https-jsse-nio-443-exec-20] DEBUG workflow.WorkflowmanagementController - dynamicAttributeMap11=====[:]
2023-11-28/14:10:25.155 [{}] [https-jsse-nio-443-exec-20] DEBUG workflow.WorkflowmanagementController - tempVal======'KontoorEcom'
2023-11-28/14:10:25.155 [{}] [https-jsse-nio-443-exec-20] DEBUG workflow.WorkflowmanagementController - bindingMap====before ==[VAL:'KontoorEcom', Organization_unquoted:KontoorEcom, Organization:'KontoorEcom', organization_unquoted:KontoorEcom, userkey:'32283', ORGANIZATION:'KontoorEcom', ?1:'1', USERKEY:'32283', action:'recreatedyna', id:'601', ID:'601', PROJECT_LEVEL_ACCOUNTS:'', project_level_accounts:'', val:'KontoorEcom', controller:'workflowmanagement', ACCOUNTS:'0', REQUESTOR:32283, requestor:32283, requestee:32283, CONTROLLER:'workflowmanagement', ACTION:'recreatedyna', organization:'KontoorEcom', REQUESTEE:32283, accounts:'0', ORGANIZATION_unquoted:KontoorEcom]
2023-11-28/14:10:25.156 [{}] [https-jsse-nio-443-exec-20] DEBUG println.PrintlnToLogger - Println :: bindingMap======[VAL:'KontoorEcom', Organization_unquoted:KontoorEcom, Organization:'KontoorEcom', organization_unquoted:KontoorEcom, userkey:'32283', ORGANIZATION:'KontoorEcom', ?1:'1', USERKEY:'32283', action:'recreatedyna', id:'601', ID:'601', PROJECT_LEVEL_ACCOUNTS:'', project_level_accounts:'', val:'KontoorEcom', controller:'workflowmanagement', ACCOUNTS:'0', REQUESTOR:32283, requestor:32283, requestee:32283, CONTROLLER:'workflowmanagement', ACTION:'recreatedyna', organization:'KontoorEcom', REQUESTEE:32283, accounts:'0', ORGANIZATION_unquoted:KontoorEcom]
2023-11-28/14:10:25.156 [{}] [https-jsse-nio-443-exec-20] DEBUG workflow.WorkflowmanagementController - binding map is [VAL:'KontoorEcom', Organization_unquoted:KontoorEcom, Organization:'KontoorEcom', organization_unquoted:KontoorEcom, userkey:'32283', ORGANIZATION:'KontoorEcom', ?1:'1', USERKEY:'32283', action:'recreatedyna', id:'601', ID:'601', PROJECT_LEVEL_ACCOUNTS:'', project_level_accounts:'', val:'KontoorEcom', controller:'workflowmanagement', ACCOUNTS:'0', REQUESTOR:32283, requestor:32283, requestee:32283, CONTROLLER:'workflowmanagement', ACTION:'recreatedyna', organization:'KontoorEcom', REQUESTEE:32283, accounts:'0', ORGANIZATION_unquoted:KontoorEcom]
2023-11-28/14:10:25.227 [{}] [https-jsse-nio-443-exec-20] DEBUG println.PrintlnToLogger - Println :: SQL query is select distinct customproperty1 as ID from entitlement_values where entitlementtypekey=656 and entitlement_value like '%'KontoorEcom'%'
2023-11-28/14:10:25.227 [{}] [https-jsse-nio-443-exec-20] DEBUG workflow.WorkflowmanagementController - SQL query is select distinct customproperty1 as ID from entitlement_values where entitlementtypekey=656 and entitlement_value like '%'KontoorEcom'%'
2023-11-28/14:10:25.237 [{}] [https-jsse-nio-443-exec-20] ERROR errors.GrailsExceptionResolver - MySQLSyntaxErrorException occurred when processing request: [POST] /ECM/workflowmanagement/recreatedyna/601
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 'KontoorEcom'%'' at line 1. Stacktrace follows:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'KontoorEcom'%'' at line 1
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2769)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
at com.saviynt.ecm.workflow.WorkflowmanagementController$_closure293_closure987.doCall(WorkflowmanagementController.groovy:17515)
at com.saviynt.ecm.workflow.WorkflowmanagementController$_closure293.doCall(WorkflowmanagementController.groovy:17172)
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:171)
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.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2023-11-28/14:10:25.260 [{}] [https-jsse-nio-443-exec-20] ERROR error.ErrorController - Exception
org.codehaus.groovy.grails.web.errors.GrailsWrappedRuntimeException: 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 'KontoorEcom'%'' at line 1
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:171)
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.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'KontoorEcom'%'' at line 1
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2769)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
at com.saviynt.ecm.workflow.WorkflowmanagementController$_closure293_closure987.doCall(WorkflowmanagementController.groovy:17515)
at com.saviynt.ecm.workflow.WorkflowmanagementController$_closure293.doCall(WorkflowmanagementController.groovy:17172)
... 9 more