ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Goldengate 列转换样例

Goldengate 列转换样例

原创 Linux操作系统 作者:paulyibinyi 时间:2012-03-29 17:25:22 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-719849/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
oracle 10g ocm oracle 10g/11g/12c ocp aix 6.1 administrator,ogg expert,ITSS 技术交流群 201703254 微信公众号 paulyibin 探讨技术,开心工作 电话 13719354869 ,深入研究数据库和开始研究big data

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6505063