On the Options Tab, you can specify a number of options relating to the load.
The options here are discussed in Load Method
If you only want this done for some columns:
For Oracle, Sybase and SQL Server, AQT will use the Truncate Table command; for other databases, AQT will use Delete from Table.
This option cannot be specified when the load is running in Update mode.
This can also be achieved by coding the mapping function rep([comma],[dot]) for all numeric columns.
This can also be achieved by coding the mapping function rep([comma],) for all numeric columns.
This option should be used with caution - when it is specified you will loose some data. However it can be useful if you are loading "unclean" data and prefer a truncated row to be loaded, rather than the row being rejected.
When Report on this is selected, AQT will write to message to the Report file every time a value is truncated.
This option is useful when loading data from a File or an Excel File. Often a zero length (or missing) value is used to represent Null. When this option is selected, AQT will load such values as Null. For instance the following line in a csv file:
SMITH,,0,,FRED
will be loaded as:
SMITH,null,0,null,FRED
When this option is specified, this is done for all columns being loaded. If you want this to be done for some (but not all), columns, you can:
This option is only in effect when data is loaded from a file or Excel file. When the data is loaded from a table or query, zero-lengths values in the source table/query will be loaded as zero-length values in the target table. If these need to be loaded as nulls, you should use the nib function as discussed above.
Note that this option can equally be acheived using Column Functions - a Column Type of char and a Function of nib will also do this.
If you are loading into an Oracle Not Null column you may wish to use the function sin (space if null) instead of this. This will load a single space when the input value is Null or a zero-length string.
You can use this to specify a number of conditions. When any condition is satisfied, the input data is ignored.
The format of a condition is:
value1:condition:value2
For both value1 and value2 you can refer to the columns of the input data, as per mapping specs. If value2 is not specified, it is taken as blank.
You can have multiple conditions, separated by a comma
Examples:
<1>:eq:N/A |
Ignore lines where the first column is N/A |
<3:num>:lt:10 |
Ignore lines where the value in column 3 is less than 10 |
<1>:eq,<1>:co:null |
Ignore lines where column 1 is blank, or column 1 contains the string "null". |
Values condition can take are as follows:
eq |
Equal to |
ne |
Not equal to |
co |
Contains. True if value1 contains string value2. Example: <2>:co:XX |
lt |
Less than |
le |
Less or equal to |
ge |
Greater or equal to |
ne |
Not equal |
neq |
Numeric equal. This will return true if the values are numerically equal. Example: 12.00:neq:12 will return True |
nne |
Numeric not equal |
Example: <2:num>:lt:10
This specified how frequently AQT is to commit the inserted/updated rows to the database.
Notes:
Unable to start a transaction: [ ] [ ] Attribute cannot be set now.
The resolution to this is to use a Commit Frequency of Every Row.
then the load will be done within a transaction. After the load has completed you will need to do a Commit or Rollback to commit or rollback the changes.
These options are described in Create new table
This provides a mechanism to apply a particular mapping function to all columns of a particular type. This is a big time saver if you have many columns you need to apply a mapping function to.
In Column Type enter the name of a column type. You can use a partial type. For instance CHAR will apply to both VARCHAR and CHARACTER. If you prefix the column type with an * it must be an exact match (eg. *CHAR only matches a column type of CHAR). Column Type can be all for it to apply to all values irrespective of the data type.
In Function enter the mapping function, as described in Map to target from Source.
Examples
Column Type |
Function |
Comments |
date |
date(d) |
Applies the date(d) function to all date and datetime columns |
*date |
ifnull(1900-01-01) |
Will load 1900-01-01 into all date columns if the input is Null. |
char |
lcase |
Will load all character values as lower case |
int |
ifnull(0) |
WIll load 0 into all integer, smallint, bigint column if the input is Null |
date |
rep([dot],-):date(d) |
Will load date values when a period is used as field separator. |
all |
nullif(-) |
Will load nulls if the value is - (for all column types) |