and more in a single search tool across platforms. Read the announcement here. |
04/21/2023 01:32 PM
I have this query running fine in data analyzer but when I copy paste into Analytics (V2), I get a gateway timeout error. Looks like Analytics is not able to process this query.
select b.name as AccountName, a.displayname as AccountDisplayName, u.displayname as UserDisplayName, u.username as UserName, a.status as AccountStatus
from accounts b
Inner join accounts a on (substring_index(b.customproperty28,'|',-1) = upper(a.name))
left join user_accounts ua on a.accountkey = ua.accountkey
left join Users u on ua.userkey = u.userkey
where b.endpointkey=33
and a.endpointkey=3
and b.name like 'HR%'
Solved! Go to Solution.
04/21/2023 02:52 PM
What is the query suppose to do?
Thanks
04/21/2023 03:49 PM
The query retrieves Usernames from a correlated endpoint 3 and the same accounts are also being sourced from a different application into another uncorrelated Endpoint, 33 (as there is no data for correlation). The result set from the query will be downloaded and uploaded into 33 for manual correlation.
04/21/2023 05:36 PM
Query might run fine in data analyzer as the result set is limited by 100 records. Probably the query is timing out when you run it via analytics v2 due to which you are receiving timeout error.
-Siva
04/21/2023 06:10 PM
Please try to limit the output of the query (limit 100) that way you can rule out timeout related error
Thanks
04/23/2023 03:08 PM
I see query having wrong joins use where conditions in joins itself and retry
04/24/2023 07:11 AM
Please try this and let us know if this works for you
SELECT b.name AS AccountName,
a.displayname AS AccountDisplayName,
u.displayname AS UserDisplayName,
u.username AS UserName,
a.status AS AccountStatus
FROM accounts b
JOIN accounts a ON substring_index(b.customproperty28, '|', -1) = upper(a.name)
JOIN user_accounts ua ON a.accountkey = ua.accountkey
JOIN Users u ON ua.userkey = u.userkey
WHERE b.endpointkey = 33
AND a.endpointkey = 3
AND b.name LIKE 'HR%';
04/24/2023 09:39 AM
Thanks. I refined to query by adding where clauses and also to avoid cartesian joins. This query runs in data analyzer but fails in Analytics. Is REGEXP supported in Analytics? I want to include only accounts that have characters with or without a period. I believe Analytics is complaining about the charachters in REGEXP.
04/24/2023 10:26 AM
Is REGEXP supported in Analytics -- Yes it is supported
$ in your regexp is the one which is giving issue in your case. $ is not valid special character in the Analytics Query. Check the information icon next to the Analytics Query
Thanks
04/24/2023 11:23 AM
Thanks. I got the query to work by replacing REGEXP with "NOT LIKE". Analytics is able to execute now.