11 Replies Latest reply: Oct 20, 2013 8:59 AM by xerces8 RSS

    Can AnyOne Explain Parameterized View with Example

    727892
      Explain the parameterized view with example
        • 1. Re: Can AnyOne Explain Parameterized View with Example
          728534
          Hi,
          Check
          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1448404423206

          Regards,
          Bhushan
          • 2. Re: Can AnyOne Explain Parameterized View with Example
            BluShadow
            user12059276 wrote:
            Explain the parameterized view with example
            How about you explain your problem?

            We're not here to teach things that are easily looked up in documentation.
            • 3. Re: Can AnyOne Explain Parameterized View with Example
              727892
              Passing two Dates to View and Select data between two dates
              • 4. Re: Can AnyOne Explain Parameterized View with Example
                6363
                user12059276 wrote:

                Can AnyOne Explain Parameterized View with Example
                http://homepage.ntlworld.com./jonathan.deboynepollard/FGA/questions-with-yes-or-no-answers.html

                Yes.

                http://forums.oracle.com/forums/search.jspa?threadID=&q=%22parameterized+view%22&objID=f75&dateRange=all&numResults=15&rankBy=10001
                • 5. Re: Can AnyOne Explain Parameterized View with Example
                  BluShadow
                  user12059276 wrote:
                  Passing two Dates to View and Select data between two dates
                  How would you go about selecting data between two dates in a normal query?
                  (Hint: A view is no different to a normal query)
                  • 6. Re: Can AnyOne Explain Parameterized View with Example
                    728140
                    Hi ,

                    I have created a view...
                    CREATE OR REPLACE VIEW V_UNITS_UNITMOVEMENT_AMTEST(fromdate varchar,todate varchar)
                    AS
                    Select Decode('',0,A.Code,A.Code) Code,A.UDate,
                    A.NAV,
                    B.Scheme_Description SchemeName,
                    A.DividendOption,
                    C.DividendType_Name DividendName,
                    0 As OpeningBalance,
                    Sum(Decode('',0,A.PurchaseAmt,A.PurchaseAmt)) PurchaseAmt,
                    Sum(Decode('',0,A.SwitchInAmt,A.SwitchInAmt)) SwitchInAmt,
                    Sum(Decode('',0,A.DividendAmt,A.DividendAmt)) DividendAmt,
                    Sum(Decode('',0,A.RedemptionAmt,A.RedemptionAmt)) RedemptionAmt,
                    Sum(Decode('',0,A.SwitchOutAmt,A.SwitchOutAmt)) SwitchOutAmt,
                    A.Units
                    From
                    (
                    Select PurSU_Code As Code,PurSU_Date As UDate,case when PURSU_DATE <= dt then
                         DECODE(MARKETSU_ADJUSTEDNAV,0,PURSU_NAV,MARKETSU_ADJUSTEDNAV)
                    else
                         PURSU_NAV
                    end As NAV,
                    PurSu_TotalSU As Units,
                    (case when PURSU_DATE <= dt then
                         DECODE(MARKETSU_ADJUSTEDNAV,0,PURSU_NAV,MARKETSU_ADJUSTEDNAV)
                    else
                         PURSU_NAV
                    end )* Sum(PurSu_TotalSU) As PurchaseAmt,0 SwitchInAmt,
                    0 DividendAmt,0 RedemptionAmt,
                    0 SwitchOutAmt,DividendType_ID As DividendOption From Trn_Invest_PurShareUnits a
                    left outer join
                    (
                         select MARKETSU_SU_CODE, MARKETSU_ADJUSTEDNAV , dt from
                         (
                              select MARKETSU_SU_CODE, MARKETSU_ADJUSTEDNAV,MARKETSU_ADJUSTEDDATE dt, row_number()
                              over (partition by MARKETSU_SU_CODE order by MARKETSU_ADJUSTEDDATE desc) rn
                              from trn_invest_marketsu
                              where MARKETSU_ADJUSTEDDATE between to_date(fromdate,'DD/MM/YYYY') and to_date(todate,'DD/MM/YYYY')
                              and MARKETSU_ADJUSTEDNAV> 0
                         )
                         where
                         rn=1
                    ) b
                    on a.PURSU_CODE = b.MARKETSU_SU_CODE(+)
                    Where SubStr(PurSU_ID,0,2)='UN' And SubStr(PurSU_ID,0,4) != 'UNSW'
                    And PurSU_RequestStatus='Y' and PURSU_FROMBANKCODE <> ' '
                    and PURSU_DATE between to_date(fromdate,'DD/MM/YYYY') and to_date(todate,'DD/MM/YYYY')
                    Group By PurSU_Code,PurSU_Date,PurSU_Date,MARKETSU_ADJUSTEDNAV,PURSU_NAV,PurSu_TotalSU,DividendType_ID,dt
                    Union
                    Select a.PURSU_CODE As Code,a.PURSU_DATE As UDate,
                    case when PURSU_DATE <= dt then
                         DECODE(MARKETSU_ADJUSTEDNAV,0,PURSU_NAV,MARKETSU_ADJUSTEDNAV)
                    else
                         PURSU_NAV
                    end As NAV,
                    a.PURSU_TOTALSU As Units,
                    0 As PurchaseAmt, (case when PURSU_DATE <= dt then
                         DECODE(MARKETSU_ADJUSTEDNAV,0,PURSU_NAV,MARKETSU_ADJUSTEDNAV)
                    else
                         PURSU_NAV
                    end ) *a.PURSU_TOTALSU As SwitchInAmt,
                    0 DividendAmt,0 RedemptionAmt,0 SwitchOutAmt,
                    a.DIVIDENDTYPE_ID As DividendOption From trn_invest_purshareunits a left outer join
                    (
                    select MARKETSU_SU_CODE, MARKETSU_ADJUSTEDNAV , dt from
                         (
                              select MARKETSU_SU_CODE, MARKETSU_ADJUSTEDNAV,MARKETSU_ADJUSTEDDATE dt, row_number()
                              over (partition by MARKETSU_SU_CODE order by MARKETSU_ADJUSTEDDATE desc) rn
                              from trn_invest_marketsu
                              where MARKETSU_ADJUSTEDDATE between to_date(fromdate,'DD/MM/YYYY') and to_date(todate,'DD/MM/YYYY')
                              and MARKETSU_ADJUSTEDNAV> 0
                         )
                         where
                         rn=1
                    ) b
                    on a.PURSU_CODE = b.MARKETSU_SU_CODE(+)
                    Where SubStr(purSU_ID,0,4) = 'UNSW'
                    And purSU_RequestStatus='Y'
                    and PURSU_DATE between to_date(fromdate,'DD/MM/YYYY') and to_date(todate,'DD/MM/YYYY')
                    Group By MARKETSU_ADJUSTEDNAV,PURSU_CODE,PURSU_DATE ,dt,PURSU_NAV,PURSU_TOTALSU,DIVIDENDTYPE_ID
                    Union
                    Select a.PURSU_CODE As Code,a.PURSU_DATE As UDate,
                    case when PURSU_DATE <= dt then
                         DECODE(MARKETSU_ADJUSTEDNAV,0,PURSU_NAV,MARKETSU_ADJUSTEDNAV)
                    else
                         PURSU_NAV
                    end As NAV,
                    a.PURSU_TOTALSU As Units,
                    0 As PurchaseAmt, (case when PURSU_DATE <= dt then
                         DECODE(MARKETSU_ADJUSTEDNAV,0,PURSU_NAV,MARKETSU_ADJUSTEDNAV)
                    else
                         PURSU_NAV
                    end ) *a.PURSU_TOTALSU As SwitchInAmt,
                    0 DividendAmt,0 RedemptionAmt,0 SwitchOutAmt,
                    a.DIVIDENDTYPE_ID As DividendOption From trn_invest_purshareunits a left outer join
                    (
                    select MARKETSU_SU_CODE, MARKETSU_ADJUSTEDNAV , dt from
                         (
                              select MARKETSU_SU_CODE, MARKETSU_ADJUSTEDNAV,MARKETSU_ADJUSTEDDATE dt, row_number()
                              over (partition by MARKETSU_SU_CODE order by MARKETSU_ADJUSTEDDATE desc) rn
                              from trn_invest_marketsu
                              where MARKETSU_ADJUSTEDDATE between to_date(fromdate,'DD/MM/YYYY') and to_date(todate,'DD/MM/YYYY')
                              and MARKETSU_ADJUSTEDNAV> 0
                         )
                         where
                         rn=1
                    ) b
                    on a.PURSU_CODE = b.MARKETSU_SU_CODE(+)
                    Where SubStr(PurSU_ID,0,2)='UN' And SubStr(PurSU_ID,0,4) != 'UNSW'
                    And PurSU_RequestStatus='Y' and PURSU_FROMBANKCODE = ' '
                    and PURSU_DATE between to_date(fromdate,'DD/MM/YYYY') and to_date(todate,'DD/MM/YYYY')
                    Group By MARKETSU_ADJUSTEDNAV,PURSU_CODE,PURSU_DATE ,dt,PURSU_NAV,PURSU_TOTALSU,DIVIDENDTYPE_ID
                    Union
                    Select SalSU_Code As Code,SalSU_Date As UDate,SalSU_SaleNAV As NAV,SalSU_NoOfUSold As Units,
                    0 PurchaseAmt,0 SwitchInAmt,0 DividendAmt,Sum(SalSU_TotalRealization) As RedemptionAmt,
                    0 SwitchOutAmt,DividendType_ID As DividendOption From Trn_Invest_SalShareUnits
                    Where SubStr(SalSU_ID,0,5)='UNRED' And SalSU_RequestStatus='Y'
                    and SalSU_Date between to_date(fromdate,'DD/MM/YYYY') and to_date(todate,'DD/MM/YYYY')
                    And SalSU_MJVStatus in('C','D','O') Group By SalSU_Code,SalSU_Date,SalSU_SaleNAV,SalSU_NoOfUSold,DividendType_ID
                    Union
                    Select SalSU_Code As Code,SalSU_Date As UDate,SalSU_SaleNAV As NAV,SalSU_NoOfUSold As Units,0 PurchaseAmt,
                    0 SwitchInAmt,0 DividendAmt,0 RedemptionAmt,Sum(SalSU_TotalRealization) As SwitchOutAmt,
                    DividendType_ID As DividendOption
                    From Trn_Invest_SalShareUnits Where SubStr(SalSU_ID,0,4)='UNSW' And SalSU_RequestStatus='Y'
                    and SalSU_Date between to_date(fromdate,'DD/MM/YYYY') and to_date(todate,'DD/MM/YYYY')
                    And SalSU_MJVStatus in('C','D','O') Group By SalSU_Code,SalSU_Date,SalSU_SaleNAV,SalSU_NoOfUSold,DividendType_ID
                    ) A,Mst_Invest_Scheme B,Mst_Invest_SchemeDividendType C
                    Where A.Code=B.Scheme_Code
                    And A.DividendOption = C.DividendType_ID
                    Group By A.Code,A.UDate,B.Scheme_Description,A.DividendOption,C.DividendType_Name,A.NAV,A.Units
                    /

                    when i execute i get this error ...
                    ORA-00907: missing right parenthesis
                    what should i do?
                    • 7. Re: Can AnyOne Explain Parameterized View with Example
                      Frank Kulash
                      Cathrin wrote:
                      Hi ,

                      I have created a view...
                      CREATE OR REPLACE VIEW V_UNITS_UNITMOVEMENT_AMTEST(fromdate varchar,todate varchar)
                      ... 127 more lines (no kidding)
                      Group By A.Code,A.UDate,B.Scheme_Description,A.DividendOption,C.DividendType_Name,A.NAV,A.Units
                      /

                      when i execute i get this error ...
                      ORA-00907: missing right parenthesis
                      what should i do?
                      First, indent your code to show the extent of structures that extend over several lines, such as subqueries, CASE expressions, or function calls that have nexted arguments.
                      Make parentheses line up so that, if a ) has to be lines away from its matching (, then are indented the same amount, with nothing except white space between them.
                      When posting code (or any formatted text) on this site, type these 6 characters:

                      \
                      (small letters only, inside curly brackets) before and after sections of formatted text, to preserve spacing.
                      
                      Note that "ORA-00907: missing right parenthesis" doesn't always mean you have unbalanced parentheses.  Often, it just means that something (such as an Oracle keyword) appears at a point where it doesn't make sense, but where a ) would make sense.
                      
                      Second, develop code in very small increments.  Take baby steps. Start with a very small, simple query, test it, then add one or two more lines.  When you get a mysterious error like "ORA-00907: missing right parenthesis", chances are there was a mistake in the one or two lines you just added.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                      • 8. Re: Can AnyOne Explain Parameterized View with Example
                        728140
                        Hi
                        create or replace view test(fromdate varchar(20),todate varchar(20))
                        as
                        select * from trn_invest_purshareunits
                        where pursu_date between to_date(fromdate,'dd/MM/yyyy') and to_date(todate,'dd/MM/yyyy')..
                        even with this view i get the error missing right parantheses
                        • 9. Re: Can AnyOne Explain Parameterized View with Example
                          Frank Kulash
                          Hi,
                          Cathrin wrote:
                          Hi
                          create or replace view test(fromdate varchar(20),todate varchar(20))
                          as
                          select * from trn_invest_purshareunits
                          where pursu_date between to_date(fromdate,'dd/MM/yyyy') and to_date(todate,'dd/MM/yyyy')..
                          even with this view i get the error missing right parantheses
                          Even that is too much to start with, and even that is unformatted.
                          Here's an example of formatted code:
                          create or replace view     test ( fromdate  varchar (20)
                                                         , todate    varchar (20)
                                              )
                          as
                          select     * 
                          from      trn_invest_purshareunits
                          where      pursu_date     between      to_date (fromdate, 'dd/MM/yyyy') 
                                         and       to_date (todate,   'dd/MM/yyyy')
                          ;
                          Whenever you have an error, it helps to post the entire error message.
                          In your case, it's probably:
                          create or replace view     test ( fromdate  varchar (20)
                                                                   *
                          ERROR at line 1:
                          ORA-00907: missing right parenthesis
                          Notice where the * is in the error message: it's right below "varchar". That's a hint. In this example, you have balananced parentheses, but you have an Oracle keyword in a place where it isn't allowed, but where ) would be allowed.

                          In a CREATE VIEW statement, you can give aliases to all the columns immediately after the view name, but you can't specify a data type (such as VARCHAR) at that point. Use CAST in the query if you need to specify a data type or length.
                          • 10. Re: Can AnyOne Explain Parameterized View with Example
                            xerces8

                            (google listed this as one of first ten hits for searching on "how to make a parameterized view in oracle", so I'll add some notes)

                             

                            Oracle (as of version 11g) does not support parameters for views, as is tried in the post above this one.

                             

                            There are several ways to "emulate" parametrized views in Oracle:

                            - using SYS_CONTEXT

                            - using Global Package Variables

                            - maybe some more ways

                             

                            If anyone finds a nice article explaining each, please post...

                             

                            A start would be Ask Tom "Parameterized View & Truncation of Table"

                            or Dive in Oracle: Create View with Parameter

                            • 11. Re: Can AnyOne Explain Parameterized View with Example
                              SomeoneElse

                              3.5 year old thread.

                               

                              Probably should be locked.