Home » SQL & PL/SQL » SQL & PL/SQL » How to pass parameter in a View? (Oracle 10g, Windows xp)
How to pass parameter in a View? [message #570805] Fri, 16 November 2012 00:17 Go to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
How to pass parameter in a view.
create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60'  then 
      case when a.ccn <> '091' then 
           nvl(sum(a.quantity),0) else 0 
      End else 0
 End) from_plant,
(case when a.card_code = '60' then
      case when a.ccn = '091' then
           nvl(sum(a.quantity),0) else 0 
      End else 0
End) from_party,a.document_date
from kardex a,kardex_bags d
where exists (select b.item_code from kardex_bags b where b.item_code = a.item_code)
and a.document_date < '01/aug/2012'
and to_date(a.document_date) between add_months('01/aug/2012',-1) and last_day('01/aug/2012')
and a.item_code= d.item_code(+)
group by a.item_code,a.uom,a.card_code,a.ccn,a.document_date
union
select item_code Material_Code,''  uom,0 from_plant ,0 from_party ,null from kardex_bags where item_code not in (
select item_code from kardex a
where exists (select b.item_code from kardex_bags b where b.item_code = a.item_code)
and a.document_date < '01/aug/2012'
and to_date(a.document_date) between add_months('01/aug/2012',-1) and last_day('01/aug/2012'))


in this above view i have hardcoded dates, well how to pass parameter for this view without hardcoding?
here i need to pass the date as a parameter from a select query to view the data from the above view!

pls reply asap.

Stalin

[Updated on: Fri, 16 November 2012 00:20]

Report message to a moderator

Re: How to pass parameter in a View? [message #570811 is a reply to message #570805] Fri, 16 November 2012 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can register the value of the parameter in a context variable and refer this context variable in the view definition.
For instance, using the USERENV predefined context and CLIENT_INFO predefined variable.
SQL> create or replace view v as
  2  select * from emp
  3  where deptno = sys_context('userenv','client_info')
  4  /

View created.

SQL> exec dbms_application_info.set_client_info ('10');

PL/SQL procedure successfully completed.

SQL> select * from v;
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-1982       1300                    10

3 rows selected.

SQL> exec dbms_application_info.set_client_info ('20');

PL/SQL procedure successfully completed.

SQL> select * from v;
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-1987       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-1987       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20

5 rows selected.

Regards
Michel
Re: How to pass parameter in a View? [message #570833 is a reply to message #570811] Fri, 16 November 2012 05:26 Go to previous messageGo to next message
mamalik
Messages: 256
Registered: November 2008
Location: Pakistan
Senior Member

what is advantage of passing parameter in view. Will it fast view or not?
Re: How to pass parameter in a View? [message #570840 is a reply to message #570833] Fri, 16 November 2012 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First it is most often not a performances issue but a functional one. For instance, in my example, people can only see the rows from its department, department which is set in a logon trigger or by the application in the session context.
Then the condition on a context is always seen as a bind variable not as a constant value.

Regards
Michel
Re: How to pass parameter in a View? [message #570865 is a reply to message #570840] Fri, 16 November 2012 13:11 Go to previous message
Bill B
Messages: 1099
Registered: December 2004
Senior Member
the easiest way is use the following view and the the query

create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60'  then 
      case when a.ccn <> '091' then 
           nvl(sum(a.quantity),0) else 0 
      End else 0
 End) from_plant,
(case when a.card_code = '60' then
      case when a.ccn = '091' then
           nvl(sum(a.quantity),0) else 0 
      End else 0
End) from_party,a.document_date
from kardex a,kardex_bags d
where exists (select b.item_code from kardex_bags b where b.item_code = a.item_code)
and a.item_code= d.item_code(+)
group by a.item_code,a.uom,a.card_code,a.ccn,a.document_date
union
select item_code Material_Code,''  uom,0 from_plant ,0 from_party ,null from kardex_bags where item_code not in (
select item_code from kardex a
where exists (select b.item_code from kardex_bags b where b.item_code = a.item_code);



select * from spic_bags
where to_date(document_date) between add_months('01/aug/2012',-1) and last_day('01/aug/2012');
Previous Topic: dequeue_array not working with array_size > 1 [merged 2 by jd]
Next Topic: Query Help with table function
Goto Forum:
  


Current Time: Fri Sep 19 19:54:20 CDT 2014

Total time taken to generate the page: 0.05818 seconds