Query not processed in Analytics

[igaravi]

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%'

[dgandhi]

What is the query suppose to do?

Thanks

[igaravi]

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.

[Sivagami]

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

[dgandhi]

Please try to limit the output of the query (limit 100) that way you can rule out timeout related error

Thanks

[rushikeshvartak]

I see query having wrong joins use where conditions in joins itself and retry

[DixshantValecha]

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%';

[igaravi]

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.

select b.name as AccountName,

        t.ADAccountName,

        t.UserDisplayName,

        t.UserName

from accounts b

join (select distinct upper(replace(a.name,'priv.','')) as ADAccountName,

        u.displayname as UserDisplayName,

        u.username as UserName

        from accounts a

        join user_accounts ua on a.accountkey = ua.accountkey

        join Users u on ua.userkey = u.userkey

        where a.endpointkey = 16

        and a.status=1

        and a.name REGEXP  '^[a-zA-Z.]+$'

) t

on substring_index(b.customproperty28, '|', -1) = t.ADAccountName

Where

        b.endpointkey = 147

        and b.name like 'HR%'

[dgandhi]

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

[igaravi · Accepted solution]

Thanks. I got the query to work by replacing REGEXP with "NOT LIKE".  Analytics is able to execute now.

select b.name as AccountName,

        t.ADAccountName,

        t.UserDisplayName,

        t.UserName

from accounts b

join (select distinct upper(replace(a.name,'priv.','')) as ADAccountName,

        u.displayname as UserDisplayName,

        u.username as UserName

        from accounts a

        join user_accounts ua on a.accountkey = ua.accountkey

        join Users u on ua.userkey = u.userkey

        where a.endpointkey = 16

        and a.status=1

        and (a.name not like '%(%' or a.name not like '%-%' or a.name not like '%_%' )

) t

on substring_index(b.customproperty28, '|', -1) = t.ADAccountName

Where

        b.endpointkey = 147

        and b.name like 'HR%'