Creating view in oracle using multiple queries

Question

I have 2 tables:

create table my_users
( userid number,
  username varchar(20)
 );

create table all_users
( userid number,
  username varchar(20)
 );

I want to create a view which is equivalent to:

select * from all_users;
MINUS
select * from my_users;

However, I'm getting an error when I try to create the view using the following:

create of replace view 'vw_users' as
select * from all_users;
MINUS
select * from my_users;

I'm using Oracle12c database.

**What** error? Apart from having an extra semicolon after the first branch? And having the view name in single quotes (instead of double quotes which will make it a quoted identifier, which will cause you pain later)? (And 'of' instead of 'or', as Gurv mentioned) — Alex Poole, Feb 16 '17 at 13:14
Error starting at line 3 in command: MINUS Error report: Unknown Command >>Query Run In:Query Result 1 — Alok, Feb 16 '17 at 13:18
yu have just a tipo as GurV tell you . you musr use `or` instead of `of` — ScaisEdge, Feb 16 '17 at 13:18
Don't edit the question to fix the problems you're being told about - it invalidates existing answers. Although it that is now actually the code you ran, and that was the only error you got, then the issue was only the semicolon after all... — Alex Poole, Feb 16 '17 at 13:20
I had used "or" in the db. the typo occurred while typing the question here. That's why I edited the question to reflect the actual query I ran. The error was of the semicolon and single quotes as everyone pointed out. Thanks for all your help. — Alok, Feb 16 '17 at 13:30

Gurwinder Singh · Answer 1 · 2017-02-16T13:18:41.750

3

Three typos:

Try this:

create or replace view "vw_users" as
select * from all_users
MINUS
select * from my_users;

edited Feb 16 '17 at 13:18

answered Feb 16 '17 at 13:17

Gurwinder Singh

1 Answers1