UDF在group by 里出现时，报SQL0583的错

问题重现：

db2 CREATE TABLE T(SAL INTEGER)

db2 INSERT INTO T VALUES(12)
db2 INSERT INTO T VALUES(16)
db2 INSERT INTO T VALUES(20)
db2 INSERT INTO T VALUES(112)
db2 INSERT INTO T VALUES(125)
db2 INSERT INTO T VALUES(182)
db2 INSERT INTO T VALUES(120)
CREATE FUNCTION HUANGDK.money_l( V_MONEY DECIMAL(10,3) )
RETURNS INTEGER
SPECIFIC HUANGDK.money_l
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
RETURN
--根据输入的钱数返回费用分档
CASE WHEN V_MONEY>0 AND V_MONEY<=100 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),5)=0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/5*5
WHEN V_MONEY>0 AND V_MONEY<=100 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),5)>0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/5*5+5
WHEN V_MONEY>100 AND V_MONEY<=1000 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),10)=0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/10*10
WHEN V_MONEY>100 AND V_MONEY<=1000 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),10)>0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/10*10+10
WHEN V_MONEY>1000 AND V_MONEY<=3000 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),100)=0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/100*100
WHEN V_MONEY>1000 AND V_MONEY<=3000 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),100)>0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/100*100+100
ELSE -1
END ;
END
E:>db2 SELECT MONEY_L(SAL),sum(sal) FROM T GROUP BY MONEY_L(SAL)
SQL0583N 使用例程 "HUANGDK.MONEY_L"
是无效的，因为它是不确定的或具有外部操作。 SQLSTATE=42845

如何解决呢？其实很简单：

CREATE FUNCTION HUANGDK.money_l( V_MONEY DECIMAL(10,3) )
RETURNS INTEGER
SPECIFIC HUANGDK.money_l
DETERMINISTIC
NO EXTERNAL ACTION
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
RETURN
--根据输入的钱数返回费用分档
CASE WHEN V_MONEY>0 AND V_MONEY<=100 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),5)=0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/5*5
WHEN V_MONEY>0 AND V_MONEY<=100 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),5)>0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/5*5+5
WHEN V_MONEY>100 AND V_MONEY<=1000 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),10)=0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/10*10
WHEN V_MONEY>100 AND V_MONEY<=1000 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),10)>0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/10*10+10
WHEN V_MONEY>1000 AND V_MONEY<=3000 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),100)=0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/100*100
WHEN V_MONEY>1000 AND V_MONEY<=3000 AND MOD(CAST(CEIL(V_MONEY) AS INTEGER),100)>0 THEN CAST(CEIL(V_MONEY) AS INTEGER)/100*100+100
ELSE -1
END ;
END


E:>db2 SELECT MONEY_L(SAL),sum(sal) FROM T GROUP BY MONEY_L(SAL)

1 2
----------- -----------
15 12
20 36
120 232
130 125
190 182

5 条记录已选择。


E:>

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

转载于:http://blog.itpub.net/5718/viewspace-168283/