Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

How to write the Advance query to generate systemusername?

anujapawar
New Contributor
New Contributor

I want to create the system username under global configuration with following condition using advance query:-

a. first character of  firstname  and full  lastname. (EX. firstname=John, Lastname=Doe, username= JDoe)

b. handling duplicates with the auto increment starting with 1. (EX. username=JDoe2)

NOTE: 

1. Give preference to preferred firstname and preferred lastname if available.

2. the maximum length of the characters can be 20.

 

7 REPLIES 7

PremMahadikar
All-Star
All-Star

Hi @anujapawar ,

Try below, it should work:

CASE WHEN (preferedfirstname!='' OR preferedfirstname) THEN  left(concat(substring(preferedfirstname,1,1),replace(lastname,' ','')),20) else left(concat(substring(firstname,1,1),replace(lastname,' ','')),20) END 
### 
CASE WHEN (preferedfirstname!='' OR preferedfirstname) THEN left(concat(substring(preferedfirstname,1,1),replace(lastname,' ',''),FN_EIC_SEQGEN('increment')),20) else left(concat(substring(firstname,1,1),replace(lastname,' ',''),FN_EIC_SEQGEN('increment')),20) END 

For FN_EIC_SEQGEN, please refer this article for pre-configuration - Database Functions (saviyntcloud.com)

 

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos

@PremMahadikar  hi,

I also have preferedlastname which is to be given preference over lastname. Can you please give me the advance query wrt it included in the above mentioned query.

For reference, I am attaching the csv file ss for better understanding.

anujapawar_0-1713495922349.png

 

CASE
WHEN (preferedfirstname != '' OR preferedfirstname IS NOT NULL) THEN
LEFT(CONCAT(SUBSTRING(preferedfirstname, 1, 1), REPLACE(COALESCE(preferedlastname, lastname), ' ', ''), FN_EIC_SEQGEN('increment')), 20)
ELSE
LEFT(CONCAT(SUBSTRING(firstname, 1, 1), REPLACE(COALESCE(preferedlastname, lastname), ' ', ''), FN_EIC_SEQGEN('increment')), 20)
END


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Hi,

Here when I am using the FN_EIC_SEQGEN('increment') it gives me the following result.

Ex. firstname1: Bhavya, lastname1: Singh, systemusername: BSingh1

firstname2: Bhavna, lastname1: Singh, systemusername: BSingh2

firstname3: Ashi, lastname1: Paul, systemusername: APaul3

firstname4: Ashna, lastname1: Paul, systemusername: APaul4

whereas I am aiming for,

firstname1: Bhavya, lastname1: Singh, systemusername: BSingh

firstname2: Bhavna, lastname1: Singh, systemusername: BSingh1

firstname3: Ashi, lastname1: Paul, systemusername: APaul

firstname4: Ashna, lastname1: Paul, systemusername: APaul1

suggest me the apt solution for it!

 

This is know behavior with database functions fn sequence generator 


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

PremMahadikar
All-Star
All-Star

@anujapawar ,

We have similar requirement!

For this use case, do not use FN_EIC_SEQGEN('increment'). Try below code, it should work.

Note: I have just considered 5 iterations here, assuming lastname would be unique most of the times and wouldn't be having many duplicates. Please increase it, if you notice frequency of duplicates is high.

CASE WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname!='' OR preferedlastname is not null) THEN  left(concat(substring(preferedfirstname,1,1),replace(preferedlastname,' ','')),20) else left(concat(substring(firstname,1,1),replace(lastname,' ','')),20) END 
### 
CASE WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname!='' OR preferedlastname is not null) THEN concat(substring(preferedfirstname,1,1),substring(replace(preferedlastname,' ',''),1,LENGTH(left(replace(preferedlastname,' ',''),19))),1) else concat(substring(firstname,1,1),substring(replace(lastname,' ',''),1,LENGTH(left(replace(lastname,' ',''),19))),1) END
### 
CASE WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname!='' OR preferedlastname is not null) THEN concat(substring(preferedfirstname,1,1),substring(replace(preferedlastname,' ',''),1,LENGTH(left(replace(preferedlastname,' ',''),19))),2) else concat(substring(firstname,1,1),substring(replace(lastname,' ',''),1,LENGTH(left(replace(lastname,' ',''),19))),2) END
### 
CASE WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname!='' OR preferedlastname is not null) THEN concat(substring(preferedfirstname,1,1),substring(replace(preferedlastname,' ',''),1,LENGTH(left(replace(preferedlastname,' ',''),19))),3) else concat(substring(firstname,1,1),substring(replace(lastname,' ',''),1,LENGTH(left(replace(lastname,' ',''),19))),3) END
### 
CASE WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname!='' OR preferedlastname is not null) THEN concat(substring(preferedfirstname,1,1),substring(replace(preferedlastname,' ',''),1,LENGTH(left(replace(preferedlastname,' ',''),19))),4) else concat(substring(firstname,1,1),substring(replace(lastname,' ',''),1,LENGTH(left(replace(lastname,' ',''),19))),4) END
### 
CASE WHEN (preferedfirstname!='' OR preferedfirstname is not null) AND (preferedlastname!='' OR preferedlastname is not null) THEN concat(substring(preferedfirstname,1,1),substring(replace(preferedlastname,' ',''),1,LENGTH(left(replace(preferedlastname,' ',''),19))),5) else concat(substring(firstname,1,1),substring(replace(lastname,' ',''),1,LENGTH(left(replace(lastname,' ',''),19))),5) END

 

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos

PremMahadikar
All-Star
All-Star

@anujapawar ,

Did you try this?

Please close the thread if it's working. Thanks!

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos