Selecting Columns

The columns you select define the input to your data target. If you are using the Data Management Consoleibi Data Migrator desktop interface 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 Server, which ibi Data Migrator 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.

Automatically Select All Columns

Procedure
1. On the Home tab, in the Tools group, click Options.
2. Expand Data Flow and select Data Flow Designer from the tree.
3. Select the Automatically select all columns checkbox in the On Add Source section.
4. Click OK.

Select Columns

Procedure
1. Right-click the SQL object in the data flow workspace and click Column Selection.

   The Column Selection dialog opens.

2. Double-click the columns you want to select from the Available Columns grid on the left.

   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.

3. You can change the order of the columns by selecting them and clicking the up and down arrows.
4. Optionally, you can select the Distinct checkbox to remove duplicate rows from the results of your query.
5. You can rename a selected column by entering a name in the Sql Alias field.
6. To remove a column from the Selected Columns grid, select it and click the left arrow, or click the Delete button.
7. Click OK.

Edit Selected Columns

Procedure
1. Right-click the SQL object in the data flow workspace and click Column Selection.

   The Column Selection dialog opens.

2. Select the column you want to edit.
3. Click the Edit column button, or double-click the number next to the column in the Selected Columns grid.

   The SQL Calculator opens.

4. Using the Columns/Variables and Functions tabs and the calculator buttons, edit the expression for the column. For information about using functions, see Using an SQL Function. For information about using variables, see Using Variables in a Flow.
5. Click OK.

Add or Change Column Descriptions From the Column Selection dialog

Procedure
1. From the data flow workspace, double-click the SQL object, or right-click it and click Column Selection.

   The Column Selection dialog opens.

2. Double-click the Description field for the column in the Selected Columns grid.
3. Type the description.
4. Click OK.

Column Selection dialog

To access the Column Selection dialog from the data flow workspace, double-click the SQL object, or right-click it and click Column Selection.

The Column Selection dialog contains the following fields and options by default:

Distinct

Removes duplicate rows from the results of your query.

Available Columns

Lists the columns in each data source.

Right arrow

Adds columns to the Selected Columns grid.

Left arrow

Deletes columns from the Selected Columns grid.

Selected Columns

Lists the columns to be extracted from the data source(s).

Sql Expression

Is the column name, or an expression created in the SQL Calculator.

Sql Alias

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.

Format

Is the format of the column.

Aggregate

Is the aggregation applied to the column.

Description

Is the description of the column.

Nulls

Indicates whether the column can contain NULL values.

Find

Allows you to search for a column.

Insert columns

Opens the SQL Calculator to create a column.

Delete columns

Deletes the selected column.

Edit condition

Opens the SQL Calculator to edit a column.

Test SQL Statement

Tests the SQL statement and displays the result.

Note: The test button will be grayed out if you have not selected any columns.

Up and down arrows

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:

Find

Allows you to search for a column.

Best Fit

Resizes the column to fit its data.

Customize

Allows you to select what information is displayed for each column.

If you right-click any column, you get the following options:

Edit

Opens the SQL Calculator where you can edit the properties of a column.

Delete

Deletes the selected column.

Aggregating Columns

You can aggregate your columns in the Column Selection dialog.

Aggregate a Column

Procedure
1. Select the column or columns you want to aggregate. To select more than one column, hold the Shift or Ctrl keys while selecting columns.
2. Click the down arrow in the Aggregate field to display the dropdown menu containing the types of aggregation, and select an aggregation. Some of your options are:
   • Group By. Groups the data by each distinct value in the column.
   • Group By Not Selected. Groups the data on (or by) the column that you do not want to appear in the results.
   • Sum. Adds the values within the column.
   • Min. Returns the minimum value found in the column.
   • Max. Returns the maximum value found in the column.
   • Count. Returns a count of all non-null column values.
   • Avg. Returns the average value for the column.
   • Sum Distinct. Adds the distinct values within the column, excluding duplicates.
   • Count Distinct. Returns a count of all distinct, non-null column values, excluding duplicates.
   • Avg Distinct. Returns the average value for the column, excluding duplicates.

Creating an SQL Calculation

You can create an SQL calculation for your data flow that:

• Renames a column.
• Performs a calculation. For details, see Create a Calculation.
• Designates a constant value for a column.
• Uses a variable to calculate a column. For details, see Create a Column That Uses a Variable.

Create a Calculation

Procedure
1. In the Column Selection dialog, click the Insert Columns button.

   The SQL Calculator opens.

2. Enter the calculation you want to perform using the Columns/Variables and Functions tabs, and the calculator buttons. For details, see SQL Calculator. For information about using functions, see Using an SQL Function. For information about using variables, see Using Variables in a Flow.
3. In the Alias box, enter an alias for the column.
4. Click OK.

   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.
5. Optionally, select an aggregation for the column in the Aggregate field. For details, see Aggregating Columns.
   Note:

    

   • You can type a calculation directly into the Selected Columns grid by double-clicking the Sql Expressions field.
   • You can type an alias directly into the Selected Columns grid by double-clicking the Sql Alias field.

Create a Column That Uses a Variable

Procedure
1. Click the Insert Columns button.

   The SQL Calculator opens.

2. Type an alias for the virtual column.
3. Double-click the variable you want assigned to the column from the Columns/Variables tab. You can also type in character-valued or date-valued variables, which must be enclosed in single quotation marks (‘).
4. Click OK.

SQL Calculator

To access the SQL Calculator from the Column Selection dialog, click the Insert Columns button, or select a column and click the Edit Column button.

The SQL Calculator contains the following fields and options:

Alias

Is an alias for the column.

Expression window

Displays the expression.

SQL Optimization button

Opens the Filter SQL Optimization Report, which allows you to specify settings for, and run, the report.

Check Expression button

Verifies the validity of the expression.

Test SQL Statement

Tests the SQL statement and displays the result.

Find button

Allows you to search for a function or variable by name.

Columns/Variables tab

Displays available columns and variables in a hierarchical tree or in a grid.

Functions tab

Displays a list of SQL functions that are available for your transformations.

Function Assist button

Allows you to specify parameters for the function through a dialog when creating or editing a transformation.

Calculator buttons

Insert numbers and operators.

( )

Adds parentheses.

''

Inserts two single quotation marks (‘). Enter alphanumeric test values between these.

a->A

Converts selected text to uppercase.

A->a

Converts selected text to lowercase.

Date

Opens the Date Editor dialog, which lets you use the current date or specify a date from the calendar.

Datetime

Opens the Date Editor dialog, which lets you use the current date and time or specify a date and time from the calendar.

SQL Operators

Using an SQL Function

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.

Use an SQL Function

Procedure
1. In the SQL Calculator, click the Functions tab.
2. Double-click the folder for the type of function you want to use.
3. Double-click the function you want to use.

   The Function Assist dialog opens.

4. Use the Function Assist dialog to set the parameters for the function and click OK.
5. The function appears in the Expressions dialog. Click OK.
6. The function is added to the Selected Columns grid of the Column Selection dialog.

For more information about the Function Assist dialog, see Use Function Assist.

SQL Functions

CHAR_LENGTH(arg)

CONCAT(arg1, arg2)

EDIT(arg, mask)

GET_TOKEN(string, delimiter_string, occurrence)

INITCAP(string)

LCASE(arg)

string LIKE mask [ESCAPE escape]

LPAD(string, out_length, pad_character)

LTRIM(arg)

PATTERNS(string)

POSITION(substring IN arg)

REPLACE(string, pattern, [replacement])

REVERSE(string)

RIGHT(source_string, substring_limit)

RTRIM(arg)

string RLIKE regular_expression

RPAD(string, out_length, pad_character)

RTRIM(arg)

SPLIT(part, string)

SUBSTR(arg FROM 

start-pos[FOR length])

SUBSTR(arg, start_pos[, length])

TOKEN(string, delimiter, number)

TRIM(arg)

TRIM(trim_char FROM arg)

TRIM(trim_where[trim_char] FROM arg)

UCASE(arg)

CURRENT_DATE

CURRENT_TIME[(precision)]

CURRENT_TIMESTAMP

[(precision)]

CURRENT_TIMEZONE()

CAST(expression AS data_type[(length)])

CHAR(arg[,date_format])

DATE(arg)

DECIMAL(arg, [length 

[dec_places]])

DIGITS(arg)

DT_FORMAT(arg, date_format)

FLOAT(arg)

FOCDATE(arg, format)

INT(arg)

OLDDATE(arg, format)

PHONETIC(string)

SMALLINT(arg)

TIME(arg)

TIMESTAMP(arg)

VARGRAPHIC(arg)

DAY(arg)

DAYS(arg)

DAY_OF_YEAR(arg)

DTDIFF(end_date, start_date, component)

DTIME

DTRUNC(date_or_timestamp, date_period)

datetime + INTERVAL increment c omponent

EXTRACT(field FROM arg)

HOUR(arg)

MICROSECOND(arg)

MILLISECOND(arg)

MINUTE(arg)

MONTH(arg)

QUARTER(arg)

SECOND(arg)

WEEKDAY(arg)

YEAR(arg)

DB_EXPR(sql_expression )

COUNTBY(arg)

CURRENT_EDASQLVERSION()

GREATEST(arg1,arg2[,...arn])

HEX(arg)

IF(test, val1, val2)

LEAST(arg1,arg2[,...arn])

LENGTH(arg)

USER()

ABS(arg)

CEIL(number)

EXP(arg)

FLOOR(number)

LOG(arg)

MOD(dividend, divisor)

POWER (arg1, arg2)

RAND(seed)

SIGN(arg)

SQRT(arg)

CASE test-expr 

   WHEN value-expr-1 THEN  

result-expr-1 

   . . .

   WHEN value-expr-n THEN  

result-expr-n 

   [ ELSE else-expr ] 

END

CASE

   WHEN cond-1 THEN  

result-expr-1 

   . . .

   WHEN cond-n THEN  

result-expr-n 

   [ ELSE else-expr ] 

END

COALESCE(arg1, arg2, 

[ ... argn ])

EXISTS(SELECT * FROM LOOKUP_MFD SQ [WHERE condition] )

NULLIF(arg1, arg2)

test_exp1 IN (exp1, exp2)

(SELECT lookup_result from LOOKUP_MFD [WHERE condition])

(SELECT * FROM LOOKUP_MFD SQ [WHERE condition] )

(test_exp1, test_exp2) IN ( SELECT exp1, exp2 FROM mfd WHERE condition)

ASQ(field)

AVG(field)

AVG(DISTINCT field)

COUNT(field)

COUNT(DISTINCT field)

MAX(field)

MEDIAN(field)

MIN(field)

MODE(field)

SUM(field)

SUM(DISTINCT field)

AVG(exp) OVER([PARTITION BY part] ORDER BY ord [window])

COUNT(exp) OVER([PARTITION BY part] ORDER BY ord [window])

DENSE_RANK(exp) OVER([PARTITION BY part] ORDER BY ord])

FIRST_VALUE(exp) OVER([PARTITION BY part] ORDER BY ord [window])

LAG(exp, offset, default) OVER([PARTITION BY part] ORDER BY ord])

LAST_VALUE(exp) OVER([PARTITION BY part] ORDER BY ord [window])

LEAD(exp, offset, default) OVER([PARTITION BY part] ORDER BY ord])

MAX(exp) OVER([PARTITION BY part])

MEDIAN(exp) OVER([PARTITION BY part] ORDER BY ord [window])

MIN(exp) OVER([PARTITION BY part])

MODE(exp) OVER([PARTITION BY part] ORDER BY ord [window])

PERCENT_RANK(exp) OVER([PARTITION BY part] ORDER BY ord])

RANK(exp) OVER([PARTITION BY part] ORDER BY ord])

STDDEV_POP(exp) OVER([PARTITION BY part])

STDDEV_SAMP(exp) OVER([PARTITION BY part])

SUM(exp) OVER([PARTITION BY part] ORDER BY ord [window])

CORRELATION(input_field1, input_field2)

STDDEV_POP(field)

STDDEV_SAMP(field)

ACOS(number)

ASIN(number)

ATAN(number)

ATAN2(x,y)

COS(angle)

COT(angle)

DEGREES(angle)

PI()

RADIANS(angle)

SIN(angle)

TAN(angle)