We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

User Generation rule not working for multiple cases

New Contributor
New Contributor


I am currently implementing the username generation rule with the following use case:

- The username is the concatenation between the 7 first lastname's characters and the first firstname character. If the lastname contains less than 7 characters, all the lastname is included in the username

- If there is a duplicate, a numerical value has to be added at the end of the username. It also has to be incremented: exemple: Alain Dupont with the username duponta and Allan Dupont with the username duponta1

For the moment, I am only focusing on the lastname that contains less than 7 characters and I also want to handle only one duplicate, so I have applied this rule:

CASE WHEN (LENGTH(users.lastname)<7) THEN lower(concat(users.lastname,substring(users.firstname,1,1))) END ### CASE WHEN (LENGTH(users.lastname)<7) THEN lower(concat(users.lastname,substring(users.firstname,1,1),substring('1',1,1))) END

However it is not working and I have an error when checking the logs: 

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

I also tried to use only one '#' but the problem is still the same.

Can anyone help on how to handle multiple use cases for duplicates ?

Thank you.





Saviynt Employee
Saviynt Employee

Hi @ayane_ahmed 

Can you please use the below and try it out- it should work

(CASE WHEN (LENGTH(users.lastname)<11) THEN lower(concat(users.lastname,substring(users.firstname,1,1))) END) # (CASE WHEN (LENGTH(users.lastname)<11) THEN lower(concat(users.lastname,substring(users.firstname,1,1),substring('1',1,1))) END)



Hello @Darshanjain 

It worked, thank you.