how to create view with subquery?

[sukimac]

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.

[sambavan]

Originally posted by sukimac
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.




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

Hope this would fix. When you use multiple subqueries, use the aliasing .

Sam

[Edited by sambavan on 06-18-2001 at 06:07 PM]