converting a sql query into an Enhanced Execution query

[julfikar]

can anyone help me in converting below sql query into an Enhanced Execution Query?

Needed urgently.

update ZYZ set rolekey=(select max(accentkey) from account_entitlements1); update ABC set rolekey=(select max(accountkey) from accounts);

[Raghu]

@julfikar  can you prvide proper tables ? ZYZ and ABC which is not avaible is saviynt. you means Roles table those? and if any where cause using or not?

your query updating rolekey column it is a primary key we can't update.

[julfikar]

Hi Raghu,

Below is the entire sql query.

update OOBE set rolekey=(select max(accentkey) from account_entitlements1); update ooba set rolekey=(select max(accountkey) from accounts);

[Raghu]

OOBE table is available in saviynt DB?

I belive it not part saviynt db table but if consider it will convert like below

SELECT (select max(accountkey) from accounts) AS OOBE__rolekey, r.rolekey as OOBE__primarykey from OOBE

[julfikar]

Yes its available

[julfikar]

you can assume jeew as primary key for OOBE table.

thanks

[Raghu · Accepted solution]

Below EH query:

SELECT (select max(accentkey) from account_entitlements1) AS OOBE__rolekey,r.jeew as OOBE__primarykey from OOBE r

SELECT (select max(accountkey) from accounts) AS OOBE__rolekey,r.jeew as OOBE__primarykey from OOBE r

[julfikar]

Thanks Raghu for you help.

[julfikar]

Need one more help,

Can u convert below query also into an EH query also, much needed.

thanks

UPDATE arstasks ar,

accounts acc,

endpoints ep

SET

ar.PROVISIONINGCOMMENTS = CONCAT(COALESCE(ar.PROVISIONINGCOMMENTS, ''),

'System closing task as account has been identified as a secondary AD account'),

ar.STATUS = 4

WHERE

ar.tasktype in (12,6)

AND ar.accountkey = acc.accountkey

AND acc.endpointkey = ep.endpointkey

AND ep.ENDPOINTNAME = 'AD.SHARED'

AND ar.STATUS = 1

AND acc.name LIKE '%#_%' ESCAPE '#';

[Raghu · Accepted solution]

SELECT CONCAT(COALESCE(ar.PROVISIONINGCOMMENTS, ''),'System closing task as account has been identified as a secondary AD account') AS ARSTASKS__PROVISIONINGCOMMENTS,AR.TASKKEY AS ARSTASKS__PRIMARYKEY FROM arstasks AR,accounts acc,endpoints ep WHERE
ar.tasktype in (12,6) AND ar.accountkey = acc.accountkey
AND acc.endpointkey = ep.endpointkey
AND ep.ENDPOINTNAME = 'AD.SHARED'
AND ar.STATUS in (1,4) AND acc.name LIKE '%#_%' ESCAPE '#'

[julfikar]

thanks so much Raghu