Within AQT, you can define relationships between tables. This is used in:
In many cases, the relationships between tables will have been defined to the database as a Foreign Key relationship. In these cases, AQT will detect these relationships(*). However there are a number of circumstances where tables are related, yet this relationship is not defined to the database:
In these cases you can define the relationship as a User Defined Relationship, or UDR. Once you have done this, the relationship between these tables becomes a “known” relationship in the Query Builder and Data Display windows.
(*) Note that if you have set Options > Table Information > Get Related Table information from User Defined Relationships only, AQT will get the related table information from the UDRs only, and will not query the database.
This can be done as follows:
tab1.keycol = tab2.keycol and tab1.col2 = 'X'
in this case
tab1.keycol = tab2.keycol is the join condition (as it involves both tables) and
tab1.col2 = 'X' is an addition Where clause on the join.
You can specify Generic relationships. These are useful if you have the same set of tables (with the same relationships) defined in a number of different schemas. By defining the relationship as generic, you need define each relationship only once - it will then apply to all schemas.
Example: suppose you defined a generic relationship between Schema1.TableA and Schema1.TableB. This would then imply:
The information on the UDRs is held in a file. There is a separate UDR file for each database. The UDR filename is
database.udr (for instance
aqtdemo.udr). The directory where the UDR files are stored is the Default Directory. The file name is given in the caption on the User Defined Relationships window.
You can manipulate these files in the same way as any other files. For instance, if you have a UDR file set up you can copy it to another person's PC so that they will be able to use the UDR definitions you have set up.
Similarly, if you have multiple similar databases (for instance, development and production). You can set up the UDRs for one of the databases, then copy the contents into UDR files for the other databases.