Alias Names and Two Table Query Problems

Question

I'm quite new to querying databases, I've only been doing it for about 2 weeks in the current job that I'm in and have never touched SQL prior to this.

I'm working on trying to use alias names to pull two different sets of tables and display the fields in each table, all while ordering them by a specific field. Here is the code I'm currently trying to use when to do that:

 SELECT ars.facility_id, ars.service_code, ars.coding_system, 
 ars.result_label_seq, ars.display_seq, ats.facility_id, 
 ats.service_code, ats.coding_system, ats.display_name, ats.test_name
 FROM anc_test_codes AS ats,
 anc_results_seqs AS ars
 WHERE ats.service_code = ars.service_code
 AND ars.coding_system = ats.coding_system
 AND ars.facility_id = ats.facility_id 
 AND ats.facility_id = 'C' AND ats.service_code = 'CBC'
 ORDER BY ats.display_name

I'm sure my methods and style is totally hideous, so excuse this. The error I'm getting is a generic Oracle SQL error: SQL Command Not Properly Ended - ORA-00933

Can anyone tell me what I'm doing wrong? Any help on this would be greatly appreciated.

Thanks!

Answer 1

Oracle does not support using the AS keyword for defining a table alias, only for column aliases. Just rewrite the query by placing a space between the table name and the alias and see if it runs that way.

In particular rewrite the FROM clause like this:

 FROM anc_test_codes ats,
 anc_results_seqs ars