I'm trying to create a view using a subquery but it's not working.
CREATE or REPLACE view v_users(user_nm)
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.
Make sure that user_listing is an actual table in the schema and not a synonym to a table or view in another schema.
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.