Error using the SPSS Statistics "Sample" option in an Enterprise View connected to ORACLE
Using an SPSS Statistics Enterprise View to access data from an Oracle 10 G source, the following error is raised:
SQLExecDirect failed :[ODBC Oracle Wire Protocol driver][Oracle]
ORA-00933: SQL command not properly ended
No Data are imported from the Enterprise View
When using the PEV driver, the query sent to the database will include a subselect (subquery).
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)
Diagnosing the problem
One can find a detailed explanation in the following documentation:
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.