Click HERE to see how Saviynt Intelligence is transforming the industry. |
04/18/2024 10:36 PM - edited 04/18/2024 10:37 PM
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",
04/21/2024 08:08 PM
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
04/24/2024 07:43 AM
Does this check uniqueness of systemusername against existing users?
04/24/2024 08:44 PM
systemusername does not allowed duplicate hence its checked
04/25/2024 07:32 AM
Does it check against the systemusername generation rule or does uniqueness conditions need to be added to inline preprocessor queries?
04/25/2024 08:55 PM
Its automatically taken care internally in database
05/02/2024 01:24 PM
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
05/02/2024 07:24 PM
Does it mean 2 user have same systemusername?
05/03/2024 05:27 AM
Yes that is what I am observing
05/04/2024 08:27 AM
Refer below sample
05/06/2024 02:05 PM
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?
05/06/2024 06:23 PM
Check latest reply https://forums.saviynt.com/t5/identity-governance/generate-the-systemusername-amp-validate-with-user...
05/07/2024 12:41 PM
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"
05/07/2024 03:19 PM
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
05/08/2024 09:01 AM
For some reason, even with these updates I am seeing it go to the else statement still
05/08/2024 08:48 PM
Please share JSON
05/09/2024 07:47 AM
{
"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"
]
}
05/09/2024 07:20 PM
Can you validate same in data analyzer first
05/10/2024 09:45 AM
In data analyzer I am seeing it skip to else statement as well
05/10/2024 09:46 AM
Then fix logic in data analyzer Please share query
05/10/2024 10:14 AM
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
05/10/2024 10:50 AM
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'
05/14/2024 04:38 AM
"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?
05/14/2024 08:39 PM
use mysql if condition
05/15/2024 04:57 AM
This is the same as using a case statement as we need multiple conditions (elseif)
05/15/2024 06:17 AM
You can have multiple conditions in every else