Output Information Settings

(1) Extractions and operations that use functions

You can define the Input field, Fixed or Query value as the output fields.

You can extract and convert each data by using the functions.

DataMagic has functions to process the output data according to the content of the input data. The table below lists the functions currently provided. For details about the functions, see DataMagic Reference Manual.

You can specify multiple functions and have them pass their processing result from one to another. This is called "multi-line specification" of functions. For details about how to specify a "multi-line specification", see DataMagic Reference Manual.

Table 3.5 Functions

Function name	Description
CALC_MONTHS	Converts the date specified in the argument to a date where one or more months are added to or subtracted from the date.
CHK_LENB	Checks whether the length of character string data exceeds the maximum length, and returns the specified character string.
COALESCE	Returns the first non-null character inside the items of the argument.
DISPLAY_TO_Z	Converts a signed numeric value (in COBOL DISPLAY format) to a numeric character string.
FORMAT_DATE	Converts the target string to the date format specified in the argument.
FORMAT_NUM	Converts the target string to the format of the numeric value specified in the argument.
IF	If the condition is met, the function returns the corresponding result. If no condition is met, the function returns the default value.
ITOM	Converts type I data to character data.
JULIANDAY	Converts the mapped character string to the corresponding date in the Julian calendar. If a date is specified as the argument, this function converts the date to the corresponding date in the Julian calendar.
LENB	Obtains the length of the data in bytes, which can be used to check whether the number of fields in a CSV matches the specified value.
MATCH_REG	Judges if the mapped character string meets the specified condition, and replaces the character string with another character string according to the judgment result.
REPLACE_DB	Issues the SQL statements specified as the argument to search database tables, and replaces the value with that of the search results.
REPLACE_REG	Searches for the character string specified in the argument, and replaces the string with another character string.
SEQNO	Based on the rule specified in the argument, outputs a sequential number.
SUBSTR	Extracts a substring whose number of bytes is specified in the argument.
TO_INCODE	Converts the target character string to the input character encoding by using the method specified in the argument. External characters and user table codes cannot be used. In DataMagic Desktop grade, the TO_INCODE function cannot be used.
Z_TO_DISPLAY	Converts a numeric character string to a signed numeric value (in COBOL DISPLAY format).

(2) Numeric operation and date operation

When the input field is the numeric data type or date type, you can define the result of the operation for each field as the output.

(3) Specifying SQL executed for the query value

You can specify SQL executed for the virtual table query value set in the Virtual Table Settings. For details about how to create the virtual tables, see Setting virtual tables.

 

To obtain values from the virtual tables, in the Output Settings dialog box of the Output Settings screen in the Data Processing Settings screen, select Query value and specify an SQL statement. You can specify any SQL statement to Query value, and in case multiple values meet the search condition, the first value that meets the condition is returned. If there are no records that meet the search condition, you can select in the setting of No-hit behavior whether to treat this as an error, or not to treat it as an error but treat the data as null data.

(4) Notes about virtual query

• The SQL statement specified for the query value is not checked for validity. Therefore, if the settings are not correct, the conversion results in an error.

• If the value is a floating point data, the SQL statement returns a value with 9 digits after the decimal point. Therefore, if you want to specify a layout for the numeric value, set the output format.

• In the case of executing SQL for the query value, if multiple columns to SELECT are specified in a fixed query SQL statement, the value of the first specified column is returned.

• When specifying a virtual table name and a virtual table field name in a SQL statement, enclose those names in [ ]. When specifying a fixed query SQL statement, if the name of the virtual table and the name of the virtual table field are not enclosed in [ ], the operation is not guaranteed.

  Ex.) select[col1]from[vt]

• When you specify a field, which has been taken into the virtual table as a binary type, in a WHERE clause, if the comparison target is not a binary type, the desired data cannot be obtained from the virtual table correctly.

• If you specify "$VAR", "$INUM", "$INAME", "$DNUM", "$DSTR", "$MINUM" or "$RCOUNT" in a query value, it is expanded as a value. It is not handled as the column name of the virtual table. Also, the maximum number that can be specified is 999. If you specify DM_REC_COUNT, it is expanded as the record number of the virtual table.

(5) Conversion using common components

In settings of output fields, you can use generalized processing (common components) that can be reused by various data processes to extract and transform data.

To use a common component for data processing, specify a common component ID on the Output Information Settings screen.

In DataMagic, frequently used processing is provided as common components.

For details about how to specify common component IDs, see Settings for common component information.

A common component that you created is registered with a common component ID. To use the common component in the output settings, specify the corresponding common component ID.

On the Output Information Settings screen within the Mapping Information Settings screen, you can specify up to 5 arguments for a common component ID.

(6) Settings of conversion that uses a user exit program

You can use the user-created user exit program.

For details about how to create a user exit program, see Creating a user exit program.