10/14/2022 09:09 AM
Hello,
I have a use case where systemusername should be generated based on some conditions:
Conditions # 1 If country is "Singapore" and employee type is "Employee"....Systemusername should be
<Emp><firstname intital><sing><1>
here <1> should be incremented if first name initial is repeated.
like :
<Emp><firstname intital><sing><1>
<Emp><firstname intital><sing><2>.....and so on
Conditions #2 If country is "Singapore" and employee type is "Contract"....Systemusername should be
<Cont><firstname intital><sing><1>
here <1> should be incremented if first name initial is repeated.
like :
<Cont><firstname intital><sing><1>
<Cont><firstname intital><sing><2>.....and so on
I have tried following but doesn't work:
CASE WHEN ((users.country like '%Singapore%') and (users.employeetype like '%Employee%' )) then concat('Emp',LEFT(users.firstname,1),'sing','1') END ###
CASE WHEN ((users.country like '%Singapore%') and (users.employeetype like '%Employee%' )) then concat('Emp',LEFT(users.firstname,1),'sing','2') END### .......and so on
CASE WHEN ((users.country like '%Singapore%') and (users.employeetype like '%Contract%' )) then concat('Cont',LEFT(users.firstname,1),'sing','1') END ###
CASE WHEN ((users.country like '%Singapore%') and (users.employeetype like '%Contract%' )) then concat('Emp',LEFT(users.firstname,1),'sing','2') END ###......and so o
10/14/2022 09:43 AM
Please try # as a separator
10/14/2022 11:27 AM
Hello Rushikesh,
I have tried # as well but it is still not going through.
10/14/2022 11:41 AM
concat(users.firstname , substring('.',1,1) , users.lastname) ### CASE WHEN (users.middlename is NOT NULL) THEN concat(users.firstname , substring('.',1,1) , substring(users.middlename,1,1) , substring('.',1,1) , users.lastname) END ### concat(users.firstname , substring('.',1,1) , users.lastname , substring('1',1,1))### concat(users.firstname , substring('.',1,1) , users.lastname , substring('2',1,1))### concat(users.firstname , substring('.',1,1) , users.lastname , substring('3',1,1)
CASE WHEN((users.country like '%Singapore%') and (users.employeetype like '%Employee%' )) then concat('Emp',substring(users.firstname,1,1),'sing','1') END ###
CASE WHEN((users.country like '%Singapore%') and (users.employeetype like '%Employee%' )) then concat('Emp',substring(users.firstname,1,1),'sing','2') END ###
CASE WHEN((users.country like '%Singapore%') and (users.employeetype like '%Contract%' )) then concat('Con',substring(users.firstname,1,1),'sing','1') END ###
CASE WHEN((users.country like '%Singapore%') and (users.employeetype like '%Contract%' )) then concat('Con',substring(users.firstname,1,1),'sing','2') END