When I run the below query in Data Analyzer, it retrieves records, but when I copy and paste it in analytics SQL query, I get 0 results. Whats the difference between the two where I'm not able to retrieve results?
SELECT '1248289' AS entvaluekey, u.userkey AS userKey, u.username AS UserName, a.accountkey AS acctKey, a.name AS accName, u.customproperty32 AS CustomProperty32, e.endpointname AS EndpointName FROM users u JOIN user_accounts ua ON u.userkey = ua.userkey JOIN accounts a ON a.accountkey = ua.accountkey JOIN endpoints e ON a.endpointkey = e.endpointkey WHERE e.endpointname = 'Active Directory' AND u.userkey IN ( SELECT DISTINCT u.userkey FROM users u JOIN user_accounts ua ON u.userkey = ua.userkey JOIN accounts a ON a.accountkey = ua.accountkey WHERE a.endpointkey = 4954 AND a.status IN ('1','Manually Provisioned') ) AND a.name = u.customproperty32 AND NOT EXISTS ( SELECT 1 FROM account_entitlements1 ae WHERE ae.accountkey = a.accountkey AND ae.entitlement_valuekey = 1248289 )
Preview should be fine as data will be manipulated or used when report is ran. Preview will be used once during query update which you can check via data analyzer.
Create duplicate analytics that will help
Have seen this in past, in preview it gives 0 result but when we do Run now , it gives actual result.
For us , creating a fresh new analytics report helped us to resolve the issue. Maybe you can give that a try.