Goldengate 列轉換樣例

Goldengate提供的Column Conversion Functions中包括對應的num轉str，str轉num等函式，如下：

如果OGG提供的函式不能滿足需求，我的想法是還能利用oracle本身的函式寫對應的query語句來完成轉換，比如
jy_date   date
jy_date_str   varchar2

MAP sales.account, TARGET sales.account, SQLEXEC (ID lookup, QUERY "select to_char(jy_date, 'yyyy-dd-mm') into target_col from account",
COLMAP (newacct_id = account_id, jy_date_str = lookup.target_col);

 

NUMSTR
Use the @NUMSTR function to convert a string (character) column or value into a number.
Use @NUMSTR to do either of the following:
● Map a string (character) to a number.
● Use a string column that contains only numbers in an arithmetic expression.
Syntax @NUMSTR ()
Example PAGE_NUM = @NUMSTR (ALPHA_PAGE_NO)

STRNUM
Use the @STRNUM function to convert a number into a string and specify the output format
and padding.
Syntax @STRNUM (, {LEFT | LEFTSPACE, | RIGHT | RIGHTZERO} [] )
Argument Description
The name of a string (character) column or a literal string. Enclose literals
within quotes.
The maximum string length, in characters.
Argument Description
The first string to be compared.
The second string to be compared.
The maximum number of characters in the string to compare.
Argument Description
The name of a source numeric column.
LEFT Left justify, without padding.
LEFTSPACE Left justify, fill the rest of the target column with spaces.
RIGHT Right justify, fill the rest of the target column with spaces. If the value of a column
is a negative value, the spaces are added before the minus sign. For example,
strnum(Col1, right) used for a column value of -1.27 becomes ###-1.27, assuming the
target column allows 7 digits. The minus sign is not counted as a digit, but the
decimal is.
RIGHTZERO Right justify, fill the rest of the target column with zeros. If the value of a column
is a negative value, the zeros are added after the minus sign and before the
numbers. For example, strnum(Col1, rightzero) used for a column value of -1.27
becomes -0001.27, assuming the target column allows 7 digits. The minus sign is
not counted as a digit, but the decimal is.
Specifies the output length, when any of the options are used that specify padding
(all but LEFT). For example:
◆ strnum(Col1, right, 6) used for a column value of -1.27 becomes ##-1.27. The minus
sign is not counted as a digit, but the decimal is.
◆ strnum(Col1, rightzero, 6) used for a column value of -1.27 becomes -001.27. The
minus sign is not counted as a digit, but the decimal is.

Example Assuming a source column named NUM has a value of 15 and the target column’s maximum
length is 5 characters, the following examples show the different types of results obtained
with formatting options.
Function statement Result (# denotes a space)
CHAR1 = @STRNUM (NUM, LEFT) 15
CHAR1 = @STRNUM (NUM, LEFTSPACE) 15###
CHAR1 = @STRNUM (NUM, RIGHTZERO) 00015
CHAR1 = @STRNUM (NUM, RIGHT) ###15
Function statement Result (# denotes a space)
CHAR1 = @STRNUM (NUM, LEFTSPACE, 4) 15##
CHAR1 = @STRNUM (NUM, RIGHTZERO, 4) 0015
CHAR1 = @STRNUM (NUM, RIGHT, 4) ##15