CODEWITHSUNDEEP
sqloracle-databaseselectinner-joinunion-all
Dilhan Nakandala
Dilhan Nakandala

Reputation: 363

SQL Select - Union combined with Join query results an Error

I am an amateur learning SQL and have a requirement to fetch records from three tables where two are identical in column structure and names (services, services_log) for which a UNION is used and a table (visit) which can JOIN first two tables using same column names.

select vi.desc, serv.* from services serv
inner join visit vi
on serv.service_id = vi.visit_service_code
where serv.id = '777777'
union all 
select vi.desc, servlog.* from services_log serv.servlog
inner join visit vi
on servlog.service_id = vi.visit_service_code
where servlog.id = '777777'

Above query fetches the desired records with no issues, but I wanted to try something cleaner and executed below.

select vi.desc, serv.* from (select * from services union all select * from services_log) as serv
inner join visit vi
on serv.service_id = vi.visit_service_code
where serv.id = '777777'

This however, displays an error.

ORA-00933: SQL command not properly ended

Isn't the syntax of the above 2nd query correct? Is there a cleaner query I can try to achieve the results rather than 1st query?

Thank you.

Upvotes: 0

Views: 165

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your syntax error is the as in the from clause. Oracle does not support as for table aliases.

So this is allowed:

from (select * from services union all
      select * from services_log
     ) serv

but not:

from (select * from services union all
      select * from services_log
     ) as serv

Even if this fixes the immediate syntax problem, you still need to guarantee that the columns in the two tables are the same and defined in the same order for this to work.

Upvotes: 1

Related Questions