How to write the Advance query to generate systemusername?

[anujapawar]

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.

[PremMahadikar]

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

[anujapawar]

@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.

[rushikeshvartak]

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

[anujapawar]

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!

[rushikeshvartak]

This is know behavior with database functions fn sequence generator 

[PremMahadikar]

@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]

@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