IBM Support What's New?

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

Technote (troubleshooting)


Problem(Abstract)

Using an SPSS Statistics 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 successful when one uses the SAMPLE clause within Statistics, rather than run 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: 2016-03-18