我有一个table-valued
函数f_customer_return_rate
(返回一个表,而不是一个数字)
Customer_id end_date period RATE
1234 '2020-12-31' 12 0.3
我想多次运行以获得很多客户的回报率。如何循环或将其嵌入查询中?
例如我想要
select customer_id,
select RATE
from table(f_customer_return_rate (
customer_id, -- customer id
To_date('2020-12-31', 'YYYY-MM-DD'), --end_date IN DATE,
12 -- period: since how many months ago
)
)
where rownum = 1
as return_rate
FROM customer_table -- this table has 1000 customers
它抛出错误
[代码:936,SQL状态:42000] ORA-00936:缺少表达式
是因为此函数是表值的并且不能放入列中?
我有什么办法可以循环(理想情况下嵌入查询中)并将1000个客户的结果放在一起?
不,您正在调用的函数不会发生这种情况。您缺少几个括号。
select customer_id,
--> here
(select RATE
from table(f_customer_return_rate (
customer_id, -- customer id
To_date('2020-12-31', 'YYYY-MM-DD'), --end_date IN DATE,
12 -- period: since how many months ago
)
)
where rownum = 1) --> here
as return_rate
FROM customer_table -- this table has 1000 customers
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句