problem in Populating Dynamic Attribute in Ars page

[Mahendran]

SELECT DISTINCT accountname as ID FROM ( SELECT customproperty6, userkey, @rank := IF(@prev = accountname, @rank + 1, 1) AS rank, @prev := accountname, CASE WHEN @rank = 1 THEN accountname ELSE CONCAT(accountname, LPAD(@rank, 2, '0')) END AS accountname FROM ( SELECT customproperty6, userkey, LOWER(CASE WHEN LENGTH(SUBSTRING_INDEX(customproperty6, '@', 1)) <= 15 THEN SUBSTRING_INDEX(customproperty6, '@', 1) ELSE LEFT(SUBSTRING_INDEX(customproperty6, '@', 1), 15) END) AS accountname FROM users WHERE LENGTH(customproperty6) > 0 ORDER BY accountname, userkey) subquery CROSS JOIN (SELECT @rank := 0, @prev := '') var ) finalquery WHERE userkey = ${requestee} ORDER BY userkey;

we used the above query to populate the customized account name in Ars page as dynamic attributes the query is working fine in the data analyzer and as well in analytics, but it is not working in dynamic attribute in Ars page. 
can anyone help us in why this query is not working as expected
Dynamic attribute type: single select form SQL query
I also ran the sync jobs it is changing the label names if i make it bold,italic but its not populating the value which is expected as single drowdown value.

thanks in advance

[rushikeshvartak]

Could you kindly provide a detailed snapshot of the information extracted from the logs, encompassing errors and other pertinent functionality details encountered during the execution of this process? Your assistance in furnishing this information would greatly aid in the analysis and resolution of any issues .



‼️‼️⚠️Do not upload any attachments that contain sensitive information, such as IP Addresses, URLs, Company/Employee Names, Email Addresses, etc.⚠️‼️‼️

[Mahendran]

Hi 
this is what we are received as a errors in Log

Exception while executing query|java.lang.IllegalArgumentException: org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [select count(distinct ID) as ID from (SELECT DISTINCT(accountname) as ID FROM ( SELECT subquery.customproperty6, subquery.userkey, @rank := IF(@prev = subquery.accountname, @rank + 1, 1) AS rank, @prev := subquery.accountname, CASE WHEN @rank = 1 THEN subquery.accountname ELSE CONCAT(subquery.accountname, LPAD(@rank, 2, '0')) END AS accountname FROM ( SELECT customproperty6, userkey, LOWER( CASE WHEN LENGTH(SUBSTRING_INDEX(customproperty6, '@', 1)) <= 15 THEN SUBSTRING_INDEX(customproperty6, '@', 1) ELSE LEFT(SUBSTRING_INDEX(customproperty6, '@', 1), 15) END ) AS accountname FROM users WHERE LENGTH(customproperty6) > 0 ORDER BY accountname, userkey ) AS subquery CROSS JOIN (SELECT @rank := 0, @prev := '') AS var ) AS finalquery WHERE userkey =7 ORDER BY userkey) as T where TRIM(T.ID) != '' order by ID]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.getNativeQueryImplementor(AbstractSharedSessionContract.java:1012) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createNativeQuery(AbstractSharedSessionContract.java:931) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createNativeQuery(AbstractSharedSessionContract.java:104) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]|Caused by: org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [select count(distinct ID) as ID from (SELECT DISTINCT(accountname) as ID FROM ( SELECT subquery.customproperty6, subquery.userkey, @rank := IF(@prev = subquery.accountname, @rank + 1, 1) AS rank, @prev := subquery.accountname, CASE WHEN @rank = 1 THEN subquery.accountname ELSE CONCAT(subquery.accountname, LPAD(@rank, 2, '0')) END AS accountname FROM ( SELECT customproperty6, userkey, LOWER( CASE WHEN LENGTH(SUBSTRING_INDEX(customproperty6, '@', 1)) <= 15 THEN SUBSTRING_INDEX(customproperty6, '@', 1) ELSE LEFT(SUBSTRING_INDEX(customproperty6, '@', 1), 15) END ) AS accountname FROM users WHERE LENGTH(customproperty6) > 0 ORDER BY accountname, userkey ) AS subquery CROSS JOIN (SELECT @rank := 0, @prev := '') AS var ) AS finalquery WHERE userkey =7 ORDER BY userkey) as T where TRIM(T.ID) != '' order by ID]| at org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:175) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.ParamLocationRecognizer.parseLocations(ParamLocationRecognizer.java:56) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.internal.NativeQueryInterpreterStandardImpl.getParameterMetadata(NativeQueryInterpreterStandardImpl.java:30) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.QueryPlanCache.getSQLParameterMetadata(QueryPlanCache.java:128) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.getNativeQueryImplementor(AbstractSharedSessionContract.java:1006) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createNativeQuery(AbstractSharedSessionContract.java:931) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]|


but the same query is working fine in both analytics and data analyzer

is there another way to achieve the below one is the requirement?

How to generate uniques Account Name rule:
Conditions are:

1)We have user.Customproperty which has emailid we need prefix part before @gmail.com

2)The account Name size should be 15 characters.

3)If Account Name as duplicates, we need to append two numeric characters starting with 02-99.

4)After appending the numbers, the account name size is 17 characters.

[rushikeshvartak]

Use trim