When the Dimensions panel is selected, the Wizard button can be used to generate dimension values, When Conditions for Searched Case statements or conditional expressions for And All or Or All statements. To generate dimension values, highlight any dimension value or ensure that no value is highlighted when the Wizard button is selected. Otherwise, highlight the desired Case Conditions folder under a Case - Searched node or the Expressions folder under an And All or Or All node and select the Wizard button.
The maximum number of dimensions or values that can be generated by a single application of the wizard is limited to 1000.
- Dimension Prefix — When a comparison operator such as Equal is selected in the Operator field, the names of the resulting dimension values consist of the prefix followed by underscore and the selected value. Otherwise, the dimension value name is the prefix followed by a number.
- Description — When a comparison operator such as Equal is selected in the Operator field, the description of the resulting dimension values consist of the description specified here followed by the operator and selected value. Otherwise, the description is the description entered here.
- Left Side Column/Expression — Replace the “(empty)” node with a SQL Column or more complex expression involving a SQL Column.
Operator — Select a comparison operator such as Equals; or select Between, Not Between, In, Not In, Is Null or Is Not Null as the operator to use.
- If Between or Not Between is selected, a dimension value or condition is generated for each pair of requested values.
- If In or Not In is selected, the Wizard will generate a single dimension value or condition based on all requested values when OK or Apply is clicked.
- If Is Null or Is Not Null is selected, the Wizard will generate a single dimension value or condition based on no values.
- Otherwise, if a comparison operator such as Equal is selected, the Wizard will generate a dimension value or condition for each requested value.
- Else Value — Select either Else Null or Else Zero to indicate the value to use when the condition is not met.
Right Side Values
Values — This tab accepts values displayed by clicking on Values for input columns on the left side of the input screen. Values can be drag-dropped onto this panel or selected with the right-arrow button. They can be numeric, string or date type values.
When values are displayed on the left side of the input screen, the ellipses button (the one displaying ‘…’) may be used to Select All Values.
Range — This tab can be used to generate a range of integer or decimal numeric values based on a From, To and By field. If desired, the values can be generated in descending order by making the From value greater than the From value, so that the By value should always be positive. If the By field is not specified, an incremental value of 1 is assumed. A value displayed with the Values button may be drag-dropped into this field. Also, the escape key will revert to the last value entered in this field.
When the Between or Not Between operator has been specified, the Range fields behave somewhat differently and may be used only to specify a single pair of values using the From and To field, with the From field validated to be less than or equal to the To field. The By field may not be specified when the Between or Not Between operator has been specified.
List — A separated list of numeric, string or date literal values can be entered here according to the conventions of the current locale settings.
Column values displayed with the Values button may be drag-dropped into this field, adding them to the list.
Standard right-click menu options are available within this field, namely Undo, Cut, Copy, Paste, Delete and Select All. Note, however, that the Paste option will convert a columnar list of values separated by carriage returns into a separated list, making it easier to copy values from other tools such as Microsoft Excel. Conversion of the values into valid Teradata literals are attempted for each of the incoming values, first into various numeric types, then if these fail, into date values, and finally if these conversions fail, the values are converted into a list of string literals.Whether dragging values displayed with the Values button, or pasting a list of values into this field, care should be taken that the items comprising the list do not themselves contain the current locale's list separator character, leading to undesired results.
- Generate list — This option is displayed only if the selected Operator is IN or NOT IN. If selected, it causes the list of values to be converted into a single Text Literal or Text Parameter element (if As parameter is also selected).
- As parameter — This option is displayed only if the selected Operator is IN or NOT IN and Generate list is selected. If selected, this option causes a Text Parameter with automatically assigned name to be created.
- Values — This tab accepts values displayed by clicking on Values for input columns on the left side of the input screen. Values can be drag-dropped onto this panel or selected with the right-arrow button. They can be numeric, string or date type values.
- Clear All — Clears all of the fields of this dialog.
- OK — Generates the requested dimension values or conditions and returns to the Dimensions panel.
- Cancel — Returns to the Dimensions panel without generating any elements.
- Apply — Generates the requested dimension values or conditions and remains on this panel. A status message is displayed just above this button reporting on the number of generated conditions.
When the Dimensions panel is selected and dimensions are defined, the Combine button can be used to generate combined dimension values based on existing dimension values. When dimensions are combined their conditions are joined with either an SQL ‘AND’ or ‘OR’ operator. The Else condition (Zero or Null) of the combined dimension is determined by the dimension on the left side of the AND or OR operator.
- Dimension Values — These are the dimension values from the Dimensions panel plus any dimensions already combined using the Apply button (thus becoming candidates for re-combining). Information about a dimension value may be viewed by holding the mouse pointer over it.
- Dimensions to Combine — Using the upper and lower sets of right and left arrow buttons, dimensions may be selected or deselected for combining. The AND/OR radio buttons may be selected to determine the method of combining the conditions represented by the dimension values. The double left arrow buttons to the right of these panels move combined dimensions back into the panels in preparation for re-combining.
- Combined Dimensions — The single right and left arrow buttons next to this panel cause the dimensions to be combined and added to the combined dimensions list, or removed from the list, respectively. If the name of any combined dimension is too long, a warning message is given in the lower left corner of the dialog. The double left arrow buttons to the left of this panel move combined dimensions back into the “Dimensions to Combine” panels in preparation for re-combining. Thus, it is possible to build up combined dimensions without making dimension values out of the intermediate results.
- Clear All — Clears all of the fields of this dialog except the Dimension Values in the left-most panel.
- OK — Generates the dimensions defined in the Combined Dimensions panel and return to the Dimensions panel.
- Cancel — Returns to the Dimensions panel without generating any elements.
- Apply — Generates the dimensions defined in the Combined Dimensions panel and remain on this panel. A status message is displayed in the lower left corner of the dialog reporting on the number of generated combined dimensions.
The Delete button can be used to delete any node within the Dimensions tree. If applicable, the tree will roll-up children, but in some cases, a delete may remove all children.
The SQL button can be used to dynamically display the SQL for any node within the Dimensions tree. If the resulting display is not closed, the expression changes as you click on the different levels of the tree comprising a dimension value. An option is provided in the display to Qualify column names, that is to precede each column name in the display with its database and table name.
Name — A name must be specified for each dimension value.Variables can be named by single left-clicking on the name, which produces a box around the name, as in Windows Explorer.
- Else Condition — Dimension values are applied to a variable via a CASE construct. By default, the ELSE condition within the CASE construct is NULL. Here, you can specify a 0 be used instead.
- Description — An optional description may be specified for each dimension value. Note that a default description is generated automatically by the Wizard if its Description field contains a value, and also by the Combine Dimensions dialog based on individual descriptions or dimension names.
The Undo button can be used to undo changes made to the Dimensions panel, up to 100 operations. Note that if a number of dimension values are added at one time, a single Undo request is all that is needed to undo the addition of items, but only up to 100 items (because each addition is a separate operation).
Note also that if a change to a dimension value is undone, and that dimension value is currently applied to a variable on the Dimensions panel, the applied dimension will not change as a result of the Undo operation.
The Redo button can be used to reinstate a change previously undone with the Undo button.
Question-Mark Help Button
The Question-Mark Help button can be used to request help information about a specific SQL element by first clicking on the question-mark and then on the SQL element in the SQL Elements panel or Dimensions panel.