topic Re: Why does the ALIAS cause an error? in SAS Programming

Why does the ALIAS cause an error?

FK1 — Tue, 13 Dec 2016 16:28:01 GMT

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:

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

Re: Why does the ALIAS cause an error?

Astounding — Tue, 13 Dec 2016 16:48:58 GMT

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.

Re: Why does the ALIAS cause an error?

FK1 — Tue, 13 Dec 2016 21:31:52 GMT

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

Re: Why does the ALIAS cause an error?

Astounding — Tue, 13 Dec 2016 21:45:29 GMT

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.

Re: Why does the ALIAS cause an error?

Ksharp — Wed, 14 Dec 2016 03:42:13 GMT

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