DATA Step, Macro, Functions and more

Why does the ALIAS cause an error?

Reply
Occasional Contributor FK1
Occasional Contributor
Posts: 14

Why does the ALIAS cause an error?

Hello Folks,

 

I stumbled upon a weird thing:

 

I am using the pass through facility in order to send a query to an ORACLE database and create a table.

 

When I run the second PROC SQL Statement in the picture below, it works fine:

Abfragen_SQL.JPG

 

However, when I run the upper PROC SQL Statement I get an error message, telling me the following:

 

"ORA-00933: SQL command not properly ended"

 

All I changed between the two PROC SQL Statements is that I assigned an ALIAS to the table WANT. Why does Oracle have trouble with this?

 

Any help from you guys would be much appreciated.

 

Thanks,

Felix

 

 

 

 

Super User
Posts: 5,082

Re: Why does the ALIAS cause an error?

A guess: 

 

When you supply an alias, SQL assumes that you will be reading from more than one source of data.  After all, why assign an alias if you are only reading from one source?  When SQL doesn't see a second source, it generates the error.

 

Occasional Contributor FK1
Occasional Contributor
Posts: 14

Re: Why does the ALIAS cause an error?

@Astounding: Thank you for your answer, but I do not think this is the reason. When running the following SAS code

 

proc sql;
select A.height
from sashelp.class as A;
quit;

I do not get any error messages in the log. But of course this could also be related to the fact that the above statment is being processed locally and not by the means of the pass through facility....

 

Gegenbeispiel.JPG

Super User
Posts: 5,082

Re: Why does the ALIAS cause an error?

As you noted, it could be the pass-through feature.  Each SQL implementation has its own quirks.  Perhaps you could try a version of the successfully executing alias, but use that syntax directly on the pass-through system.

Super User
Posts: 9,681

Re: Why does the ALIAS cause an error?

Yeah. Firstly you need make this SQL is a legal oracle SQL, talk to your oracle DBA.

Ask a Question
Discussion stats
  • 4 replies
  • 195 views
  • 0 likes
  • 3 in conversation