Ankit Bhatt, August 10, 2017 - 10:59 am UTC
Thanks a lot Chris. very helpful snippets but our issue is that we are passing rev_id at run time, so for that can we use sys_context? please suggest.
August 10, 2017 - 1:49 pm UTC
You have to set the context before querying the view. If you can't do that, this won't work for you.
followup on last comments
Ankit Bhatt, August 10, 2017 - 4:29 pm UTC
Thanks again. in the same view, we are joining 2 tables and creating a view and for those table definitions are as under,
create table test_item_class(calc_abc varchar2(10),rev_id number,cost number,resale number,margin number)
create table test_item_details(item_class varchar2(10), item_desc varchar2(50))
insert into test_item_class values ('A',1,1000,500,300)
insert into test_item_class values ('B',1,2000,400,200)
insert into test_item_class values (null,1,3000,300,100)
insert into test_item_class values ('C',2,1200,500,300)
insert into test_item_class values ('D',2,1500,500,200)
insert into test_item_details values ('A','Desc_A')
insert into test_item_details values ('B','Desc_B')
insert into test_item_details values ('C','Desc_C')
insert into test_item_details values ('D','Desc_D')
insert into test_item_details values ('A1A','Desc_A1A')
insert into test_item_details values ('A1B','Desc_A1B')
CREATE VIEW test_item_v AS
COUNT(DISTINCT tic.cost) cost,
COUNT(DISTINCT tic.resale) resale,
COUNT(DISTINCT tic.margin) margin
RIGHT OUTER JOIN test_item_details tid ON tid.item_class = tic.calc_abc
Now we want output as,
item_class | rev_id | Cost | resale | Margin
A | 1 | 1 | 1 | 1
B | 1 | 1 | 1 | 1
C | 1| NULL|NULL|NULL
D | 1 | NULL|NULL|NULL
A1A | 1 | NULL|NULL|NULL
A1B | 1 | NULL|NULL|NULL
Is it possible to get with the given details?
August 11, 2017 - 2:49 pm UTC
Hmmm. I don't think so. Querying rev_id in the view means you inner joining test_item_class instead of outer joining it. So you end up removing all rows except A & B.
I think you need to do this in the subquery which is inside the view.
Add column to filter to view or use table function
Evan, August 10, 2017 - 6:40 pm UTC
When I've run into this problem, what I've usually done is added the column I want to have as a parameter to the view and then when I select from the view I have a condition in the WHERE clause on the column.
So in this example, add rev_id to the view e.g.
create view test_item_v
--I think you'll need to add a group by clause since there are aggregate functions
and then when using the view
where rev_id = v_rev_id;
Another option is to create a PL/SQL table function with a parameter for the column you want to parameterize. Then execute the SQL statement using the parameter passed in. Finally, return the result set.
August 11, 2017 - 2:39 pm UTC
Yep, those are worth investigating.
RE: Add column to filter...
Duke Ganote, August 11, 2017 - 4:52 pm UTC
One potential issue with adding a column to the view, then filtering on the results, is an "unpushed" predicate. For small data sets and/or a simple view-query (where the optimizer can transform the query in order to push the predicate), it's not important.
However for large data sets, it's the difference between the first SELECT (which mimics a view with a column/parameter) and the second:
SQL> exec DBMS_application_info.set_client_info('SYS'); -- here's the parameter I want. Owner = 'SYS'
PL/SQL procedure successfully completed.
SQL> select cnt from ( select owner, count(*) cnt from dba_source group by owner ) where owner = userenv('client_info');
SQL> select count(*) cnt from dba_source where owner = userenv('client_info');
Same results, but for a complex query or very large data set, the difference in execution time could be significant.
August 14, 2017 - 2:27 pm UTC
Yep, if the optimizer can't push the predicate for some reason the SQL could be too slow.
SQL table macros
Stew Ashton, January 19, 2021 - 10:21 am UTC
Now that SQL table macros are available in the current long-term support release (19c), here is the macro equivalent of a parameterized view.
First, a tiny test table:
drop table test_item_class purge;
create table test_item_class(item_class, rev_id, total_cost, total_resale, margin) as
select 'A', 1, 100, 90, 10 from dual;
Next, a view definition that might actually work
if parameters were allowed:
create view test_item_v as
where rev_id = v_rev_id
group by item_class, rev_id;
Now, instead of preceding the SELECT statement by "create view", we have to create a function. The code that wraps the SELECT part is a few lines more, but that's it.
create or replace function test_item_v(v_rev_id in number) return varchar2 sql_macro is
where rev_id = v_rev_id
group by item_class, rev_id
select * from test_item_v(1);
I REV_ID SUM_COST SUM_RESALE SUM_MARGIN
- ---------- ---------- ---------- ----------
A 1 100 90 10
Best regards, Stew
January 19, 2021 - 1:09 pm UTC
Great point Stew