Why adding "AS" in SQL statement "FROM" cause error?

user_9008797 0
Viewed 84

Follow SQL statement works well, but while adding AS, the database gives an error. However, many cases given contain AS in FROM. So what's wrong with the error statement?

Thanks

-- works code

select distinct id 
from PROJECT b
where 0>= (
    select max(step) 
    from PROJECT
    where (status='C' and id =b.id)
    );


-- error code

select distinct id 
from PROJECT as b
where 0>= (
    select max(step) 
    from PROJECT
    where (status='C' and id =b.id)
    );

error message: ORA-00933: SQL command not properly ended

-- testing data

create table project (
    id varchar(22) not null,
    step int not null,
    status char(1) not null,
    primary key (id, step)
);

insert into project values ('P100', 0,'C');
insert into project values ('P100', 1,'W');
insert into project values ('P100', 2,'W');
insert into project values ('P201', 0,'C');
insert into project values ('P201', 1,'C');
insert into project values ('P333', 0,'W');
insert into project values ('P333', 1,'W');
insert into project values ('P333', 2,'W');
insert into project values ('P333', 3,'W');

Note: my code run on https://livesql.oracle.com/ (i.e. using Oracle Database 19c)

oracle sql
1 Answers

as is not allowed for table aliases in Oracle (although it is allowed in every (or almost every) other database.

That said, here are two alternative ways to write your query that may be more efficient:

select distinct p.id 
from PROJECT p
where exists (select 1
              from PROJECT p2
              where p2.id = p.id and p2.status = 'C' and p2.step > 0
             );

Or:

select distinct p.id
from project p
where p.status = 'C' and p.step > 0
user_11440350
Related