Oracle Goldengate列转换样例

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:34

Oracle 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

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】