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.
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.