Create Self-Join Copy – This option lets users of the Variable Creation analysis easily create a query representing the “self-join” of a table or view. It is only enabled when Input Source is set to Table, a table or view is selected, and its columns are displayed. The result of selecting Create Self-Join Copy is that a Function Table representing a derived table is automatically generated and selected in the Columns/Values area. The Function Table can be viewed by selecting the Function Tables tab. The generated Function Table represents a derived table containing the free-form statement:
“SELECT * FROM <db>.<tbl>”
where <db> and <tbl> represent the database and table names of the table or view the self-join copy is made from. The generated Function Table will have the same columns as the original table, from which the desired columns can be placed in a Variables expression in the usual way. When columns from the Function Table have been used in an expression, the Function Table will appear in a Join Path on the anchor table tab, where the Wizard button and other standard facilities can be used to define the self-join conditions.
- Aggregation, Arithmetic, Comparison, Literals, Literal Parameters, Logical, and Other plus indirectly Other, Case / Null Handling, Ordered Analytical, String Functions, Table Functions and Run Units — These options correspond to categories of SQL elements that may be used in an expression. When hovering over one of these categories, a list of commonly used elements in the category are listed in a sub-menu (in a few cases, a further sub-menu is displayed with specific examples of the element). When one of these is selected, the element is inserted above (or replaces) the existing or empty element that is the target of the right-click action. The target is either the element under the cursor when the right-click action occurs, or the element that is highlighted if the right-click occurs on empty space in the expression tree. If a Variable, Dimension, Table or Run Unit itself (i.e., the highest level item in the expression tree) is the target of a right-click action, the selected element is placed in a new Variable, Dimension, Table or Run Unit. As a special case, Table Functions/Operators and Run Units are applied to their respective tabs as new items, regardless of the tab that is current when selected.
- Expand All Nodes — Expands the nodes completely for each variable.
- Collapse All Nodes — Collapses the nodes to show only variables.
- Switch Input To ‘<table name>’ — This option applies only when a SQL Column is highlighted in the Variables panel or a Variable containing only a SQL Column is highlighted. When this option is selected, the selectors on the left side of the input screen are adjusted to match the table or analysis that contains the selected SQL Column, and the column is also selected. Note that if the table selected on the left side of the screen contains the column currently selected in the Variables panel, this option becomes Switch Input To This Column.
- Switch ‘<table name>’ To Current Input — This option applies only when a SQL Column is highlighted in the Variables panel or a Variable containing only a SQL Column is highlighted. When this option is selected, the selectors on the left side of the input screen are used to change the input table or analysis of the selected SQL Column. A pop-up menu is displayed to allow changing the input for this column only or for all occurrences. For a single column, a column with the same name must occur in the new (currently selected) input table or analysis or an error is given. When all columns are changed, the new table or analysis must contain all the columns or an error is given and no changes are made. Note that if the table selected on the left side of the screen contains the column currently selected in the Variables panel, this option is disabled.
- Apply Dimensions to Variables — This option jumps to the upper dimensions tab so that dimensions can be applied to variables.
- SQL — This option displays the SQL that will be generated for this node in the tree.
- Properties — This option displays the Properties dialog box, if any, that applies to this SQL element.
- Add Columns Matching Parameter Names — This option applies only when a User Defined Function element (or the Arguments folder underneath it) is selected in the Variables panel, and when the Arguments folder is empty (i.e., no arguments have been selected). If the option is selected, columns with names matching those of each function argument are selected from the current input table on the left side of the screen into the Arguments folder underneath the User Defined Function. If any or all argument names are not found in the currently selected input table, a message is given to indicate how many argument names could not be matched and to provide the option to continue with placeholders for unmatched parameters. If a response of 'OK' is given to this message, dummy column elements with names in the form “>>.name” (where name is the argument name) are created and added to the arguments folder in the required order.
- Help — Jumps to the Online Help section appropriate for the currently highlighted element.