Use Case
We have a requirement to generate a random three-letter system username while excluding the following keywords: 'ADD', 'ALL', 'AND', 'ANY', 'ASC', 'END', etc
Pre-requisites
N/A
Applicable Version(s)
202x and 3.x
Solution
This use case can be achieved using inline processing by configuring the below query in the MODIFYUSERDATAJSON at the user import source (Connected Application, Schema Import, CSV Import)
#MODIFYUSERDATAJSON:
{ "ADDITIONALTABLES" : { "USERS" : "SELECT USERKEY,USERNAME,EMPLOYEEID,SYSTEMUSERNAME FROM USERS" }, "COMPUTEDCOLUMNS" : [ "OWNER","STATUSKEY","STARTDATE","MANAGER","SYSTEMUSERNAME","CUSTOMPROPERTY3","DISPLAYNAME"], "PREPROCESSQUERIES" : [
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME = (SELECT random_word FROM (SELECT SUBSTRING(CONCAT(CHAR(FLOOR(RAND() * 26) + 65),CHAR(FLOOR(RAND() * 26) + 65),CHAR(FLOOR(RAND() * 26) + 65)), 1, 3) AS random_word) AS derived_table WHERE random_word NOT IN ('ADD', 'ALL', 'AND', 'ANY', 'ASC', 'END'))"]}
Attaching the recording using the CSV upload for reference
