Oracle – Alias Names and Two Table Query Problems

oracletable-alias

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!

Best Answer

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

Related Solutions

Insert into table from a query to a linked database hangs

Based on your question and other discussion comments, I didn't see any clue for network and blocking issues.

Here, I'd like to give following approaches to try which can help in your situation:

Approach#1:

Suspecting performance issue because of indexes in STG_TRANSIT table. In the sutuation of big STG_TRANSIT table and there are indexes available, what if you might drop them and rebuild them after populating tables during off hours.

Approach#2:

Create temporary table -

create table temp_STG_TRANSIT 
as
select * from 
(
  SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
  FROM sradmin.RACEWAY@sr25 r
  LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

Perform bulk insert -

insert /* APPEND */ into STG_TRANSIT select a.* from temp_STG_TRANSIT as a;

Approach#3:

Trying with DRIVING_SITE hint which forces query execution to be done at a different site than the initiating instance, following query pattern can help in your context:

INSERT INTO STG_TRANSIT(TRANSIT_ID, SHIP, TRANSIT_TYPE, COMPARTMENT_ID, DESCRIPTION)
SELECT /*+ DRIVING_SITE(X) */ *
FROM
(
    SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
    FROM sradmin.RACEWAY@sr25 r
    LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
) X

Refer link for the details of DRIVING_SITE hint.

How to create database/tablespace/schema and users for quickstart developers on Oracle 12

You already have a database, so you don't need the 'create database'.
Since you are working with a 12.1 database, you need to throw out your 10.2 docs and go to the 12.1 doc set.
When you create a user, don't use that @localhost construct. Your simple create user foo identified by bar is the correct syntax, but as @a1ex07 pointed out, you need to be attached to the pluggable database, not the container. This is a drastically new architecture for Oracle, beginning with 12c. You need to spend a bit of time with the 12c Concepts manual (see my link above), especially Part VI - Mutlitenant Architecture.

Related Question