The Join Condition

 

If you wish 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 Table1.FirstName Column but only those rows whose corresponding value in Table3.WhatToDo = 'MASK'.

To determine the corresponding 'WhatToDo' values for each row in Table1, we must join to Table2 on Column 'ID_A', and from Table2 we join to Table3 on Column 'ID_C'.

 

 

Therefore when we create the 'Person First Name' mask we must define this join under 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 Table2 on the ID_A Column and click Add.

You must now click OK to add the join for Table2.

Note: If you continued to click Add in this panel then you would be defining additional conditions for the same Join to Table2. 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 Table2.

 

Now we repeat the process to join Table3 with Table2.

 

After clicking OK, we see that we have defined all the joins that are needed to correlate values in Table1 with tables Table2 and Table3.

 

Now we can define the Where condition so that we mask only those FirstName values that have a corresponding WhatToDo value of 'MASK' in Table3.

i.e. So that only the FirstName values on Table1 rows 1, 2, 8 and 9 are masked.

 

After performing a Preview Run we can confirm that indeed only those FirstName values on rows 1, 2, 8 and 9 are selected for masking.

 

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: "TABLE3"."WHATTODO": invalid identifier"
 SQL Server: "The multi-part identifier "Table3.WhatToDo" could not be bound."

 

The 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 "Table3" 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.

 

 

The 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.

You access a Table's Default Join by selecting the Table (such as in the Project Explorer tree) and then opening the Masking tab.

You then create a Default Join in the same way as for a mask as described above.