Hi @indra_hema_95 ,
Solution would be to also check the temp table (NEWUSERDATA) for uniqueness.
Below is the sample JSON: (Please use it as reference)
{
"ADDITIONALTABLES": {
"USERS" : "SELECT userkey,systemusername,username,firstname,lastname FROM USERS"
},
"COMPUTEDCOLUMNS" :[
"username",
],
"PREPROCESSQUERIES" : [
"UPDATE NEWUSERDATA set NEWUSERDATA.username=case
when (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) in (select u.username from currentusers u)=0) AND (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname) in (select CONCAT(n.firstname,'.',n.lastname) from NEWUSERDATA n group by CONCAT(n.firstname,'.',n.lastname) having count(*)>1)=1) then CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname)
when (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'1') in (select u.username from currentusers u)=0) AND (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'1') in (select CONCAT(n.firstname,'.',n.lastname,'1') from NEWUSERDATA n group by CONCAT(n.firstname,'.',n.lastname,'1') having count(*)>1)=0) then CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname)
when (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'2') in (select u.username from currentusers u)=0) AND (CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'2') in (select CONCAT(n.firstname,'.',n.lastname,'2') from NEWUSERDATA n group by CONCAT(n.firstname,'.',n.lastname,'2') having count(*)>1)=0) then CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname)
ELSE CONCAT(NEWUSERDATA.firstname,'.',NEWUSERDATA.lastname,'3') END"
]
}
If this helps your question, please consider select Accept As Solution and hit Kudos
