In this section: How to:
Reference: |
The columns you select define the input to your data target. If you are using the DMC to format a new data target, the order of the columns will reflect the order that the columns are specified in the SQL command.
The DataMigrator Server, which DataMigrator uses to process its extraction logic, can have up to approximately 400 columns in a single data flow. If you intend to move more than 400 columns, several data flows may be linked together by creating a process flow. For information on process flows, see Designing a Process Flow.
The default settings of the DMC require you to manually select the columns you want to extract. To change your settings so that all columns are selected automatically:
The Column Selection window opens.
or
Select one or more columns and click the right arrow.
Tip: To select all columns, click anywhere in the column list and press CTRL + A.
The columns appear in the Selected Columns grid on the right.
The Column Selection window opens.
The SQL Calculator opens.
For information about creating a calculated column in the Selected Columns grid, see Creating an SQL Calculation.
If a column in a source synonym has a description, that description will be carried over with the column into any new target synonyms. You can also use the Column Selection window to add descriptions for columns that do not have them, or to change the descriptions for columns that do have them. These descriptions will also be carried over into any new target synonyms in the current flow.
The Column Selection window opens.
The Column Selection window is available from the data flow workspace by double-clicking the SQL object, or by right-clicking it and selecting Column Selection.
The Column Selection window contains the following fields and options by default:
Removes duplicate rows from the results of your query.
Lists the columns in each data source. Right-clicking on a column header opens a context menu with the following choices
Note: When in List View, clicking a column heading will sort the column grid on that heading.
The columns symbols are:
Symbol |
Description |
---|---|
|
Regular column |
|
Key column |
|
Index column |
|
Virtual column Note: The symbol for a virtual column in the synonym is blue, while the symbol for a transformation (virtual column in the flow) is yellow. |
Is the name of the column. Next to each column name there is a symbol that indicates regular columns, key columns, index columns, virtual columns, and transformations.
Is the name of the data source.
Is the format of the column.
Is the description of the column.
Indicates if the column can contain NULL values.
Adds columns to the Selected Columns grid.
Deletes columns from the Selected Columns grid.
Lists the columns to be extracted from the data source(s).
Is the column name, or an expression created in the SQL Calculator.
Is an alternate name for referencing the column. If you add an alias and expression, the expression shows up where the column name would normally be.
Is the format of the column.
Is the aggregation applied to the column. The available options are Group By, Group By Not Selected, Sum, Min, Max, Count, Avg, Sum Distinct, Count Distinct, and Avg Distinct.
Is the description of the column.
Indicates if the column can contain NULL values.
Allows you to search for a column.
Opens the SQL Calculator to create a column.
Deletes the selected column.
Opens the SQL Calculator to edit a column.
Tests the SQL statement and displays the result.
Note: The test button will be grayed out if you have not selected any columns.
Moves the selected column up or down within the Selected Columns grid.
If you right-click anywhere on the header bar, you get the following options:
Allows you to search for a column.
Resizes the column to fit its data.
Allows you to select what information is displayed for each column.
If you right-click any column, you get the following options:
Opens the SQL Calculator where you can edit the properties of a column.
Deletes the selected column.
How to: |
You can aggregate your columns in the Column Selection window.
In the Column Selection window:
Note: If you apply an aggregate function to one of your selected columns, the rest of the selected columns must, in most cases, have aggregation functions applied or be GroupBy columns. After an aggregation is applied to a column, all columns without an aggregation turn red until an aggregation is applied to them as well.
How to: Reference: |
You can create an SQL calculation for your data flow that:
The SQL Calculator opens.
The calculation appears in the SQL Expressions field.
Note: When you create expressions, keep in mind their order of evaluation. For details, see Order of Evaluation.
Note:
From the Column Selection window:
The SQL Calculator opens.
The variable and alias appear in the Selected Columns grid.
For more information about using variables, see Using Variables in a Flow.
The SQL Calculator is available from the Column Selection window by clicking the Insert Columns button, or by selecting a column and clicking the Edit Column button.
The SQL Calculator contains the following fields and options:
Is an alias for the column.
Displays the expression.
Displays available columns and variables in a hierarchical tree or in a grid.
Displays a list of SQL functions that are available for your transformations.
Allows you to specify parameters for the function through a dialog box when creating or editing a transformation.
Insert numbers and operators.
Adds parentheses.
Inserts two single quotation marks. Enter alphanumeric test values between these.
Converts selected text to uppercase.
Converts selected text to lowercase.
Opens the Date Editor window which lets you use the current date, or specify a date from the a calendar.
Opens the Date Editor window which lets you use the current date and time, or specify a date and time from the a calendar.
The following table describes the result of an operator on an expression or expressions, where expr is an expression.
Operator |
Result |
---|---|
expr + expr |
Returns the sum of two expressions. |
expr - expr |
Returns the difference of two expressions. |
- expr |
Returns the negated value of an expression. |
expr * expr |
Returns the product of two expressions. |
expr / expr |
Returns the dividend of two expressions. |
expr || expr |
Returns the concatenation of two character strings. |
How to: Reference: |
You can use an SQL function in the SQL Calculator to create a complex expression. In addition, most ANSI 92 SQL functions can be used in a SELECT command, depending on the data source type and platform.
The Function Assist dialog box opens.
For more information about the Assist dialog box, see How to Use Function Assist.
Functions in the SQL Calculator Functions tab can be viewed by category (Tree View) or alphabetically (List View). Right-click the Name header bar to select between the two views.
SQL Function |
Description |
---|---|
Character Functions | |
CHAR_LENGTH(arg) |
Returns the length of arg in a character string. |
CONCAT(arg1, arg2) |
Concatenates the values of two arguments. |
DIGITS(arg) |
Converts a numeric expression to a fixed length character string. DB2 only. |
EDIT(arg, mask) |
Edits a numeric or character value according to a format specified by a mask. |
LCASE(arg) |
Converts a character string value to lowercase. Identical to LOWER and LOWERCASE. |
LTRIM(arg) |
Removes leading spaces from a character string. |
POSITION(substring IN arg) |
Returns the location of substring in arg, or returns a zero if substring is not found. |
RTRIM(arg) |
Removes trailing spaces from a character string. |
SUBSTR(arg FROM start-pos[FOR length]) |
Returns a string from arg starting at start-pos to the end of the string, or if specified length characters. |
SUBSTR(arg, start_pos[, length]) |
Returns a string from arg starting at start-pos to the end of the string, or if specified length characters. |
TRIM(arg)
TRIM(trim_char FROM arg)
TRIM(trim_where[trim_char] FROM arg) |
Removes leading and/or trailing spaces and other characters from a character string. The character to be removed may be specified as trim-char, if it is not, the space character is assumed. Whether to remove leading and/or trailing characters may be specified as trim-where, if not, both leading and trailing spaces are removed. |
UCASE(arg) |
Converts a character string value to uppercase. Identical to UPPER and UPPERCASE. |
Current Date and Time Functions | |
CURRENT_DATE |
Returns the date in YYMD format. |
CURRENT_TIME[(precision)] |
Returns the current time of the operating system in the form, HHMMSS. |
CURRENT_TIMESTAMP
[(precision)] |
Returns the current timestamp of the operating system (date and time) in the form, YYYYMMDDHHMMSS. |
CURRENT_TIMEZONE() |
Returns the current time zone. |
Data Type Conversion | |
CAST(expression AS data_type [ (length) ] ) |
Converts the value of its argument to a specified data type. Length is an optional integer that specifies the length of the target data type. |
char(arg[,date_format]) |
Converts a number to a character string or data value to a string in a specified format.
|
DATE(arg) |
Returns a date, time or timestamp value that is computed from arg. |
DECIMAL(arg, [length [dec_places]]) |
Converts a number into a fixed-length decimal where dec-places is the number of decimal places in the result. |
FLOAT(arg) |
Converts arg to a floating point value. |
INT(arg) |
Converts a number to an integer. Identical to INTEGER. |
SMALLINT(arg) |
Converts a number to a small integer. |
TIME(arg) |
Converts its argument to a time. |
TIMESTAMP(arg) |
Converts its argument to a timestamp. |
VARGRAPHIC(arg) |
Converts its argument to a vargraphic. |
Date and Time Functions | |
DAY(arg) |
Returns a number that contains the day of the month from a date or timestamp. |
DAYS(arg) |
Returns the number of days since December 31, 1900. |
EXTRACT(field FROM arg) |
Extracts a numeric value from a date or time value arg, where field is either a year, month, day, hour, minute, or second. |
HOUR(arg) |
Returns the hour field from a time or timestamp value. |
MICROSECOND(arg) |
Returns the number of microseconds from a time or timestamp value. |
MILLISECOND(arg) |
Returns the number of milliseconds from a time or timestamp value. |
MINUTE(arg) |
Returns the number of minutes from a time or timestamp value. |
MONTH(arg) |
Returns the number of the month in which arg falls. |
SECOND(arg) |
Returns the second field from a time or timestamp value. |
YEAR(arg) |
Returns the year in which arg falls. |
Miscellaneous Functions | |
COUNTBY(arg) |
Produces a column whose values are incremented row-by-row by a specified amount. |
HEX(arg) |
Converts its input value to hexadecimal. Note: This function is available only for DB2, Ingres, and Informix. |
IF(test, val1, val2) |
Tests a condition and returns a value based on whether the condition is true or false. |
LENGTH(arg) |
Returns the number of bytes of storage used by arg. |
USER() |
Returns the user ID running the query. |
Numeric Functions | |
ABS(arg) |
Returns the absolute value of a number. |
LOG(arg) |
Returns the natural logarithm of the input value. |
SQRT(arg) |
Returns the square root of the input value. |
POWER (arg1, arg2) |
Returns the first number raised to the power of the second. |
EXP (arg) |
Returns e raised to the power of the argument. |
SQL Operators | |
CASE test-expr WHEN value-expr-1 THEN result-expr-1 . . . WHEN value-expr-n THEN result-expr-n [ ELSE else-expr ] END |
Allows a value to be computed depending on the values of expressions. |
CASE WHEN cond-1 THEN result-expr-1 . . . WHEN cond-n THEN result-expr-n [ ELSE else-expr ] END |
Allows a value to be computed depending on the truth or falsity of conditions. |
COALESCE(arg1, arg2, [ ... argn ]) |
Can take two or more arguments. The first argument that is not NULL, is returned. If all arguments are NULL, NULL is returned. |
EXISTS(SELECT * FROM LOOKUP_MFD SQ [WHERE condition] ) |
Tests if subquery returns one or more rows.
|
NULLIF(arg1, arg2) |
Returns NULL if its two arguments are equal. Otherwise, the first argument is returned. |
test_exp1 IN (exp1, exp2) |
Determines whether a specified value matches any value in a list. |
(SELECT lookup_result from LOOKUP_MFD [WHERE condition]) |
Used in a select list to return a column value. |
(SELECT * FROM LOOKUP_MFD SQ [WHERE condition] ) |
Returns result set based on subquery.
|
(test_exp1, test_exp2) IN ( SELECT exp1, exp2 FROM mfd WHERE condition) |
Determines whether a specified value matches any value in a subquery. |
iWay Software |