IBM Support

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

Troubleshooting


Problem

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

[{"Product":{"code":"SSLVMB","label":"IBM SPSS Statistics"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Statistics Desktop","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"20.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 April 2020

UID

swg21623579