SQL procedure pass-through views cannot be used with procedures that do multipass processing


Using an SQL view that accesses a Database Management System (DBMS) table using explicit pass-through with a procedure that does multipass processing is not supported. Attempting to do this might result in an error and incorrect results. How that error is surfaced varies based on what you are doing.

For example, accessing a local pass-through view accessing a DBMS table through VIEWTABLE or FSVIEW and applying a WHERE clause might result in incorrect results. The following error might occur in the SAS log:

ERROR: The reopen operation is not supported on a PassThrough data source after the first record has been fetched.

Trying to do the same with a remote view accessed via a Remote Library Services (RLS) library might also display incorrect results, but it will not show an error in the log. The only indication of the problem, other than the incorrect results, is the following message on the SAS Status Bar:

ERROR: An error has occurred

Instead of using explicit pass-through in your view definition, use a LIBNAME that points to your DBMS to avoid this limitation.

For example, your code looks like the following:

proc sql;
connect to oracle(user=xxx pw=xxx path=xxx);
create view myview as
select *
from connection to oracle (
select *
from sasdemo.class);
quit;

Use the following instead:

proc sql;
create view myview as
select *
from oralib.class
using libname oralib oracle user=xxx pw=xxx path=xxx schema=sasdemo;
quit;