Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Posts
    38

    Unanswered: Is it possible to have a view with a Parameter??

    Hi,

    Is it possible to pass a parameter and get the results from a view. Like a Cursor....??? I dont want to put the above one as a stored procedure....Is there anyway we can achieve the same???

    Thanks

    regards,
    avr

  2. #2
    Join Date
    Mar 2004
    Posts
    9

    Lightbulb

    You may consider creating a view at the run time by using dynamic pl/sql and the same is achievable by using a stored procedure...

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Is it possible to have a view with a Parameter??

    Here is one way:

    PHP Code:
    SQLcreate view myemps as
      
    2  select from emp
      3  where deptno 
    userenv('client_info');

    View created.

    SQLselect from myemps;

    no rows selected

    SQL
    exec dbms_application_info.set_client_info('10')

    PL/SQL procedure successfully completed.

    SQL> /

         
    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 

  4. #4
    Join Date
    Jul 2003
    Posts
    38

    Re: Is it possible to have a view with a Parameter??

    Thankyou very much Andrewst.

    I can use the same. But i think there is a constraint in using the same. if i want to put this view combine with other table, i cannt take everything in a single query. I should put it in a loop and then call

    EXEC dbms_application_info.set_client_info('0100111101' )

    everytime and then fetch the records...is it??

    Thankyou

    regards
    avr.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Is it possible to have a view with a Parameter??

    Can you give an example of what you mean?

    Perhaps you don't really need a "parameterised" view, rather a view that queries all the data that may be relevant. When you join the view to other tables, you thereby restrict the view to what is specific to the query.

  6. #6
    Join Date
    Jul 2003
    Posts
    38

    Re: Is it possible to have a view with a Parameter??

    Originally posted by andrewst
    Can you give an example of what you mean?

    Perhaps you don't really need a "parameterised" view, rather a view that queries all the data that may be relevant. When you join the view to other tables, you thereby restrict the view to what is specific to the query.
    Hi ...
    Thanks once again. Already i have discussed reg below in a separate thread. I am attaching the same FYR.

    My First table (source) table contains...

    Customer Number
    Date
    Count

    Which contains count for some of the customer Numbers for some of the dates. Basically my requirement is to get for all the customers and all dates (continuous) between two dates. But the list of customer number is in another table and i have created one table with one row which has the dates (continuous) .

    Now i am in the position to put outer join with two tables. One is with the date table to get the records continuously between two dates ans the other one is with the customer number maintenance table to get for all cusotmers. But outer join with morethan one table is not allowed in ORACLE. So i thought of acheiving the same by making one view.

    I think now you can get the exact issue of accessing the data...

    Do u have anymore idea??

    thnx & regards,
    avr

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Is it possible to have a view with a Parameter??

    You could perhaps use an inline view rather than a stored view?
    PHP Code:
    select ...
    from 
    select t1.at2.b, ...
      
    from t1t2
      where 
    ...
    v,
      
    t3
    where v
    .t3.(+)
      and 
    v.t3.(+); 
    Now it doesn't matter that v1.a is actually t1.a and v1.b is actually t2.b

  8. #8
    Join Date
    Jul 2003
    Posts
    38

    Re: Is it possible to have a view with a Parameter??

    Originally posted by andrewst
    You could perhaps use an inline view rather than a stored view?
    PHP Code:
    select ...
    from 
    select t1.at2.b, ...
      
    from t1t2
      where 
    ...
    v,
      
    t3
    where v
    .t3.(+)
      and 
    v.t3.(+); 
    Now it doesn't matter that v1.a is actually t1.a and v1.b is actually t2.b
    Hi Andrewst,
    Thanks for the reply. I couldnt meet my requirement by using the above way. pls see the data and my requirement.

    SQL>SELECT * FROM CUSTOMER_MASTER;

    CUSTOMER
    -----------------------------------
    CUSTOMER_NAME
    --------------------------------------------------------------------------------
    CUST1
    Allen

    CUST2
    Robert

    CUST3
    Abdul


    SQL>SELECT * FROM CUST_BALANCE ORDER BY CUSTOMER, DT;

    CUSTOMER DT BAL
    ----------------------------------- --------- ----------
    CUST1 12-MAR-04 1000
    CUST1 17-MAR-04 2000
    CUST1 17-APR-04 5000
    CUST2 01-APR-04 5000
    CUST2 17-MAY-04 5000
    CUST3 01-MAR-04 2000
    CUST3 19-MAR-04 200
    CUST3 01-APR-04 5000

    8 rows selected.

    SQL>
    SQL>SELECT * FROM DATES WHERE DT BETWEEN TO_DATE('12-MAR-2004','DD-MON-RRRR') AND TO_DATE('30-MAR-20
    04','DD-MON-RRRR');

    DT
    ---------
    12-MAR-04
    13-MAR-04
    14-MAR-04
    15-MAR-04
    16-MAR-04
    17-MAR-04
    18-MAR-04
    19-MAR-04
    20-MAR-04
    21-MAR-04
    22-MAR-04
    23-MAR-04
    24-MAR-04
    25-MAR-04
    26-MAR-04
    27-MAR-04
    28-MAR-04
    29-MAR-04
    30-MAR-04

    19 rows selected.

    Our requirement is to select data as below using single query

    CUSTOMER DT BAL
    ----------------------------------- --------- ----------
    CUST1 12-MAR-04 1000
    CUST1 13-MAR-04 1000
    CUST1 14-MAR-04 1000
    CUST1 15-MAR-04 1000
    CUST1 16-MAR-04 1000
    CUST1 17-MAR-04 2000
    CUST1 18-MAR-04 2000
    CUST1 19-MAR-04 2000
    ...

    Is it possible???

    Thanks & regards,
    avr.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Is it possible to have a view with a Parameter??

    Try:
    PHP Code:
    select cb.customerd.dtcb.bal
    from cust_balance cb
    dates d
    where cb
    .dt =
    select max(cb2.dtfrom cust_balance cb2
      where  cb2
    .dt <= d.dt
      
    and    cb2.customer cb.customer
    ); 
    Or using analytics:
    PHP Code:
    select customerdtbal
    from   dates
    ,
           ( 
    select customerdt start_dtballead(dtover (partition by customer  order by dtnext
             from cust_balance
           
    v
    where  dt between v
    .start_dt and nvl(v.next_dt-1,dt

  10. #10
    Join Date
    Jul 2003
    Posts
    38

    Re: Is it possible to have a view with a Parameter??

    Originally posted by andrewst
    Try:
    PHP Code:
    select cb.customerd.dtcb.bal
    from cust_balance cb
    dates d
    where cb
    .dt =
    select max(cb2.dtfrom cust_balance cb2
      where  cb2
    .dt <= d.dt
      
    and    cb2.customer cb.customer
    ); 
    Or using analytics:
    PHP Code:
    select customerdtbal
    from   dates
    ,
           ( 
    select customerdt start_dtballead(dtover (partition by customer  order by dtnext
             from cust_balance
           
    v
    where  dt between v
    .start_dt and nvl(v.next_dt-1,dt

    Excellent !!

    I think we are thinking too much for the solution...

    Thank you very much..

    regards,
    avr

  11. #11
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: Is it possible to have a view with a Parameter??

    Originally posted by andrewst
    Try:
    PHP Code:
    select cb.customerd.dtcb.bal
    from cust_balance cb
    dates d
    where cb
    .dt =
    select max(cb2.dtfrom cust_balance cb2
      where  cb2
    .dt <= d.dt
      
    and    cb2.customer cb.customer
    ); 
    Or using analytics:
    PHP Code:
    select customerdtbal
    from   dates
    ,
           ( 
    select customerdt start_dtballead(dtover (partition by customer  order by dtnext
             from cust_balance
           
    v
    where  dt between v
    .start_dt and nvl(v.next_dt-1,dt
    something new to me too... really interesting...
    Thanks and Regards,

    Praveen Pulikunnu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •