Reading data from an external data source (ODBC, Oracle, Teradata, and so on) using a PROC SQL query with a HAVING clause might produce an incorrect result.
This issue can happen in the following rare situations:
- The query contains identical column aliases in a SELECT and an in-line SELECT clause.
- The query contains a HAVING clause with this column alias.
A result set is created. However, if you have the SAS trace option enabled, the SAS log shows a message similar to the following:
ERROR: ORACLE prepare error: ORA-00904: "TABLE"."COL2": invalid identifier, while preparing the SQL for the database.
This message is an example of the error message that you receive when running against an Oracle database.
Here is example code that triggers this issue (Notice column alias COL2):
LIBNAME DB <DB ENGINE> <CONNECTION OPTIONS>;
PROC SQL;
CREATE TABLE WORK.TEST AS
SELECT
t1.COL1,
(SUM(t1.COL2)) AS COL2
FROM (
SELECT
t1.COL1,
(SUM(t1.COL2)) AS COL2
FROM DB.TABLE1 t1
INNER JOIN DB.TABLE2 t2 ON (t1.COL1 = t2.COL1)
GROUP BY t1.COL1
) t1
HAVING (CALCULATED COL2) > 0;
QUIT;
To work around the problem, use distinct aliases for the column name, as shown here:
LIBNAME DB <DB ENGINE> <CONNECTION OPTIONS>;
PROC SQL;
CREATE TABLE WORK.TEST AS
SELECT
t1.COL1,
(SUM(t1.COL2)) AS COL2_X
FROM (
SELECT
t1.COL1,
(SUM(t1.COL2)) AS COL2
FROM DB.TABLE1 t1
INNER JOIN DB.TABLE2 t2 ON (t1.COL1 = t2.COL1)
GROUP BY t1.COL1
) t1
HAVING (CALCULATED COL2_X) > 0;
QUIT;