问个关于处理NULL的问题

初级会员 注册时间: 2008-04-25 09:51:27 文章: 15 离线

SOURCE中有个列叫'MAX_QTY', STRING,
TARGET中名字一样,但变成了NUBMER,
而且,SOURCE中的'MAX_QTY'里还有很多没有值的,空着.
如果只是把用TO_DECIMAL, 运行后报错:
**************************************************
WRITER_1_*_1> Wed May 28 07:10:40 2008
WRITER_1_*_1> WRT_8229 Database errors occurred:
ORA-01400: cannot insert NULL into ("ACTIVEBI_STG"."SPECIAL_OFFER"."MAX_QTY")

Database driver error...
Function Name : Execute
SQL Stmt : INSERT INTO SPECIAL_OFFER(SPECIAL_OFFER_ID,DESCRIPTION,DISCOUNT_PCT,TYPE,CATEGORY,START_DATE,END_DATE,MAX_QTY,MIN_QTY,MODIFIED_DATE) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO SPECIAL_OFFER(SPECIAL_OFFER_ID,DESCRIPTION,DISCOUNT_PCT,TYPE,CATEGORY,START_DATE,END_DATE,MAX_QTY,MIN_QTY,MODIFIED_DATE) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
WRITER_1_*_1> Wed May 28 07:10:40 2008
WRITER_1_*_1> WRT_8114
Row # [4] in bad file
WRITER_1_*_1> CMN_1053 : Rowdata: ( RowType=0(insert) Src Rowid=8 Targ Rowid=8
SPECIAL_OFFER_ID (SPECIAL_OFFER_ID:Double:): "8.000000000000000"
DESCRIPTION (DESCRIPTION:Char.255:): "Sport Helmet Discount-2002"
DISCOUNT_PCT (DISCOUNT_PCT:Double:): "0.100000000000000"
TYPE (TYPE:Char.50:): "Seasonal Discount"
CATEGORY (CATEGORY:Char.50:): "Reseller"
START_DATE (START_DATE:Date:): "07/01/2002 00:00:00"
END_DATE (END_DATE:Date:): "07/31/2002 00:00:00"
MAX_QTY (MAX_QTY:Double:): "(NULL)"
MIN_QTY (MIN_QTY:Double:): "0.00000000000000"
MODIFIED_DATE (MODIFIED_DATE:Date:): "06/01/2002 00:00:00"
)
************************************
我该怎么处理那些NULL的地方,使SOURCE中的值都可以转化成NUMBER进入TARGET,如果是NULL的话就显示NULL.

先谢谢了.

DECODE(i_MAX_QTY,NULL,0,TO_DECIMAL(i_MAX_QTY))
类似这样的方式
在F(X)里处理

如果是NULL的话就显示NULL
你这句话是说目标表中这个字段如果源表为空它也显示NULL吗?
如果那样那就要把数据库中该字段设置为可以为空就可以了