Previous Topic

Next Topic

Book Contents

Book Index

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

In the RIGHT pane

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)

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:

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.

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.