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

sherbert
New Contributor
New Contributor

Attempting to use the new Enhanced Query Execution which appears to just hide doing inserts and updates by disguising them as select statements within the logs. I keep receiving a syntax error when I preview (which then results in the window closing and having to start all over...super great user experience right there!). I am attempting to change the status of a role that is stuck in pending due to other issues with this environment post 23.8 being installed. Here is what I have tried so far (also used a non-saviynt syntax checker with no errors). 

first attempt met with syntax error:

SELECT 1 AS ROLES_STATUS, AS ROLES__PRIMARYKEY FROM ROLES_STATUS where r.rolekey=287;

second attempt also met with syntax error:
SELECT 1 AS ROLES_STATUS, AS ROLES__PRIMARYKEY FROM ROLES where ROLEKEY = 287;

6 REPLIES 6

smitg
Regular Contributor III
Regular Contributor III

Hi @sherbert ,

Try with below query

SELECT 1 AS ROLES__STATUS, ROLEKEY AS ROLES__PRIMARYKEY FROM ROLES where ROLEKEY = 287;

Thanks,
Smitha

dgandhi
All-Star
All-Star

Prepare your query as per below format:

SELECT
<VALUE_TO_BE_INSERTED> AS <DESTINATION_TABLE_NAME>__<COLUMN_NAME1>, <VALUE_TO_BE_INSERTED> AS <DESTINATION_TABLE_NAME >__<COLUMN_NAME2>, <DESTINATION_TABLE.PRIMARYCOLUMN> AS <DESTINATION_TABLE_NAME>__PRIMARYKEY
FROM <SOURCE_TABLE_NAME>
JOIN <DESTINATION_TABLE_NAME> ON <SOURCE_TABLE_NAME>. COL1 = <DESTINATION_TABLE_NAME>.COL2
WHERE <any condition>;

Where,

<SOURCE_TABLE_NAME>: Specify the source table here

<VALUE_TO_BE_INSERTED>: Specify the SOURCE_TABLE.COLUMN or a literal value

<DESTINATION_TABLE_NAME: Specify the destination table here

<DESTINATION_TABLE_NAME>__PRIMARYKEY: This is mandatory for any update operation

 

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.

Hi Devang,

I tried to update one of the cp1 attribute with literal value on entitlement using "Enhanced Query Execution" job and it is updating everything in upper case characters.

Any solution to convert literal into lower case while updating the values.

Also, is there a way to execute multiple queries like these in a single run?

Sample query:
select '[audit, certify, compliance, saviynt]' as entitlement_values__customproperty1,entitlement_values.ENTITLEMENT_VALUEKEY AS entitlement_values__PRIMARYKEY FROM entitlement_values where ENTITLEMENT_VALUEKEY=132366;

Output:

This job updated cp1 as [AUDIT, CERTIFY, COMPLIANCE, SAVIYNT]

Expected value [audit, certify, compliance, saviynt]

Note: gave literal value in double quotes as well but no luck.

Thanks in Advance !

Harish Grandhi

I am able to resolve this myself. As per logs, Saviynt is converting entire query into upper case using UPPER() function. I had to user LOWER() function for the literal which I am trying to update. 

SELECT LOWER('[AUDIT, CERTIFY, COMPLIANCE, SAVIYNT]') AS ENTITLEMENT_VALUES__CUSTOMPROPERTY1,ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY AS ENTITLEMENT_VALUES__PRIMARYKEY FROM ENTITLEMENT_VALUES WHERE ENTITLEMENT_VALUEKEY=132366;


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

DixshantValecha
Saviynt Employee
Saviynt Employee

Hi @sherbert,

It seems like you are trying to use an enhanced query execution feature, but you're encountering syntax errors in your SQL queries.

First attempt,It should be like this:-

SELECT 1 AS ROLES_STATUS, r.rolekey AS ROLES__PRIMARYKEY FROM ROLES_STATUS WHERE r.rolekey = 287;

Second attempt,It should be like this:

SELECT 1 AS ROLES_STATUS, ROLEKEY AS ROLES__PRIMARYKEY FROM ROLES WHERE ROLEKEY = 287;