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.

Rate this page:

(0 users)Average rating

Add comments

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

Machine Translation

Content navigation