Using database expressions (Expression)

In some occasions it is necessary to use input of an already masked value in order to mask it consistently. For example in the Gasware Database there is the LAST_NAME, PARTNER_NAME and FULL_LAST_NAME. The full last name is a combination of the last name and the partner name with a dash between them. But sometime there isn't a partner name and in that case full last name is the same as the last name. 

In your masking template, we've added a shuffle on the LAST_NAME, but how do we keep the FULL_LAST_NAME consistent. 

To mask the FULL_LAST_NAME consistently with the already masked LAST_NAME, we have the following criteria:

• When PARTNER_NAME contains a value
  • Then combine the LAST_NAME with PARTNER_NAME with a dash between them
• Else 
  • Use the LAST_NAME

Expressions

Most databases have advanced features that help you solve this problem. The following Oracle SQL Expression will generate a combination of LAST_NAME and PARTNER_NAME when the PARTNER_NAME is not null and otherwise will return the LAST_NAME. To read more about the NVL2 function, check the following article.

Now lets apply this function in your template!

• Select the CUSTOMER table from the tables list
• Right click the FULL_LAST_NAME column and choose Add function... → Custom expression...

The function editor will open where you can configure some extra properties. The text editor enables you to write your own code. Dependent on the database your developing your template for, you can use all database functions in the expression field. You can even call a database function that returns a new value. 

• Write the following expression in the text area

  NVL2( PARTNER_NAME , LAST_NAME||'-'||PARTNER_NAME , LAST_NAME )

• Click Test to check if you made any mistakes. You will get Query validated if you didn't make any mistake!

• Add the following condition in the Condition tab

  Where TYPE = 'Civilian'

We want to generate masked FULL_LAST_NAMES after the shuffling the of the LAST_NAME column. So have to add an extra dependency for this Expression as well.

• Create a predecessor dependency in the dependencies tab with the Shuffle on LAST_NAME. The Shuffle must be executed first!
• Click OK twice to close the dependency and function editor and add your Expression function. 

Great job! You've added an conditional database expression with depedencies! 

If you got stuck in adding the expression, take a look at the following video clip