Enhanced Query Execution

[sherbert]

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;

[smitg]

Hi @sherbert ,

Try with below query

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

Thanks,
Smitha

[dgandhi]

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

[hgrandhi]

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

[hgrandhi]

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. 

[rushikeshvartak]

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;

[DixshantValecha]

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;