Systemusername Uniquness Check in preprocessor

[IGAQ]

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",

[sudeshjaiswal]

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

[nvachhani]

Does this check uniqueness of systemusername against existing users?

[rushikeshvartak]

systemusername does not allowed duplicate hence its checked 

[nvachhani]

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

[rushikeshvartak]

Its automatically taken care internally in database

[nvachhani]

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

[rushikeshvartak]

Does it mean 2 user have same systemusername?

[nvachhani]

Yes that is what I am observing

[rushikeshvartak]

Refer below sample

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

[nvachhani]

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? 

[rushikeshvartak]

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

[nvachhani]

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"

[PremMahadikar]

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]

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

[rushikeshvartak]

Please share JSON

[nvachhani]

{
"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"
]
}

[rushikeshvartak]

Can you validate same in data analyzer first

[nvachhani]

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

[rushikeshvartak]

Then fix logic in data analyzer Please share query

[nvachhani]

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

[PremMahadikar]

@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]

---

---

"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?

[rushikeshvartak]

use mysql if condition

IF(condition, value_if_true, value_if_false)

[nvachhani]

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

[rushikeshvartak]

You can have multiple conditions in every else