Parameterized View in Oracle

No.

Now a "no" answer is likely not what you were hoping for, but Oracle views do not accept parameters so the simple answer is no.

I understand that you want to solve the business problem though, so you are likely looking for a way to do that in Oracle.  A big complication here is the fact that you want matching data returned from two different tables for possibly short periods of time, when maybe one table or the other has no matching records.  You have to be careful with the solution design here to make sure you don't hide the data that is there (like a simple join view would do) if/when this happens.

Since Oracle PL\SQL functions can accept input parameters, one option would be to create a function for each table that accepts the date parameters and returns the total (or zero) for each table.  These functions could then be called from a single select statement, but not from a view (because of the limitation of not being able to pass parameters to a view).

Depending on your version of Oracle, another option would be to nest select statements something like this:
select m.id, (select sum(payment_amount) from payments p where p.from_date >= [from_date] and p.through_date <= [through_date] and p.id = m.id) "total payments",
(select count(*) from emails e where e.from_date >= [from_date] and e.through_date <= [through_date]
and e.id = m.id) "Total Emails"
from [main_table] m

How are the three tables joined?

Take a look at pipelined functions.  The function could assemble the data from the three tables.  Once you have setup the function, you could query with:

select *
from table(f_your_func(start,end));

Currently I have used pipelined function to implement.
Problem with pipelined function is
1) users cant describe it to see the columns,
2) They cannot use Group by

Please let me know if there is any way to achieve this with pipelined functions.

davidshockey- The tables are connected with ID Field.

This is untested but I think its on the right track...

create view kajunki (id, date, payment, email) as
select id, date, payment, 0 from tbl2
union all
select id, date, 0, 1 from tbl3

then do this

select id, sum(payment), count(email)
from kajunki
where date between startdate and enddate
group by id

I'm a little uncertain of what you mean by "accept parameters from user".  Can you describe some of the contraints that you have?


Here are some general options that come to mind:
-- user can run a sqlplus script that prompts for start and end dates... then runs an anonymous block to get results
-- user inserts start and end date into a global temp table... then join GT with your select.
-- use sys_context to set the start and end dates.. then your select that reference sys_context for the appropriate dates.


I can give specific example is needed, but it would be nice to know which type of option is most viable in your environment.

Though I have already provided a solution for dynamic view above, but I would like to share a solution using the PIPELINED table function also.

I was looking at your concerns related to PIPELINED table function :

-->1) users cant describe it to see the columns,
-->2) They cannot use Group by
-->Please let me know if there is any way to achieve this with pipelined functions.

1. You can describe the function, which will tell that there are two inputs required for this function. If you wish to see the return structure, you can describe the structure of table that is returned by the function. An example could be :

SQL> desc custview
FUNCTION custview RETURNS CUST_VIEW_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_DT                       DATE                    IN
 END_DT                         DATE                    IN

SQL> desc CUST_VIEW_TABLE
 cust_view1_table TABLE OF CUST_VIEW
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 TOT_PAY                                            NUMBER
 TOT_EMAILS                                         NUMBER

2. You can have the GROUP BY clause within the function code. I am not sure why you are facing the problem with it.

3. If you are interested in looking at the option of PIPELINED table function, then have a look at my solution below :

create or replace type cust_view
  as object (ID NUMBER, TOT_PAY NUMBER, TOT_EMAILS number); --structure of object, that you are interested in

create type cust_view_table
  as table of cust_view;

create or replace function custview (start_dt date, end_dt date)
  return cust_view_table pipelined
as
sdate  date;
edate  date;

cursor c1 is
select a.cust_id id, sum(b.pay_amt) tot_pay, sum(c.emails) tot_emails
from tab_cust a, tab_paym b, tab_emails c
where a.cust_id = b.id and a.cust_id = c.id
and b.pay_date between sdate and edate
and c.email_date between sdate and edate
group by a.cust_id;

begin
    sdate := start_dt;
    edate := end_dt;
    for c1_row in c1
    loop
        PIPE ROW(cust_view(c1_row.id, c1_row.tot_pay, c1_row.tot_emails)) ;
    end loop;
return;
end custview;
/

-------
You may note, that you can have the group by clause within the cursor definition that will be used to find out the relevant rows from the three tables.

Now, that we have created the function, we can use the same :

SQL> select * from table (custview1(to_date('21-APR-04'), to_date('02-MAY-04')));

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         1        350         20
         2        350          5
         3        350          5
         4        350         24
         5        350         12
         6        350          1
         7        350          2
         8        350          1
         9        350          3
        10        200          1

SQL> select * from table (custview1(to_date('28-APR-04'), to_date('02-MAY-04')));

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         4        350         24
         7        350          2
         8        350          1
         9        350          3
        10        200          1

Hope this helps. Do let me know, if you need any more info on this.

well, the name of function is custview and not custview1. I made a typo :) when u select the records, u would do :

SQL> select * from table (custview(to_date('21-APR-04'), to_date('02-MAY-04')));

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         1        350         20
         2        350          5
         3        350          5
         4        350         24
         5        350         12
         6        350          1
         7        350          2
         8        350          1
         9        350          3
        10        200          1

SQL> select * from table (custview(to_date('28-APR-04'), to_date('02-MAY-04')));

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         4        350         24
         7        350          2
         8        350          1
         9        350          3
        10        200          1

Hope this helps. Do let me know, if you need any more info on this.

@@TheLearnedOne : I do not agree to the recommendations.

davidshockey {http:#10964226} solution will not work, mainly because you cannot have the input parameters defined in a view as suggested. In that way, only the column names (for the view) can be defined. Have a look at the following example :

SQL> create or replace view vsysdate (mydummy) as
  2  select sysdate from dual where dummy = mydummy;
select sysdate from dual where dummy = mydummy
                                       *
ERROR at line 2:
ORA-00904: "MYDUMMY": invalid identifier

SQL> create or replace view vsysdate (mydummy) as
  2  select sysdate from dual where dummy = 'X';

View created.

SQL> select * from vsysdate;

MYDUMMY
---------
19-OCT-04


riazpk {http:#10964925} solution is a stored procedure, and you will not be able to select the values from a stored procedure and put it into a variable or record, as you can do with a normal view.

However, the PIPELINED table function will allow you to use it as a view, and you could do a select INTO using the table function as I have suggested.

I am sorry, but I would not like to share the points with riazpk and davidshockey, as they have not provided the correct solution.

I disagree with pratikroy's assessment.

Because kajunki called this a parameterized view in the title, pratrikroy is hung on the idea that parameters must be passed to the view.  This is not necessary to the solution.  His characterization of my solution with the mydummy query is spurious, having little relation to my proposal.

Furthermore, kajunki stated that it was a requirement that the users be able to use group by in their query.  Pratikroy waves this requirement off by saying that the group by can be part of the cursor select statement in the function - NOT the same thing.

My solution works (I tested it), and is far simpler than the pipelined table function solution.

I am perfectly OK with sharing the points with those who contributed.

I found this while doing a search. Perhaps it can help you
------------
Create context params using setmm;
------------
create package setmm is
procedure minmax(vmin in number, vmax in number);
end setmm;
/
------------
create package body setmm is
procedure minmax(vmin in number, vmax in number) is
begin
dbms_session.set_context(
namespace => 'params', attribute => 'minval', value => vmin);
dbms_session.set_context(
namespace => 'params', attribute => 'maxval', value => vmax);
end minmax;
end setmm;
/
------------
create view parametrized as
   select * from (select level n from dual connect by level <= 1000)
   where n between to_number(sys_context('params', 'minval'))
and to_number(sys_context('params', 'maxval'));
------------
SQL> exec setmm.minmax(vmin => 5, vmax => 14)

SQL> select * from parametrized;
5
6
7
8
9
10
11
12
13
14

10

SQL> exec setmm.minmax(vmin => 2, vmax => 4)


SQL> select * from parametrized;
2
3
4

Thanks and Regards,
Lionel