英文:
How to pass apex item value to database view?
问题
我有一个Apex IR报表。有一些Apex项用于设置报表的筛选器。
查询非常庞大（超过60,000个字符），无法粘贴到区域源中。
如果通过“返回SQL查询的PL/SQL函数体”调用，它还会出现“ORA-06502：PL/SQL：数值或值错误”。
因此，我为此需求创建了一个数据库“视图”。
但我不知道如何将这些apex-item的值传递给视图。
最初，我考虑使用“apex_application_page_items”以及“apex_application_items”来满足需求。但再次，我不知道如何检查“session_id”或“app_user”。
请注意：由于复杂的查询和大量的数据，我不能执行“select * from view where view_column = apex_item;”，我需要将apex-item传递到View的SQL查询中。
任何帮助将不胜感激。
谢谢。
英文:
I've a apex IR. There are few apex items to set the filter on report.
The query is so huge (more than 60K characters long) that it cannot be pasted on region source.
It also gives ORA-06502: PL/SQL: numeric or value error if being called by PL/SQL Function Body returning SQL query.
Hence I've created a database View for this requirement.
But I don't how to pass these apex-item values to View.
Initially, I thought to use apex_application_page_items along with apex_application_items to fulfill the requirement. But again, I don't know how to check session_id or app_user.
Please note: Dute to a complex query and vast number of data, I can't do select * from view where view_column = apex_item; I need to pass apex-item into View's SQL query.
Any help would be appreciated.
Thanks.
答案1
得分: 0
你不需要将项目的值传递给视图，而是在交互式报表的查询中使用它们。
假设视图名称为 V_EMP，它选择了关于员工的各种值。页面项目是 P1_DEPT（部门）和 P1_JOB。查询将如下所示：
select v.dept_name, v.emp_name, v.job, v.sal
from v_emp
where (v.dept = :P1_DEPT or :P1_DEPT is null) --> 这是你会
and (v.job = :P1_JOB or :P1_JOB is null) --> 使用页面项目
order by v.dept_name, v.job, v.emp_name;
英文:
You don't pass items' values to a view, but use them in interactive report's query.
Presume view name is V_EMP and it selects various values about employees. Page items are P1_DEPT (department) and P1_JOB. Query would then look like this:
select v.dept_name, v.emp_name, v.job, v.sal
from v_emp
where (v.dept = :P1_DEPT or :P1_DEPT is null) --> this is where you'll
and (v.job = :P1_JOB or :P1_JOB is null) --> use page items
order by v.dept_name, v.job, v.emp_name;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论