and more in a single search tool across platforms. Read the announcement here. |
05/31/2023 03:53 PM
Hi All,
We are using Sav4SAv DB connector in prod , trying to update query - below is the query, getting 'Error while Executing update query' exception , logs are not descriptive
Whats wrong with this query ?
UPDATE ACCOUNTS SET CUSTOMPROPERTY1 = CASE WHEN NAME IN (SELECT DISTINCT USERNAME FROM USER_SAVROLES us, USERS u, SAVROLES sr WHERE u.USERKEY = us.USERKEY AND us.ROLEKEY = sr.ROLEKEY AND sr.ROLENAME IN ('ROLE_XXXXX_NONEXEMPT','ROLE_XXXXX') AND u.SYSTEMUSERNAME NOT IN (SELECT DISTINCT NAME FROM ACCOUNT_ENTITLEMENTS1 ae1, ACCOUNTS a, ENTITLEMENT_VALUES ev WHERE ae1.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY AND ae1.ACCOUNTKEY = a.ACCOUNTKEY AND ev.ENTITLEMENT_VALUE IN ('CN=XXXXX-nonexempt,OU=XXXXX,OU=appgroups,OU=XXXXX,DC=XXXXX,DC=XXXXX,DC=com','CN=XXXXX,OU=XXXXX,OU=appgroups,OU=XXXXX,DC=XXXXX,DC=XXXXX,DC=com') AND a.ENDPOINTKEY IN (SELECT ENDPOINTKEY FROM ENDPOINTS WHERE ENDPOINTNAME = 'Active Directory'))) THEN 'Email Sent' ELSE '' end WHERE ENDPOINTKEY IN (SELECT ENDPOINTKEY FROM ENDPOINTS WHERE ENDPOINTNAME = 'Device Enrollment') AND STATUS IN ('1','Active','Manually Provisioned') AND NAME='e2005'
06/01/2023 07:00 AM
Hi @IAM_99
It looks like you are using multiple sub queries and joins within case statements, generally this is not allowed in SQL update statement.
try to write the query in different way as per your requirement.
Thanks
Darshan
06/01/2023 07:08 AM
Any specific reason why REST Based connector is not used?
I see you have mentioned EIC version 23.4
06/06/2023 02:10 PM
Yeah we have that plan to migrate to REST but as workaround am trying , infact ( REST also not solving all use cases ( update owners info through import) so prefered DB)
06/06/2023 02:53 PM
if hardcoding values works then you have issue in your query. In inner queries variable might not be exposed . Use joins