Query working in Data Analyzer but not working in Analytics

[Gopinath3]

Hi Team,

Getting below error while running the query in Analytics but its working as expected in Data Analyzer.
Error: Only underscore('_') is allowed in dynamic attribute names

We expect the output to be in the following format: [ {"Id" : A18XXXM} {"Id" : V18XXXK} ] 

Please assist us with the query, as we suspect there may be an issue with the CONCAT condition.

We are using below query:

Select 
Concat('[', 
Group_Concat( Distinct Concat('{"Id": ', Name, ' }') 
Separator ' , '), ']') As Account_Name
from
(SELECT 
   a.NAME
FROM 
  ACCOUNTS A 
  JOIN account_entitlements1 ae1 ON A.ACCOUNTKEY = ae1.ACCOUNTKEY 
  JOIN Entitlement_values ev ON ev.entitlement_valuekey = ae1.entitlement_valuekey
WHERE
  ev.ENTITLEMENT_VALUE ='CN=ADB-Users,XXX,DC=com' 
Union
SELECT 
   a.NAME
FROM 
  ACCOUNTS A 
  JOIN account_entitlements1 ae1 ON A.ACCOUNTKEY = ae1.ACCOUNTKEY 
  JOIN Entitlement_values ev ON ev.entitlement_valuekey = ae1.entitlement_valuekey
WHERE
  a.Name ='BC32') AS Account_Name

@Akilan 

Thanks,

Gopinath

[rushikeshvartak · Accepted solution]

{ is treated as dynamic value in analytics

SELECT Concat('[', Group_concat(DISTINCT Concat('ccc"Id": ', name, ' cc') SEPARATOR
                   ' , ')
       , ']') AS Account_Name
FROM   (SELECT a.name
        FROM   accounts A
               JOIN account_entitlements1 ae1
                 ON A.accountkey = ae1.accountkey
               JOIN entitlement_values ev
                 ON ev.entitlement_valuekey = ae1.entitlement_valuekey
        WHERE  ev.entitlement_value = 'CN=ADB-Users,XXX,DC=com'
        UNION
        SELECT a.name
        FROM   accounts A
               JOIN account_entitlements1 ae1
                 ON A.accountkey = ae1.accountkey
               JOIN entitlement_values ev
                 ON ev.entitlement_valuekey = ae1.entitlement_valuekey
        WHERE  a.name = 'BC32') AS Account_Name 

[Akilan]

Hi @rushikeshvartak ,

Now query working in analytics. But we are not getting result as below.

We expect the output to be in the following format: [ {"Id" : A18XXXM} {"Id" : V18XXXK} ] 

can you suggest to getting result like above.