Results 1 to 4 of 4

Thread: Oracle View

  1. #1
    Join Date
    Jan 2004
    Posts
    149

    Unanswered: Oracle View

    Hi All,

    I have a table with following data :

    msg_key user_key team_key message_text msg_date
    1 11727 21 hello how are you 03-03-2016 20:00:00
    2 11678 21 i am fine 03-03-2016 20:00:00
    3 11735 124 access the system 03-03-2016 20:00:00

    now, requirement is I would like to have a view, where while retrieving data from that I will the pass the user_key and view will return the all message_text for the team_key under which that user belongs to.

    like if I pass user_key as 11727 or 11678 , both the cases I should get two rows they belongs to same team_key ie. 21, but for user_key 11735 will return one row.


    Thanks in advance
    JD

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,504
    No need for a view, simply use the following query

    select msg_keym,user_key,team_key,message_text,msg_date
    from my_table
    where team_key in
    (select team_key
    from my_table
    where user_key = 11727);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Posts
    149
    Hi Beilstwh,

    Thanks for your reply.

    yes, you are right, that above query can resolve the requirement.
    But the problem is I do not want to give access the direct table, but want to have view to having table data and passing the user_key should give the same result as your query.

    the view will be accessed by the application where user will pass the user_key.

    Thanks,
    JD

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,504
    create or replace view my_view as
    select msg_keym,user_key,team_key,message_text,msg_date
    from my_table READ ONLY;

    Then do the select

    select msg_keym,user_key,team_key,message_text,msg_date
    from my_view
    where team_key in
    (select team_key
    from my_view
    where user_key = 11727);

    The READ ONLY means that you can't update through the view but have full access to the under laying table. I would add an index on user_key and another one on team_key.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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