Buttons - Teradata Warehouse Miner

New Button

Clicking on the New button creates a new Variable on the panel.

Add Button

Clicking on the Add button brings up a dialog to allow adding copies of variables from other loaded analyses. Options for retaining applied dimensions and mapping database objects are provided.

In the case where variables are copied from an analysis in another project, a check is made to see if any variable being copied contains a reference to a column created by another analysis, and if so gives an error. The same check is made for an applied dimension when the option to retain dimensions is checked.

Any literal parameters present in an added variable or applied dimension are reconciled with the literal parameters already defined in the analysis the variable is being added to. That is, if the literal parameter does not exist in the analysis, it is added, or if a literal parameter with the same name and type is present, the value of the literal parameter in the added variable is changed to match that in the literal parameters of the analysis. Finally, if a parameter with the same name but different type already exists in the analysis, the parameter in the variable being added is renamed and added to the literal parameters of the analysis.

Variable Creation > Input > Variables: Add Variables dialog

Wizard Button

When the Variables tab is selected and either a Variable is selected or nothing is selected, the Wizard button can be used to generate new variables, each containing a Searched Case statement. Alternately, when an appropriate folder is selected, When Conditions for Searched Case statements, or conditional expressions for And All or Or All statements, can be generated. To do so, highlight the 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 variables or values that can be generated by a single application of the wizard is limited to 1000.

The following dialog is given when a Variable or nothing at all is selected. Note that in the other cases a subset of these fields is displayed with appropriate instructions at the top of the dialog.

Variable Creation > Input > Variables: Variables pane - nothing selected

• Variable Prefix — When a comparison operator such as Equal is selected in the Operator field, the names of the resulting variables consist of the prefix followed by underscore and the selected value. Otherwise, the variable 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 variables 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.
• Then Expression — Replace the “(empty)” node with a SQL element or more complex expression that will form the Then clause of the generated Searched Case expression.

  The default value of ‘1’ is useful for an indicator variable.

• Else Expression — Replace the “(empty)” node with a SQL element or more complex expression that will form the Else clause of the generated Searched Case expression.

  The default value of ‘0’ is useful for an indicator variable.

• 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 variable or condition is generated for each pair of requested values. If In or Not In is selected, the Wizard will generate a single variable or condition based on all requested values when OK or Apply is clicked. If Is Null or Is Not Null is selected, the Wizard generates a single variable or condition based on no values. Otherwise, if a comparison operator such as Equal is selected, the Wizard generates a variable or condition for each requested value.
• Aggregate — If desired, select an aggregate operator, either Average, Count, Minimum, Maximum or Sum, to aggregate the entire CASE statement, or ‘(no aggregate)’ to not enclose the statement with an aggregate operator.
• Right Side Values
  • Values — This tab accepts values displayed by selecting the Values button for input columns on the left side of the input screen. The displayed values can be drag-dropped onto this panel, selected with the right-arrow button or selected by double-clicking them. 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 To 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, pressing the Escape key reverts 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.
• Clear All — This button clears all of the fields of this dialog. This is convenient because all entries are generally retained when returning to this dialog.
• OK — This button will generate the requested variables or conditions and return to the Variables panel.
• Cancel — This button returns to the Variables panel without generating any elements.
• Apply — This button will generate the requested variables or conditions and remain on this panel. A status message is displayed just above this button reporting on the number of generated conditions.

Delete Button

The Delete button can be used to delete any node within the tree. If applicable, the tree will roll-up children, but in some cases, a delete may remove all children.

SQL Button

The SQL button can be used to dynamically display the SQL for any node within the Variables tree. If the resulting display is not closed, the expression changes as you click on the different levels of the tree comprising a variable. 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.

Properties Button

The Properties button can be used to display or edit the properties of a Variable, Column or SQL Element by clicking the Properties button when the item is highlighted, or by double-clicking on the item. Note that the dialog can remain open while another element is selected. When this happens, any changes made to the properties of the original element are saved as if Apply was clicked before switching to the next element. The Properties dialog for a Variable follows.

Variable Creation > Input > Variables: Variable Properties dialog

• Name — A name must be specified for each variable. If the SQL expression defining the variable is simply a SQL Column, the name defaults to the name of the column automatically when the column is dragged to the variable.
  Variables can be named by single left-clicking on the name, which produces a box around the name, as in Windows Explorer.
• Output Type — A specific Teradata data type may optionally be specified for each variable. If specified, the SQL CAST function is used to force the data type to the requested specification. Otherwise, the type will be generated automatically by the variable’s expression (Generate Automatically option). Valid options include:
  • BIGINT
  • BYTEINT
  • CHAR
  • DATE
  • DECIMAL
  • FLOAT
  • INTEGER
  • NUMBER
  • SMALLINT
  • TIME
  • TIMESTAMP
  • VARCHAR
• Column Attributes — One or more column attributes can be entered here in a free-form manner to be used when an output table is created. They are placed as-entered following the column name in the CREATE TABLE AS statement. This can be particularly useful when requesting data compression for an output column, which might look like the following: COMPRESS NULL.
• Description — An optional description may be specified for each variable. Note that a default description is generated automatically by the Wizard if its Description field contains a value.

  The Properties button can be used to display or edit the properties of a Column by clicking the Properties button when the item is highlighted, or by double-clicking on the item. The Properties dialog for a Column follows.

  Variable Creation > Input > Variables: Column Properties dialog
  
  
• Column Name — The name of the column.
• Column Type — The type category of the column (i.e., Character, Numeric, Date, etc.).
• Database — The database that contains the table or view the column is part of.
• Table/View — The name of the table or view that the column is part of.
• Analysis — If the column is part of a table or view referenced as the output of an analysis, the name of the analysis is displayed here.
• Correlation Name — If this analysis is a correlated subquery, this field can be used to reference a table alias in the containing analysis, provided this analysis selects from the same table it needs to reference in the containing analysis or outer query. Otherwise, a SQL Text or Text literal must be used rather than a column element such as this one.
• Self-Join Alias — To produce a self-join by accessing this column from another logical instance of the table or view that contains it, enter an alias in this field for the instance of the table. If this column is to be accessed from the original copy of the table, do not enter a value here.
  Self-joined copies of a table produced in this way do not appear in the Join Paths and consequently must appear in the Where clause of the analysis or else a warning message is given. Also, a table with a Self-Join Alias entered in this field cannot serve as an Anchor table.
  Important: An alternate way to produce a self-join is to use the right-click menu option to Create Self-Join Copy, available in the area where available columns are displayed on the left-hand portion of the variables tab. The use of this technique may be preferable since each self-join table instance appears 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. For more information, see to Right-Click Menu Options in Variable Creation - INPUT - Variables.
• Parameter Alias — If a column is passed as a parameter to a UDF using the New Variant Type SQL element, an optional alias name may be assigned to the column using this field. An alias name can alternately be associated with a column by clicking slowly on the column label in the work area and entering a value.
• Parameter Name — A column name may function as a Literal Parameter of type Text by specifying a parameter name in this field. When a parameter name is entered here, a parameter is automatically added to the Literal Parameters table on the literal parameters tab. The user should put the desired initial column name in the Value column of the Literal Parameters table.
  If the same parameter name is associated with more than one column, both columns will have the same name in the resulting SQL.

  This feature is particularly useful when publishing a Variable Creation analysis to the AppCenter or to the Model Manager application, because it allows the AppCenter or Model Manager user to dynamically change the name of a column at run time when executing a published SQL application.

  Note that an entry in this field is only allowed when Correlation Name, Self-Join Alias and Parameter Alias are all blank. Further, this feature is not supported when defining a Derive transformation in a Variable Transformation analysis, or when defining a conditional expression using the Edit button on the anchor table tab.

The Properties dialogs for SQL elements are described in Variable Creation - INPUT - Variables - SQL Elements.

Undo Button

The Undo button can be used to undo changes made to the Variables panel, up to 100 operations. Note that if a number of variables 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).

Redo Button

The Redo button can be used to reinstate a change previously undone with the Undo button.

Question-Mark Help Button

Use the Question-Mark Help button 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, Variables panel or Dimensions panel.