Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Account_Entitlements1 Invalid SQL Syntax Error in Certification Configuration

cedric
New Contributor
New Contributor

Can someone please help me understand why I am getting an "Invalid SQL Syntax Error" message when attempting to use the query below in the Account Entitlements1 Query section of an Entitlement Owner certification campaign?

Account Entitlements1 Query
(ae1.startdate < '2024-03-11 00:00:00') and ae1.accountkey in (select accountkey from accounts where status in ('1', 'Active', 'Manually Provisioned', '2') AND name NOT IN ('ServiceAccountA', 'ServiceAccountB, 'ServiceAccountC', 'ServiceAccountD', 'ServiceAccountE', 'ServiceAccountF', 'ServiceAccountG', 'ServiceAccountH'))

Below are other queries I am using in the configuration:

Entitlements Query
(ENTITLEMENTTYPEKEY=101 and (ENTITLEMENT_VALUEKEY in (select ENTITLEMENT_VALUEKEY from account_entitlements1) or ENTITLEMENT_VALUEKEY in (select ENTITLEMENT_VALUE1KEY from entitlements2 where ENTITLEMENT_VALUE2KEY in (select ENTITLEMENT_VALUEKEY from account_entitlements1)))) or ((entitlement_value LIKE 'ABCDE') AND ENTITLEMENTTYPEKEY=14 AND (ENTITLEMENT_VALUEKEY in (select ENTITLEMENT_VALUEKEY from account_entitlements1) or ENTITLEMENT_VALUEKEY in (select ENTITLEMENT_VALUE1KEY from entitlements2 where ENTITLEMENT_VALUE2KEY in (select ENTITLEMENT_VALUEKEY from account_entitlements1))))

Consult User Query
select u from Users u where 1=1 and u.username not in ('admin', 'systemadmin', 'SaviyntSupportAgent1', 'saviyntsupportagent2', 'saviyntsupportagent3')

7 REPLIES 7

rushikeshvartak
All-Star
All-Star

Error 

-accounts is not mapped [Select count(*) from com.saviynt.ecm.identitywarehouse.domain.Account_entitlements1 ae1 where (ae1.startdate < '2024-03-11 00:00:00') and ae1.accountkey in (select accountkey from accounts where status in ('1', 'Active', 'Manually Provisioned', '2') AND name NOT IN ('ServiceAccountA', 'ServiceAccountB', 'ServiceAccountC', 'ServiceAccountD', 'ServiceAccountE', 'ServiceAccountF', 'ServiceAccountG', 'ServiceAccountH'))]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: accounts is not mapped [Select count(*) from com.saviynt.ecm.identitywarehouse.domain.Account_entitlements1 ae1 where (ae1.startdate < '2024-03-11 00:00:00') and ae1.accountkey in (select accountkey from accounts where status in ('1', 'Active', 'Manually Provisioned', '2') AND name NOT IN ('ServiceAccountA', 'ServiceAccountB', 'ServiceAccountC', 'ServiceAccountD', 'ServiceAccountE', 'ServiceAccountF', 'ServiceAccountG', 'ServiceAccountH'))]

 

In this corrected query, I added a closing single quote after 'ServiceAccountB', and also added commas between each service account name in the NOT IN list for clarity.


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

rushikeshvartak
All-Star
All-Star

Use :

rushikeshvartak_0-1712254765834.png

Accounts Query : 

status in ('1', 'Active', 'Manually Provisioned', '2') AND name NOT IN ('ServiceAccountA', 'ServiceAccountB', 'ServiceAccountC', 'ServiceAccountD', 'ServiceAccountE', 'ServiceAccountF', 'ServiceAccountG', 'ServiceAccountH')

Account Entitlements1 Query

(ae1.startdate < '2024-03-11 00:00:00')

 

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Replying back to your top comment regarding the error - that was a manual error on my part when I was trying to generalize the query to remove the actual service account names. From what i'm understanding, you're saying Accounts is not mapped to AE1 so it's impossible to take the approach using my original query?

Regarding this specific comment - Originally I was using this approach you mentioned but it causes the campaign to take over 25+ minutes to launch because it's cycling through millions of ae1 records for each entitlement I am filtering by (270+ entitlements total). I was trying to optimize the approach by combining the two queries into one and putting it into the ae1 query filter.

With specifying accounts query, how many records are included in the campaign.

As if there are millions of records then in that case if it takes 25+ mins to launch then it should be good.

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

cedric
New Contributor
New Contributor

It's millions of lines in the AE1 table, not the certification. The certification we should have just under 4000 accounts.

If you have performance issue with Elastic search you can raise support ticket 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

What is total line items you are getting from data analyzer?


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.