DB2递归UDF表 [英] DB2 recursive UDF Table
问题描述
我有一个值表....
$ b $（
）（$）
，（300）
，（300）
选择*从t;
我需要传递给UDF表函数一个开放值（例如5000）和一个因素0.1）
我需要显示如下结果....
开始调整关闭
（3）（1）（2）
======================== ======================
5000 500 100 5600
5600 560 200 6360
6360 636 300 7296
- Shift = Opening * 0.1
- 关闭=开启+ Shift +关闭
- 开始是上一行的结束
我的递归函数被卡住，当我试图带来带来的...（SQL0345N全选的递归通用表表达式）
欣赏任何想法如何做到这一点。我知道存储过程可以这样做，但是我需要使用UDF，以便可以在另一个UDF中扩展。
我不确定我是否正确地解释了这种情况，但是这里是我所做的：
要开始，我将创建一对夫妇变量来模拟函数的输入，然后组成递归通用表表达式（RCTE），以使用从这些变量获取的数据生成报告：
创建变量OpenedWith十进制默认值5000
;
创建变量Factor decimal（2，1）default 0.1
;
t（调整）为（值（100），（200），（300））
，ordRows（rn，Adjustment）为
（select row_number over（），从t调整
，addRows（rn，Opening，Shift，Adjustment，closing）as
（select rn，OpenedWith，OpenedWith * Factor，Adjustment
，（OpenedWith +打开的*因子）+调整）
从ordRows
其中rn = 1
联合全部
选择b.rn，a.Closing，a.Closing *因子，b.Adjustment
，（a.Closing +（a.Closing * Factor）+ b.Adjustment）
from addRows a
join ordRows b
on a.rn =（b.rn - 1）
）
select int（rn）as rn，int（opening）as opening
，int（shift）as shift ，调整
，int（关闭）作为从addRows
关闭
以下是上述查询的报告：
RN打开移动调整关闭
1 5,000 500 100 5,600
2 5,600 560 200 6,360
3 6,360 636 300 7,296
现在要将上述脚本变量创建和查询修改为与Table T中的数据相对应的用户定义表函数（UDTF）：
创建函数shift_vals
（OpenedWith十进制（5）
，因子十进制（3，2）
）
返回表
（打开int
，Shift int
，调整int
，关闭int
）
返回
与
ordRows（rn ，调整）为
（select row_number（）over（），Adjustment from t）
，addRows（rn，Opening，Shift，Adjustment，closing）as
（select rn，OpenedWith，OpenedWith *因子，调整
，（OpenedWith +（OpenedWith * Factor）+调整）
from ordRows
其中rn = 1
union all
select b .rn，a.Closing，a.Closing *因子，b.调整
，（a.Closing +（a.Closing * Factor）+ b.Adjustment）
from addRows a
join在a.rn =（b.rn - 1）
）
从addRows
中选择开启，移动，调整，关闭
order by rn
现在调用具有注释开头值和因子作为参数的UDTF;即不再依赖于创建的变量，而是通过输入参数获得的值：
从表中选择t。*
（shift_vals（5000，0.1））如t
; - 结果如下：
开放式调整关闭
5,000 500 100 5,600
5,600 560 200 6,360
6,360 636 300 7,296
I am having problem writing a recursive table function in DB2.
I have a table of values....
With t (Adjustment) as (
VALUES (100)
, (200)
, (300)
) select * from t;
I need to pass to a UDF Table function an opening value (say 5000) and a factor (say 0.1)
I need to show a result as follows....
Opening Shift Adjustment Closing
(3) (1) (2)
==================================================
5000 500 100 5600
5600 560 200 6360
6360 636 300 7296
- Shift = Opening * 0.1
- Closing = Opening + Shift + Closing
- Opening is the closing of the previous row
My recursive function gets stuck when I tried to bring the brought forward ...(SQL0345N The fullselect of the recursive common table expression)
Appreciate any idea how to do this. I am aware the Stored Procedure can do this but I need to use a UDF so that it can be extended in another UDF.
I am unsure if I interpreted the scenario properly, but here is what I did:
To start, I will create a couple variables to mimic input to a function, and then compose a Recursive Common Table Expression (RCTE) to generate the report using data obtained from those variables:
create variable OpenedWith decimal default 5000
;
create variable Factor decimal(2, 1) default 0.1
;
with
t (adjustment) as ( values(100), (200), (300) )
, ordRows (rn, Adjustment ) as
( select row_number() over(), Adjustment from t )
, addRows (rn, Opening, Shift, Adjustment, closing ) as
( select rn, OpenedWith, OpenedWith*Factor , Adjustment
, ( OpenedWith + ( OpenedWith*Factor ) + Adjustment )
from ordRows
where rn = 1
union all
select b.rn, a.Closing, a.Closing * Factor , b.Adjustment
, ( a.Closing + (a.Closing * Factor) + b.Adjustment )
from addRows a
join ordRows b
on a.rn = ( b.rn - 1 )
)
select int(rn) as rn, int(opening) as opening
, int(shift) as shift, adjustment
, int(closing) as closing
from addRows
The following is the report from the above query:
RN OPENING SHIFT ADJUSTMENT CLOSING
1 5,000 500 100 5,600
2 5,600 560 200 6,360
3 6,360 636 300 7,296
And now to modify the above scripted variable creations and query into a User Defined Table Function (UDTF) that operates against the data in TABLE named T:
create function shifted_vals
( OpenedWith decimal( 5 )
, Factor decimal( 3, 2)
)
returns table
( Opening int
, Shift int
, Adjustment int
, Closing int
)
return
with
ordRows (rn, Adjustment ) as
( select row_number() over(), Adjustment from t )
, addRows (rn, Opening, Shift, Adjustment, closing ) as
( select rn, OpenedWith, OpenedWith*Factor , Adjustment
, ( OpenedWith + ( OpenedWith*Factor ) + Adjustment )
from ordRows
where rn = 1
union all
select b.rn, a.Closing, a.Closing * Factor , b.Adjustment
, ( a.Closing + (a.Closing * Factor) + b.Adjustment )
from addRows a
join ordRows b
on a.rn = ( b.rn - 1 )
)
select opening, shift, adjustment, closing
from addRows
order by rn
Now invoke the UDTF with the noted opening value and factor as arguments; i.e. no longer depending on created variables, instead values obtained via the input parameters:
select t.*
from table(shifted_vals(5000, 0.1)) as t
; -- results as report, follows:
OPENING SHIFT ADJUSTMENT CLOSING
5,000 500 100 5,600
5,600 560 200 6,360
6,360 636 300 7,296
这篇关于DB2递归UDF表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
