Hi guys, I'm using SAS 9.4 and I have SAS/ACCESS to Oracle installed. Whenever I query data from Oracle, the interface seems to first SELECT * from the oracle tables involved instead of only selecting the colums that I selected in my proc sql statements. For example, when I execute: PROC SQL; select t1.name, t2.age, t1.weight from oratable t1 inner join oratable2 t2 on t1.name = t2.name; quit; Then the log shows that SAS first queries the entire table which is going to be transferred over the network and takes forever: select * from oratable select * from oratable2 This is an abstract from the actual log: NOTE: PROCEDURE PRINTTO used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 96.18k OS Memory 23144.00k Timestamp 01/06/2015 09:17:21 AM Step Count 51 Switch Count 112 NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 599.21k OS Memory 22884.00k Timestamp 01/06/2015 09:17:21 AM Step Count 52 Switch Count 0 ORACLE_49: Prepared: on connection 1 SELECT * FROM oratable.DATA_STRUCTURES ORACLE: DESCRIBE on ORACLE_49 ORACLE_50: Prepared: on connection 1 SELECT "PHASE", "DSD_ID" FROM oratable0.DATA_STRUCTURES WHERE ( UPPER("DSD_bla") = 'bla_bla' ) ORACLE: DESCRIBE on ORACLE_50 ORACLE_51: Executed: on connection 1 SELECT statement ORACLE_50 ORACLE: READBUFF option value set to 682. ORACLE: The fetch time in seconds for 1 rows is 0.000000 ORACLE: Rows fetched : 1 Summary Statistics for ORACLE are: Total row fetch seconds were: 0.001689 Total SQL execution seconds were: 0.000429 Total SQL prepare seconds were: 0.005079 Total seconds used by the ORACLE ACCESS engine were 0.008692 3 The SAS System 08:57 Tuesday, January 6, 2015 NOTE: PROCEDURE SQL used (Total process time): real time 0.30 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 993.53k OS Memory 23312.00k Timestamp 01/06/2015 09:17:21 AM Step Count 53 Switch Count 7 ORACLE_52: Prepared: on connection 1 SELECT * FROM oratable.OBSERVATIONS ORACLE: DESCRIBE on ORACLE_52 ORACLE_53: Prepared: on connection 1 SELECT * FROM oratable.VARIABLES ORACLE: DESCRIBE on ORACLE_53 ORACLE_54: Prepared: on connection 1 SELECT * FROM oratable.REC_MOD ORACLE: DESCRIBE on ORACLE_54 ORACLE_55: Prepared: on connection 1 SELECT * FROM oratable.MOD ORACLE: DESCRIBE on ORACLE_55 ORACLE_56: Prepared: on connection 1 SELECT * FROM oratable.TAXES ORACLE: DESCRIBE on ORACLE_56 SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing. This couldn't possibly be the way the interface is supposed to work. When I run some of these queries directly on Oracle using SQL Developer they take less than a second. In SAS they take 1 minute, because it frist transfers the entire table to the application server. Any suggestions? Thanks!!!
... View more