Sav4SAv DB Connector - Update Account Query issue

[IAM_99]

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'

[Darshanjain]

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

[Manu269]

Any specific reason why REST Based connector is not used?

I see you have mentioned EIC version 23.4

[IAM_99]

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)

[rushikeshvartak]

if hardcoding values works then you have issue in your query. In inner queries variable might not be exposed . Use joins