Free Form SQL - INPUT - Literal Parameters - Teradata Warehouse Miner

Teradata® Warehouse Miner™ User Guide - Volume 1Introduction and Profiling

Product
Teradata Warehouse Miner
Release Number
5.4.6
Published
November 2018
Language
English (United States)
Last Update
2018-12-07
dita:mapPath
rfc1538171534881.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2300
Product Category
Software
  1. On the Free Form SQL dialog box, click on INPUT.
  2. Click literal parameters.
    Free Form SQL Input: Literal Parameters

    This screen contains information about literal substitution parameters and various advanced options. SQL literal values of string, numeric, date, time and timestamp types may be defined on this screen, with their values being substituted in the free-form SQL on the analysis parameters tab wherever they appear enclosed in less-than and greater-than signs. The substituted literal values automatically include quotes and a keyword if necessary. For example, the parameter p1 shown above appears in the free-form SQL as <p1>, which is converted to DATE ‘2008-02-28’. Note that literal values are entered in a format consistent with the workstation’s current locale setting, yet appear in the free-form SQL in an invariant format.

    In addition to the supported SQL literal types, a text type is provided that receives no formatting and is substituted in the free-form SQL “as-is”. If desired, a special use can be defined for a parameter, such as the uses of Input and Output Database and Input and Output Table shown above. These uses are recognized in other parts of the application as described in more detail below. Note that each special use can be assigned to only one parameter, so that, for example, there can be only one Output Database parameter.

    Note that Free Form SQL literal parameters are of limited use in the Teradata Profiler product since many of the features described below apply only to the Teradata ADS Generator and Teradata Warehouse Miner products. In particular, all references to Variable Creation, Variable Transformation, Refresh, Publish, ADS and scoring analyses do not apply to the Teradata Profiler product. Note also that in the sample screen shown above, the Creates Score Table and Depends on Analysis options are not visible in the Teradata Profiler.

  3. On this screen, select:
    • The Add option can be used to add a literal parameter to the analysis. A default name consisting of the letter ‘p’ followed by an integer will automatically be assigned. The default name can be changed by the user after clicking the Properties button.
      A menu with available types is displayed, including:
      • Date
      • Numeric
      • String
      • Text (used as-is without formatting)
      • Time
      • Timestamp

        Also displayed are the appropriate special use options, depending on the product and the Creates Score Table option. When one of these options is selected a parameter of type Text is added with the appropriate special use (with the exception of Target Date which creates a parameter of type Date). Note that when using a special use text parameter for a database object name, do not include double quotes in the parameter, but rather in the Free Form SQL text surrounding the parameter name if they are needed (for example, “<p1>”).

    • Generally Available Special Use Options
      The generally available special use options include:
      • Input Database — When used together with Input Table, the value is recognized when importing or adding an existing analysis. The value specified may not be a foreign database accessed via QueryGrid.
        For those parameters that don’t support foreign objects accessed via QueryGrid, a View may be used instead.
      • Input Table — When used together with Input Database, the value is recognized when importing or adding an existing analysis. The value specified may not be a foreign table accessed via QueryGrid.
      • Output Database — When used together with Output Table or Output View, the value is recognized when importing, adding an existing analysis, displaying properties or performing metadata maintenance as an output table or view that can be deleted or changed. Another special feature of this parameter use is that when the value is left blank, the default result database from the connection properties is automatically used. If, however, this table or view needs to match the output table or view of another analysis it is better to use an explicit value here so that if database mapping is ever performed upon importing or adding this analysis the database will be matched to the correct value. Finally, the Output Database and Output Table or View parameters must be present if it is desired for the Free Form SQL analysis to be “selectable” (that is, display its output table and columns when selected as an analysis for input).
      • Output Table — See the description of Output Database above for special considerations.
      • Output View — See the description of Output Database above for special considerations.
      • User ID — This special use option can be used for the database in which a volatile table is created. The logon User ID will automatically be substituted at run time, regardless of the value specified.
    • ADS Special Use Options — In products other than the Teradata Profiler when the Creates Score Table option is not set, the special use options below may be used. With the exception of the generally available special use options already described, these options are of use only when using the Refresh or Publish analyses or the Model Manager application, making the Free Form SQL analysis appear as if it were a Variable Creation analysis with these properties. Depending on the context (i.e., in Refresh, Publish or the Model Manager application), these fields may be displayed as separate fields, possibly renamed, as literal parameters or simply used internally, as is the case with User ID.
      • Target Date — An optional parameter used in time sensitive data set variables.
      • Anchor Database — The value specified may not be a foreign database accessed via QueryGrid.
      • Anchor Table — The value specified may not be a foreign database accessed via QueryGrid.
      • Input Database — The value specified may not be a foreign database accessed via QueryGrid.
      • Input Table — The value specified may not be a foreign database accessed via QueryGrid.
      • Output Database — (defined above)
      • Output Table — (defined above)
      • Output View — (defined above)
      • User ID — (defined above)
    • Scoring Special Use Options — In products other than the Teradata Profiler when the Creates Score Table option is set, the following special use options may be used. With the exception of the generally available special use options already described, these options are of use only when using the Refresh or Publish analyses or the Model Manager application, making the Free Form SQL analysis appear as if it were a scoring analysis with these properties. Depending on the context (i.e., in Refresh, Publish or the Model Manager application), these fields may be displayed as separate fields, possibly renamed, as literal parameters or simply used internally, as is the case with User ID.
      • Input Database — The value specified may not be a foreign database accessed via QueryGrid.
      • Input Table — The value specified may not be a foreign database accessed via QueryGrid.
      • Output Database — (defined above)
      • Output Table — (defined above)
      • Output View — (defined above)
      • User ID — (defined above)
      • UDF Database — This special use option can be used for the database in which PMML scoring User Defined Functions are located. The current Metadata Database will automatically be substituted at run time, regardless of the value specified.
    • The Remove option can be used to remove a literal parameter from the analysis.
    • The Sort option can be used to sort the literal parameters in the display grid, By Name, By Type or By Entry (i.e., the order entered into the analysis).
    • The Properties option can be used to change the Name, Value and/or Use of a parameter, as well as to enter a Description if desired. For the Value and Use fields, it provides a more fully-featured alternative to editing them in place in the grid.
    • The Generate SQL Only option can be used to inhibit the execution of the free-form SQL while generating the SQL (with all parameters substituted) that would ordinarily be executed. The SQL can be viewed on the Results screen.
    • The Table Function or Operator option can be used to indicate that the Free Form SQL Analysis, when referenced by another analysis, should be placed in the From clause as a Table Function or Table Operator (i.e., without the parentheses that would be added if it represented a derived table). Refer to the similar option on the Function Table Properties dialog box.
    • The Creates Score Table option (not available in the Teradata Profiler product) can be used to indicate that this analysis should be treated as a scoring analysis when published to the Model Manager application using the Publish analysis. As such, this option can only be set on the last analysis in an analysis reference chain as described below in the description of the Depends on Analysis option.
    • The Depends on Analysis option (not available in the Teradata Profiler product) can be used to chain this analysis to another analysis that it is dependent on for input, causing the analysis it depends on to be executed before this one (along with any analyses it may reference). This feature, together with the data selection tab, allows one or more Free Form SQL analyses to appear anywhere in an analysis reference chain.
    • The Advertise Output option may be requested only when substitution parameters with “special use” Output Database and Output Table or Output View have been specified. This feature “advertises” output by inserting information into one or more of the Advertise Output metadata tables according to the type of analysis and the options selected in the analysis. For more information, see Advertise Output.
    • An Advertise Note may be specified if desired when the Advertise Output option is selected or when the Always Advertise option is selected on the Connection Properties dialog box. It is a free-form text field of up to 30 characters, it may be used to categorize or describe the output. The Advertise Note is ignored, however, if the appropriate “special use” substitution parameters are not present.