Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

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
Valued Contributor
Valued Contributor

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 you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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 you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

PremMahadikar
Valued Contributor
Valued Contributor

@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
Valued Contributor
Valued Contributor

@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