We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Enhanced query execution

SANDEEP1
New Contributor
New Contributor

UPDATE users
SET customproperty20 =
CASE
WHEN customproperty12 = 'employee' AND EXISTS (
SELECT attribute2 FROM dataset_values
WHERE customproperty11 LIKE CONCAT('%', attribute1, '%') AND datasetkey = 4
) THEN
(SELECT attribute2 FROM dataset_values
WHERE customproperty11 LIKE CONCAT('%', attribute1, '%') AND datasetkey = 4)
WHEN customproperty12 = 'contingent worker' AND EXISTS (
SELECT attribute2 FROM dataset_values
WHERE customproperty13 LIKE CONCAT('%', attribute1, '%') AND datasetkey = 3
) THEN
(SELECT attribute2 FROM dataset_values
WHERE customproperty13 LIKE CONCAT('%', attribute1, '%') AND datasetkey = 3)
ELSE 'OU=USERS,OU=NMHGEXT,DC=CORP'
END
WHERE customproperty8 = 'yes' AND customproperty20 IS NULL;

 

the above query is working in Customquery for old version, But showing syntax error in new Enhanced query execution.

 

Kindly help to resolve the issue

3 REPLIES 3

Manu269
All-Star
All-Star

@SANDEEP1 Job Categories for Flat Job Control Panel (saviyntcloud.com)

As per this doc :

It is recommended to use only one SELECT query for the selected operation.

Regards
Manish Kumar
If the response answered your query, please Accept As Solution and Kudos
.

I tried to update users Customproperty using select query in new EIC V23.11 environment. Error "INSERT OR UPDATE operation on USERS table is not allowed" is displayed.

Suggest the alternative way to update the user attributes using Custom query job.

Manu269
All-Star
All-Star

@SANDEEP1 

Restrictions:

You cannot perform any insert or update operation on the following tables:

  • Users

  • configuration

  • p_history

  • userlogin_Access

  • userloginAccess

  • qrtz_blob_triggers

  • qrtz_calendars

  • qrtz_cron_triggers

  • qrtz_fired_triggers

  • qrtz_job_details

  • qrtz_locks

  • qrtz_paused_trigger_grps

  • qrtz_scheduler_state

  • qrtz_simple_triggers

  • qrtz_simprop_triggers

  • qrtz_triggers

Regards
Manish Kumar
If the response answered your query, please Accept As Solution and Kudos
.