DB2创建一个自定义的带输入参数函数,参数为字符串如:10,16,25 这样的一个参数主要是在函数中做in(参数)查询的,sql代码附上,函数可以创建成功但是调用的时候如果参数是: 10 一个这样的就可以执行,如果是:10,16,25 这样的调用select *from table(TASK_EXECUTE_FUN('10,16,25'))as D就报错,错误如下:
[SELECT - 0 row(s), 0.000 secs] [Error Code: -420, SQL State: 22018] DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=DECFLOAT, DRIVER=3.57.82
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
函数代码:
create OR REPLACE function TASK_EXECUTE_FUN(userIds VARCHAR(1000))
returns table
(
ID1 INT,
ID2 INT,
TASK_ID INT,
EXECUTE_NAME VARCHAR(255),
EXECUTE_USER_ID INT
)
SPECIFIC TASK_EXECUTE_FUN
RETURN
select row_number()over(partition by executes.PARENT_TASK_ID order by executes.PARENT_TASK_ID) id1,
row_number()over(partition by executes.PARENT_TASK_ID order by executes.PARENT_TASK_ID) id2,
executes.PARENT_TASK_ID as TASK_ID,excName.USER_NAME as EXECUTE_NAME,executes.EXECUTE_USER_ID as EXECUTE_USER_ID from TSK_TASK as executes
left join SYS_USER as excName on executes.EXECUTE_USER_ID=excName.USER_ID
WHERE executes.PARENT_TASK_ID<>0 AND executes.EXECUTE_USER_ID in(userIds)
请教各位大侠! 不胜感激,很是奇怪的问题。。