This discussion is archived
2 Replies Latest reply: Nov 15, 2012 11:15 PM by Saubhik RSS

How to pass Parameter in a view

943051 Newbie
Currently Being Moderated
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.

Ajay

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points