Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Preprocessor Query to Change Title

aarthianand93
New Contributor II
New Contributor II

Hi Team,

We are trying to change Title based on the following condition:

If customproperty = curdate then change title else title remains same. 

We have a tried with couple of pre-processor queries, however, the condition for the title to remain unchanged is failing. If cp does not match curdate it is making title as blank. Please let us know if this query can be modified in anyway to change the title only when cp matches the curdate. And if it does not match then title must be unchanged. 

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM users"
},
"COMPUTEDCOLUMNS": ["TITLE", "CUSTOMPROPERTY26"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.TITLE = IF(NU.CUSTOMPROPERTY26 = CURDATE(), NU.TITLE, CU.TITLE)"
]
}

Regards,

Aarthi Anand

10 REPLIES 10

rushikeshvartak
All-Star
All-Star

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM users"
},
"COMPUTEDCOLUMNS": ["TITLE", "CUSTOMPROPERTY26"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.TITLE = IF(NU.CUSTOMPROPERTY26 = CURDATE(), NU.TITLE, COALESCE(CU.TITLE, NU.TITLE))"
]
}


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

Hi @rushikeshvartak ,

We tried with this query. The title is changed even when curdate does not match cp value.

The requirement is the title must remain unchanged until the future date is reached.

Please check on this. 

Regards,

Aarthi Anand

 

NM
Valued Contributor
Valued Contributor

Hi @aarthianand93 , is the value been imported in the recon job.? as for future one it will be existing in Users table

try this

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM users"
},
"COMPUTEDCOLUMNS": ["TITLE", "CUSTOMPROPERTY26"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.TITLE = (SELECT CASE WHEN CU.CUSTOMPROPERTY26 = CURDATE() THEN NU.TITLE ELSE CU.TITLE)"
]
}

aarthianand93
New Contributor II
New Contributor II

Hi @NM, we are testing with CSV upload. When we mention a future date the title must remain unchanged. If we mention current date then the title must be changed. 

The query you have provided is not updating the title for both current and future date. 

Regards,

Aarthi Anand

NM
Valued Contributor
Valued Contributor

try this

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM users"
},
"COMPUTEDCOLUMNS": ["TITLE", "CUSTOMPROPERTY26"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.TITLE = (SELECT CASE WHEN NU.CUSTOMPROPERTY26 = CURDATE() THEN NU.TITLE ELSE CU.TITLE END)"
]
}

I hope your CSV file has customproperty26 column

aarthianand93
New Contributor II
New Contributor II

Hi @NM,

We have tried multiple combinations but it is not working. 

We tried the query you have provided and yes we are passing the current date in customproperty26 through csv. But title is not changing for current date. 

Regards,

Aarthi Anand

Hi @NM, and @rushikeshvartak,

We are seeing date format issue. We mentioned yy-mm-dd in the csv, however, Saviynt pics it up as DD-MM-YY from the CSV. What is the correct format for this? How can we change in the query?

[1:51 PM] Deepa Vimal Raj

Data truncation: Truncated incorrect date value: '05-06-2024'"
"2024-06-05T08:19:19.499+00:00","ecm","services.ImportSAvDataUserService","http-nio-8080-exec-629-qtq4f","ERROR","Error while processing data: "
"2024-06-05T08:19:19.690+00:00","ecm","","null-qtq4f","","com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Truncated incorrect date value: '05-06-2024'"

 

Regards,

Aarthi Anand

NM
Valued Contributor
Valued Contributor

Can you share your CSV file once?

plus try the format mentioned in my previous comment once.

NM
Valued Contributor
Valued Contributor

Hi @aarthianand93 ,

This is what curdate function gives as an output, Try changing the format to below.

NM_0-1717573602038.png

 

NM
Valued Contributor
Valued Contributor

Hi @aarthianand93 , could you please share the final query which will help others.

Thanks