Join Option

If you need to create a mask that references a column in a different table to where the mask is defined then you will need to define a Join.

Typically you would do this because you want to define a mask's Where condition that needs to test a value in another table, or that you want to define a User SQL Value mask that needs to reference columns in other tables.


For example, suppose that we want to mask the PersonTable.given column with the Person Given Name mask but only those rows whose corresponding value in ActionTable.WhatToDo = 'MASK'.

To determine the corresponding 'WhatToDo' values for each row in PersonTable, a join to MidTable on column 'ID_A' is required, and then a join from MidTable to ActionTable on column 'ID_C'.



Therefore when the 'Person First Name' mask is created, joins must be defined. This can be done in the mask's Join tab.


Note: If you have already defined a Default Join for the table then you could simply click on the checkbox "Use Default JOIN defined in this project at table level instead". We shall discuss the Default Join later below. For now, let's create an explicit Join for this mask.

Start by clicking on the "Create" button.

Now define the join to MidTable on the ID_A column and click Add.

* You must now click OK to add the join to MidTable.
* If you continued to click Add in this panel then you would be defining additional conditions for the same Join to MidTable. Multiple conditions for a join are implicitly 'AND'ed together.


After clicking OK, you shall see that the Join tab has been updated with the join to MidTable.


Now, repeat the process to join ActionTable to MidTable. Click on the Create button and define the join:


After clicking OK, we can see that all joins that correlate values in PersonTable, MidTable and ActionTable are now defined.


Now we can define the Where condition so that only those rows in the PersonTable.given column that have a corresponding value of 'MASK' in the ActionTable.WhatToDo column are selected.

i.e. So that only the PersonTable.given values on rows 1, 2, 8 and 9 are masked, as indicated in the table data relationship diagram above.


In order to emphasize the selected masked rows, we will add an unconditional Redact mask that uses the hyphen character ("-") after the Person Given Name mask. Having an unconditional mask after conditional masks is best practice, even if the mask is just a Preserve mask, and it avoids unnecessary compile warnings.


After performing a Preview Run we can confirm that indeed only those PersonTable.given values on rows 1, 2, 8 and 9 have been selected using the join.


If a Join is required (as for the column reference in the Where condition in the example above) and you do not define one then you will encounter a runtime error similar to:

Oracle: ORA-00904: "ACTIONTABLE"."WHATTODO": invalid identifier
SQL Server: The multi-part identifier "ActionTable.WhatToDo" could not be bound.


Table Alias Field

You may have noticed that in the Join definition panels there is a Table Alias field.

These are automatically populated with an initial value the same as the unqualified table name.

You only need to use these Table Aliases if there is any ambiguity in the column names that you reference in a Where condition or User SQL Value mask.

In the Where condition example above you can see that the alias "ActionTable" was used to qualify column "WhatToDo". In this case it was unnecessary; however, if you were referencing the column "Comment" (which appears in multiple tables that are joined) then you must qualify the column name with the Table Alias otherwise you will encounter a runtime error similar to:

Oracle: ORA-00918: column ambiguously defined
SQL Server: Ambiguous column name 'Comment'

Note: If you qualify a column name then you must use the Table Alias value. Do not qualify using the actual database, schema or table names as qualifiers (if different to the Alias) because this would likely result in an error at run time.


Default Join

You can define a Default Join for a table so that if multiple masks need to use the same join then you can simply select each mask's checkbox "Use Default JOIN defined in this project at table level instead" rather than having to repeatedly duplicate the join definition for each mask.

A table's Default Join is accessed by selecting the table (such as in the Project Explorer tree) and then opening the Masking tab.

Next, click on the Create button to define a Default Join in the same way as for a mask as described above.


Masks in the table can now refer to the Default Join simply by selecting the 'Use Default JOIN...' checkbox: