2 Replies Latest reply: Nov 16, 2012 1:15 AM by Saubhik RSS

    How to pass Parameter in a view

    943051
      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