Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
04/18/2024 01:07 AM
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.
04/18/2024 02:08 AM
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
04/18/2024 08:05 PM
@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.
04/18/2024 08:09 PM
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
04/21/2024 09:39 PM
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!
04/22/2024 02:13 AM
This is know behavior with database functions fn sequence generator
04/22/2024 03:32 AM
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
04/24/2024 04:19 AM
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