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
In the RIGHT pane
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.
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 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 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)
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:
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 |
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.
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 |
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]) |
' |
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.
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.
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.