Database tables are often related through columns they share in common. The universe stores information about such relations in joins.
Note:
Joins are made between base tables, not between universe folders. If you define a join between two base tables, the Information Designer understands that the join links any folders based on those tables.
For example, if you want to display the severity of an alert in reporter.REPORTER_STATUS as a name and not as a severity number, you must join this table with the reporter.REP_SEVERITY_TYPES table. The columns with common data that you need to join are reporter.REPORTER_STATUS.ORIGINALSEVERITY and reporter.REP_SEVERITY_TYPES.SEVERITY.
To create a join:
1. Click the Joins tab to bring the tab to the foreground.
2. Select the menu option CreateÆCreate Join or click the Create Universe Join icon. The New Join window is displayed, as shown below.
|
3. Enter a name for the join in the Name: field.
4. Enter a description of the join. This field is optional.
5. Expand the tables in the Left Table and Right Table list until you find the columns you would like to join. Select each of these columns.
When you select a column the type is displayed in the Column Type field below the selected column.
6. Define a relationship between the two columns by selecting an operator from the Relation drop-down list. Possible values are =, <, >, <=, >= and <>.
7. If required, select the All Rows checkbox above either the left or right table lists. This adds the text [LEFT] or [RIGHT] at the start of the SQL expression.
8. Click the Add Join button to create the join. The SQL expression for the join appears in the Expression field.
If the two selected columns have different column types, Netcool Reporter displays a warning when you click the Add Join button. You can choose to cancel the instruction or ignore the warning and create the join.
If required, you can edit the SQL expression in the Expression field.
9. Click OK to apply the join to the universe.
1. Select the join to edit in the Joins tab.
2. Select the menu option PropertiesÆUniverse Object or double-click the name of the join. The Edit Join Properties window is displayed.
This window is identical to the New Join window, as described in the previous section.
3. Edit any of the attributes of the join.
To edit the existing expression, change the SQL line in the Expression field. To replace the expression select two new columns and a new operator and click the Add Join button. The old join is replaced by the new one.
4. Click OK to apply the updated join to the universe.
To remove a join, select the name of the join in the Joins tab and click the Remove button at the bottom of the tab.