Map to target from source

This tab is used to specify how the data in the Target table is loaded from the data in the load source.

In the LEFT pane

this shows the columns in the Target table. This list is populated once the Target table is specified.

In the RIGHT pane

the Source Columns will be populated once the load source has been specified.
by manipulating the entries in the right grid, you specify how each Target Column is to be loaded. As you change the entries, a sample of the data will be displayed in the right-most column of the grid.
You can move columns up and down in the right grid. Click and drag the grey grid-numbers to move the grid-rows. This is very useful if the columns in your target table and source are in different orders.
The most important column in the right grid is Load Spec. This says how the Target Column is to be loaded. For instance <4> says the target column is to be loaded from the fourth column of the load source. You can amend Load Spec to define more complex mapping and formatting rules. This is discussed in more detail later.
Use Insert Row to add another row to the right grid. This is useful if your target table has more columns than the source table.
Use Delete Row to remove a row from the right grid. This is useful if your target table has fewer columns than the source table.
The Default Extra button is useful if the target table has many extra rows than the source. This button will add rows to the right grid so it has as many rows as the left grid. Each extra column will be loaded with a default value for its data type.
The Reset button will reset all the Load Specs to their default values (which is <1>, <2> etc).
When you change the load source, the entries in the right grid will be reloaded to reflect the new load source. However the Load Spec will not be changed. This allows you to change the load source to another file/table, without loosing any complex mapping you have set up. Click on the Reset button the reset the load spec to the default values.

Automatic Mapping

The Auto button can be used to Automatically map source and target columns. This mapping is based on column name. When this button is clicked, AQT will go through all the columns in the target table and see if there is a column in the source with the same name. If so, the mapping for the target column is changed to map to that source column. If the target column is not found in the source, the mapping for the target column is left unchanged.

This function is very useful if you are loading between tables which have the same columns, but they are in different orders.

Define load specifications

Load Spec gives the Load Specification for the table column. By default this will be a value in angle brackets such as <4>, which means column 4 from the source. You can specify other things here to manipulate the data, for example:

0	Load the value 0
TEST	Load the value TEST
<3><4>	Load source column 3, concatenated with source column 4
20<3>	Load the string 20 followed by source column 3
<3:left(4)>	Load the leftmost 4 bytes of source column 3
NULL	Loads a null value

The Load Spec can have any mixture of text and source column specifications.

The way AQT processes these is quite simple: AQT will scan the load spec and replace any source column specifications with the data from the source. The source column specification is in the format <colno> or <colno:func>. Func applies a function to the column value.

Valid functions that can be specified are:

lcase	lowercase string
ucase	uppercase string
scase	sentence case (first letter of a word is upper case, rest is lower case)
left(n)	leftmost n characters
right(n)	rightmost n characters
mid(m)	rightmost part of string from character m
mid(m,n)	n characters starting from character m
before(string)	the text preceding the first occurrence of string. Enclose string in quotes if it contains a special character or blank. Examples: before(s), before(" ").
beforelast(string)	the text preceding the last occurence of string.
after(string)	the text following the last occurrence of string.
afterfirst(string)	the text following the first occurrence of string.
ltrim(x)	trim character x from the start of the string. If x is not given (eg. ltrim), spaces are removed from the start.
rtrim(x)	trim character x from the end of the string. If x is not given (eg. rtrim), spaces are removed from the end.
trim	trim spaces from the start and end of the string
remove(n)	remove the rightmost n characters from the string
date(datespec,scale)	converts dates, times and datetime values from one format to another. Described in more detail below.
date2(datespec,scale)	date2 performs the same function as date however uses a different internal method. This is more reliable in handling dates such as 0001-01-01. The Date function has been retained for backward compatibility, however date2 is a more reliable function.
mask(mask)	Provides a powerful way of manipulating data in a column. Described in more detail below.
nib	null if blank. If a blank value is specified, it is loaded as nulls. See also the Treat Zero-length Values as NULL option
nis	null if spaces. Similar to nib. See discussion later.
sin	space if null. Will load a single space (eg. string of length 1) if the input value is either null or a string of length 0. This is useful for loading values into an Oracle Not Null column - for Oracle a string of length 0 is regarded as Null so will not be loaded into a Not Not column.
rep(s,t)	replaces all occurrences of string s with string t. Example: rep(Street,Road). See the section on Special Characters to see how to replace characters such as the comma, bracket etc.
trans(a,b)	translates string values. See discussion below.
ifnull(s)	returns value s if the input value is NULL. If you omit the (s) (eg. just have nullif), a zero-length string will be returned.
ifblank(s)	returns value s if the input value is blanks.
ifempty(s)	returns value s if the input value is a zero-length string.
lpad(n,s)	pads the string with extra characters on the left. The string is padded with multiple s characters until it is of length n.
rpad(n,s)	pads the string with extra characters on the right. The string is padded with multiple s characters until it is of length n.
nullif(a) nullif(a,b,c,d,e)	null if the string equals a. You can specify up to 5 values - will return null if the string equals any of them. Example: nullif(DNF,DNS,-)
num	converts the numeric value to a consistent representation of a number. The thousands separator will be removed, and a period used as a decimal separator. Example: on systems where the decimal separator is a comma: 123,456 will be converted to 123.456 1.234,56 will be converted to 1234.56 Example: on systems where the decimal separator is a period: 123,456 will be converted to 123456
trunc(n)	the numeric value is truncated at the given number of decimal places. trunc(2) will convert 1234.5678 to 1234.56 On systems where the decimal separator is a comma, AQT will first run the num function to turn the value in to a consistent format using the period as a decimal separator. trunc(0) will remove all the decimal places.
round(n)	similar to trunc but the value will be rounded rather than truncated. round(2) will convert 1234.5678 to 1234.57
deperc	this converts a percentage value to a numeric value. If the value is a numeric value plus a % sign, the % will be removed and the value divided by 100. Otherwise the value will be unchanged. Example: 23.5% will be converted to 0.235
ifnum(function)	if the value is numeric, the function will be applied. Example: ifnum(rep([comma],) This will remove commas from numeric values. If not numeric, the value is unchanged.

Let us know at support if there are any other functions that you might find useful (they are easy to implement).

Date

Date takes any string that Windows can interpret as a date, time or timestamp, and formats it as defined by datespec. This is a useful function when copying date, time and timestamp values between databases of different types.

Datespec can be any combination of yyyy (year), MM (month), dd (day), HH (hour), mm (minutes), ss (seconds) and xxx (fractions of a second). For compatibility with previous versions of AQT, nn can also be used for minutes,

In addition, there are a number of pre-defined formats for the common date / time / timestamp formats.

Datespec	Format	Example
d	yyyy-MM-dd	2010-12-31
t	HH:mm:ss	13:14:15
dt1	yyyy-MM-dd HH:mm:ss	2010-12-31 13:14:15
dt2	yyyy-MM-dd-HH:mm:ss	2010-12-31-13:14:15
dt3	yyyy-MM-dd-HH.mm.ss	2010-12-31-13.14.15
dt4	yyyy-MM-dd HH.mm.ss	2010-12-31 13.14.15

If no datespec is coded, AQT will use a datespec of dt1. This is the timestamp format used by most databases. It is expected that dt2, dt3, and dt4 would be rarely used.

If you are coding your own datespec, you will need to use [colon] rather than a colon, as the colon is used to delimit multiple loadspecs (this is discussed later). Example:

yyyy-MM-dd HH[colon]mm[colon]ss

Scale

Scale is used when loading timestamp values and gives the number of places after the decimal point. Example:

date(dt1,6) would result in 2010-12-31 13:14:15.123456

If the scale is not given, the number of decimal places used is the same as for the input data.

You can also specify scale by using xxxx as part of the datespec. The following two Date functions give the same result:

date(dt1,3)

date(yyyy-MM-dd HH[colon]mm[colon]ss.xxx)

Mask

This function provides a simple and powerful method for manipulating a string value. It is particularly useful for changing the order of values in a string.

You pass to the Mask function a mask string - this says how the data in the result string is to be formatted. The mask string consists of a series of characters:

a lower-case letter. The character in the result-string will be taken from the nth character of the input string, where n is the alphabetical position of the letter. For instance a specification of cab will convert Joe to eJo (third character, first character, second character). You cannot refer to any character beyond the 26th.
% - copy all the remaining characters from the input string to the result string
any other character - copy this character to the result string

The following table gives an example of how this is used.

Input Value	Mask	Resultant Value
20041201	abcd-ef-gh	2004-12-01
20041201	abcdghef	20040112
2004-12-01	abcdfgij	20041201
12-01 14:31:27	de-ab%	01-12 14:31:27

Trans

The trans function is used to translate string values. In the example of trans(abc,def), AQT will replace all occurrences of a in the string with d, all occurrences of b with e, and all occurences of c with f.

If the second string is shorter than the first string, the values are removed from the string.
If your translate-strings have any special characters (such as a comma, blank, brackets etc), enclose your translate-strings in double quotes (eg. trans("abc","def")).
For other special characters, see the section below on Special Characters.

Input Value	trans	Resultant Value
this is a test	trans(this,1234)	1234 34 a 1e41
2004/12/01 12.23	trans("/.","-:")	2004-12-01 12:23
[23]	trans("[]","()")	(23)
$23,456	trans("$,",)	23456

Special Characters

You can specify special characters using one of the following codes. It is useful to use these as some of these characters (quotes, brackets, colon) are used in the syntax of the load specs, so including them in a function can cause AQT to interpret the load spec incorrectly.

Code	Value
[squote]	'
[dquote]	"
[comma]	,
[semi]	;
[none]	empty string
[lbrak]	(
[rbrak]	)
[colon]	:
[dot]	.
[lf]	line-feed. This is normal method of indicating a new-line for Unix files.
[crlf]	carriage-return+linefeed. This is normal method of indicating a new-line for Windows files.
[tab]	tab character

Example:

Input Value	Function	Resultant Value
12,34	rep([comma],[dot])	12.34
	ifblank([squote])	'

Include

If there is a column in your table from which you do not want data to be loaded, de-select the Include check-box for that column. The column will not be included in the load. Instead, the data will be taken from the default or auto-generated value for the column.

Using more than one function

You can "string" a number of functions together. For instance:

<2:left(5):lcase>

This will do the left(5) on column 2, then do the lcase on the result.

You can string together up to 5 functions.

nib and nis

nis was introduced to deal with the following problem. Suppose you want to transform a date using mask, but to load null when the value is blank. You might try to code:

<2:mask(abcd-ef-gh):nib>

This doesn't do what you want; when <2> is blank <2:mask(abcd-ef-gh)> is ' - - ' which nib doesn't recognise as blank.

In this case use nis, eg: <2:mask(abcd-ef-gh):nis>. nis the same as nis except that it looks at the original value of the column <2> rather than the value as transformed by the previous function.

Advanced Query Tool

https://www.querytool.com