Error when using the "Sample" option in an Enterprise View connected to ORACLE

Technote (troubleshooting)


Problem(Abstract)

When using an Enterprise View to access data from an Oracle 10 G source, the following error is raised:

SQLExecDirect failed :[520][ODBC Oracle Wire Protocol driver][Oracle]
ORA-00933: SQL command not properly ended

Symptom

No Data are imported from the Enterprise View


Cause

When using the PEV driver, the query sent to the database will include a subselect (subquery).
Example:
SELECT "gender", "id" FROM (SELECT "gender", "id" FROM (SELECT "id" AS "id","gender" AS "gender" FROM (SELECT * FROM SCOTT."test") DPD_13895699) ORA_2) ORA_2 SAMPLE(10)

But the ORACLE SAMPLE clause does not support a subquery.
One can test this with the simplest subselect with will only work without the SAMPLE clause.
SELECT * FROM (SELECT EMPNO FROM SCOTT.EMP) SAMPLE(50)

Environment

N/A

Diagnosing the problem

One can find a detailed explanation in the following documentation:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2065953
Search for this statement: "Restrictions on sample_clause You cannot specify the SAMPLE clause in a subquery in a DML statement."

Resolving the problem

The import of data is properly executed when one use the SAMPLE clause within Statistics, rather than execute it in the Database.

Document information


More support for:

SPSS Statistics
Statistics Desktop

Software version:

20.0

Operating system(s):

Platform Independent

Reference #:

1623579

Modified date:

2013-01-28

Translate my page

Content navigation