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

Systemusername Uniquness Check in preprocessor

IGAQ
New Contributor
New Contributor

Hello All,

We are generating unique systemusername  in preprocessor where we need to connect to accounts table ( for AD Accounts), using below query (not even added Accounts table , just checking only users table)- but its throwing an error  "java.sql.SQLException: You can't specify target table 'NU' for update in FROM clause" - Is it possible like below ?any inputs/alternatives on this ?

=================

"UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU ON NU.USERNAME = NU.USERNAME SET NU.SYSTEMUSERNAME = CASE WHEN ( (SELECT CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) ,MAX(CAST(REPLACE(REVERSE(CAST( REVERSE(NU.SYSTEMUSERNAME) AS SIGNED) )+1,'.0','') AS UNSIGNED))) FROM CURRENTUSERS CU WHERE NU.USERNAME LIKE 'EMPC%' AND NU.SYSTEMUSERNAME IS NOT NULL AND NU.SYSTEMUSERNAME LIKE CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME),'%')) !='' ) THEN(SELECT CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) ,MAX(CAST(REPLACE(REVERSE(CAST( REVERSE(NU.SYSTEMUSERNAME) AS SIGNED) )+1,'.0','') AS UNSIGNED))) FROM NEWUSERDATA CU WHERE NU.USERNAME LIKE 'XYZ%' AND NU.SYSTEMUSERNAME IS NOT NULL AND NU.SYSTEMUSERNAME LIKE CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME),'%')) ELSE CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) END LIMIT 1",

25 REPLIES 25

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @IGAQ,

The join condition in the query is incorrect.You are using NU.USERNAME = NU.USERNAME, It should be NU.USERNAME = CU.USERNAME.

"UPDATE NEWUSERDATA NU 
INNER JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME 
SET NU.SYSTEMUSERNAME = CASE 
    WHEN ( 
        (SELECT CONCAT(
            CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) ,
            MAX(CAST(REPLACE(REVERSE(CAST( REVERSE(NU.SYSTEMUSERNAME) AS SIGNED) )+1,'.0','') AS UNSIGNED))
        ) 
        FROM CURRENTUSERS CU 
        WHERE NU.USERNAME LIKE 'EMPC%' AND NU.SYSTEMUSERNAME IS NOT NULL AND NU.SYSTEMUSERNAME LIKE CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME),'%')) !='' 
    ) THEN(
        SELECT CONCAT(
            CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) ,
            MAX(CAST(REPLACE(REVERSE(CAST( REVERSE(NU.SYSTEMUSERNAME) AS SIGNED) )+1,'.0','') AS UNSIGNED))
        ) 
        FROM NEWUSERDATA CU 
        WHERE NU.USERNAME LIKE 'XYZ%' AND NU.SYSTEMUSERNAME IS NOT NULL AND NU.SYSTEMUSERNAME LIKE CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME),'%')
    ) ELSE CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) 
END LIMIT 1",

Thanks



If you find the above response useful, Kindly Mark it as "Accept As Solution".

nvachhani
Regular Contributor
Regular Contributor

Does this check uniqueness of systemusername against existing users?

systemusername does not allowed duplicate hence its checked 


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

nvachhani
Regular Contributor
Regular Contributor

Does it check against the systemusername generation rule or does uniqueness conditions need to be added to inline preprocessor queries? 

Its automatically taken care internally in database


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

I tested this and observed unique systemusername is not generated. Via CSV upload I changed name of user to same name of another user and systemusername generated from inline preprocessor was the same.

UPDATE NEWUSERDATA INNER JOIN currentusers ON currentusers.username = newuserdata.username SET newuserdata.systemusername = CASE WHEN newuserdata.firstname != currentusers.firstname THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) WHEN newuserdata.lastname != currentusers.lastname THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) ELSE currentusers.systemusername END

Does it mean 2 user have same systemusername?


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

nvachhani
Regular Contributor
Regular Contributor

Yes that is what I am observing

Refer below sample

https://forums.saviynt.com/t5/identity-governance/generate-the-systemusername-amp-validate-with-user...


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

nvachhani
Regular Contributor
Regular Contributor

Even with that sample I am still seeing a duplicate being created, is there a setting I am missing or is there an issue with the environment that it is allowing duplicate systemusernames? 

Check latest reply https://forums.saviynt.com/t5/identity-governance/generate-the-systemusername-amp-validate-with-user... 


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

nvachhani
Regular Contributor
Regular Contributor

This is better, but same issue that user is having, it is skipping to the ELSE statement instead of evaluating every WHEN statement:

 

"UPDATE NEWUSERDATA SET NEWUSERDATA.systemusername=CASE WHEN (LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) IN (SELECT u.systemusername from currentusers u)=0) THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', ''))
WHEN (LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,2),CUSTOMPROPERTY29),1, 20), ' ', '')) IN (SELECT u.systemusername from currentusers u)=0) THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,2),CUSTOMPROPERTY29),1, 20), ' ', ''))
WHEN (LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,3),CUSTOMPROPERTY29),1, 20), ' ', '')) IN (SELECT u.systemusername from currentusers u)=0) THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,3),CUSTOMPROPERTY29),1, 20), ' ', ''))
WHEN (LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,4),CUSTOMPROPERTY29),1, 20), ' ', '')) IN (SELECT u.systemusername from currentusers u)=0) THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,4),CUSTOMPROPERTY29),1, 20), ' ', ''))
ELSE LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,5),CUSTOMPROPERTY29),1, 20), ' ', '')) END"

Hi @nvachhani ,

This should work, maybe few changes in the code:

1. Have a simple file with less columns

2. Run below query in data analyzer and check the results 

 

select LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20),' ','')) from users​

 

3. To match/check in existing systemusername, use 

 

SELECT lower(u.systemusername) from currentusers u

 

4. If first iteration works, then increase more.

 

If this helps your questions, please consider selecting Accept As Solutions and hit Kudos

nvachhani
Regular Contributor
Regular Contributor

For some reason, even with these updates I am seeing it go to the else statement still

Please share JSON


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

nvachhani
Regular Contributor
Regular Contributor

{
"ADDITIONALTABLES": {
"USERS": "SELECT userkey, systemusername, CUSTOMPROPERTY27, CUSTOMPROPERTY29 from USERS"
},
"COMPUTEDCOLUMNS": [
"systemusername"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.systemusername=CASE WHEN (LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) IN (SELECT LOWER(u.systemusername) from currentusers u)=0) THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) WHEN (LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,2),CUSTOMPROPERTY29),1, 20), ' ', '')) IN (SELECT LOWER(u.systemusername) from currentusers u)=0) THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,2),CUSTOMPROPERTY29),1, 20), ' ', '')) ELSE LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,5),CUSTOMPROPERTY29),1, 20), ' ', '')) END"
]
}

Can you validate same in data analyzer first


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

nvachhani
Regular Contributor
Regular Contributor

In data analyzer I am seeing it skip to else statement as well

Then fix logic in data analyzer Please share query


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

nvachhani
Regular Contributor
Regular Contributor

Here is the query I am testing in the data analyzer with, the result skips to the else statement:

select CASE WHEN (LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) IN (SELECT LOWER(u.systemusername) from users u)=0) THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,1),CUSTOMPROPERTY29),1, 20), ' ', '')) WHEN (LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,2),CUSTOMPROPERTY29),1, 20), ' ', '')) IN (SELECT LOWER(u.systemusername) from users u)=0) THEN LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,2),CUSTOMPROPERTY29),1, 20), ' ', '')) ELSE LOWER(REPLACE(SUBSTRING(CONCAT(SUBSTRING(CUSTOMPROPERTY27,1,5),CUSTOMPROPERTY29),1, 20), ' ', '')) END from users where username = 999019

@nvachhani ,

This will help you how to debug: I have hardcoded to explain the below:

1. Remove LOWER - SQL is case insensitive

2. Pick the first iteration: SELECT REPLACE(SUBSTRING(CONCAT(SUBSTRING('Prem',1,1),'Mahadikar2000'),1, 20), ' ', '')

Result - pmahadikar2000

Check if the above result is present in - SELECT distinct u.systemusername from users u

Result - If its present (exact match), it always goes to else because of '=0'

 

nvachhani
Regular Contributor
Regular Contributor


"Result - If its present (exact match), it always goes to else because of '=0'"

 

I guess I am asking how we can get it to go to the next case statement without going to else if the systemusername already exists?

 

 

use mysql if condition

IF(condition, value_if_true, value_if_false)
 

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

nvachhani
Regular Contributor
Regular Contributor

This is the same as using a case statement as we need multiple conditions (elseif)

You can have multiple conditions in every else


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