首页 新闻 搜索 专区 学院

DB2 FUNCTION 报错,执行环境 是 eclipse 的 DataManagent

0
悬赏园豆:5 [已关闭问题] 关闭于 2011-12-12 14:48

CREATE TABLE TARGET
(
PK INTEGER NOT NULL PRIMARY KEY,
C1 INTEGER
);
CREATE TABLE EXCEPT1
(
PK INTEGER NOT NULL PRIMARY KEY,
C1 INTEGER
);
CREATE TABLE SOURCE
(
PK INTEGER NOT NULL PRIMARY KEY,
C1 INTEGER
);
SELECT
* FROM TARGET;
SELECT
* FROM EXCEPT1;
SELECT
* FROM SOURCE;

-- 创建一个方法
DROP FUNCTION DISCRETIZE;
--<ScriptOptions statementTerminator="@"/>
CREATE FUNCTION DISCRETIZE(RAW INTEGER) RETURNS INTEGER
RETURN CASE
WHEN RAW
< 0 THEN CAST(NULL AS INTEGER)
WHEN RAW
> 1000 THEN NULL
ELSE ((RAW
/10)*10)+5
END

--
INSERT INTO SOURCE(PK, C1)
VALUES(
1,-5),
(
2,NULL),
(
3,1200),
(
4,23),
(
5,10),
(
6,876);

SELECT
* FROM SOURCE;
SELECT
* FROM TARGET;
SELECT
* FROM EXCEPT1;

BEGIN ATOMIC
FOR ROW AS
SELECT PK,C1,DISCRETIZE(C1) AS D FROM SOURCE
DO
IF ROW.D IS NULL THEN
INSERT INTO EXCEPT1 VALUES(ROW.PK,ROW.C1);
ELSE
INSERT INTO TARGET VALUES(ROW.PK,ROW.D);
END IF;
END FOR;
END

--
SELECT
* FROM EXCEPT1;
SELECT
* FROM TARGET;

问题补充: 报错信息如下: BEGIN ATOMIC FOR ROW AS SELECT PK,C1,DISCRETIZE(C1) AS D FROM SOURCE DO IF ROW.D IS NULL THEN INSERT INTO EXCEPT1 VALUES(ROW.PK,ROW.C1) ELSE INSERT INTO TARGET VALUES(ROW.PK,ROW.D) END IF END FOR END Elapsed Time: 0 hr, 0 min, 0 sec, 0 ms.
标准小兵的主页 标准小兵 | 菜鸟二级 | 园豆:205
提问于:2011-04-12 17:55
< >
分享
所有回答(1)
0

已经解决了,不需要广告啊。

标准小兵 | 园豆:205 (菜鸟二级) | 2011-12-12 14:46
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册