how to create view with subquery
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: how to create view with subquery

  1. #1
    Join Date
    Aug 2000
    Posts
    132
    Hi folks,

    I'm trying to create a view using a subquery but it's not working.

    CREATE or REPLACE view v_users(user_nm)
    AS
    select user_nm from user_listing where user_nm not in (select user_nm from user_listing where user_nm like '%test.com') a\
    nd user_nm NOT IN (select user_nm from user_listing where user_nm like '%testing.com')
    and exchg_id = 6
    and reg_dt > '12-JUN-01'
    and user_nm not like '%jones%'
    and user_nm not like '%smith%';

    when I tried to compile the view I received the following error message at the first subquery

    *
    ERROR at line 3:
    ORA-00942: table or view does not exist

    Any suggestions would be most appreciated.


  2. #2
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    Make sure that user_listing is an actual table in the schema and not a synonym to a table or view in another schema.

  3. #3
    Join Date
    May 2001
    Posts
    4
    Make sure that the account that is creating this view has been granted permissions directly on the objects it is referencing to build the view. If any of the permissions are granted through roles this may cause a problem.

Posting Permissions

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



Click Here to Expand Forum to Full Width