When I run the PROC SQL below in DI Studio, I saw that at the oracle log, two SQLs are executed.:
PROC SQL;
INSERT INTO ODS.ODS_CUSTOMER (ID,DESC)
SELECT ID, DESC
FROM ORA.CUSTOMER
WHERE ID IN (1,2);
QUIT;
SQL 1) SELECT "ID", "DESC" FROM CUSTOMER WHERE ( ("ID" IN ( 1 , 2 ) ) )
SQL 2) SELECT * FROM CUSTOMER
Can anyone tell me if there is the option to not execute the second SQL (Select * from)? As the table in question is very large, i'm having a performance problem.
If that is your log, then you aren't running a pass-through query. Looks like you're running a normal statement. To use the pass-through, add "as conn" to your connection.
proc sql;
create table ods.ods_customer like ora.customer;
connect to oracle as conn (path='...' user=XXXXX password=XXXXXX);
insert into ods.ods_customer
select * from connection to conn (
select * from customer where id in (1,2)
);
disconnect from conn;
quit;
are both of the tables in oracle?
No.
ODS.ODS_CUSTOMER -> SAS table
ORA.CUSTOMER-> Oracle table
proc sql noprint;
connect to oracle as ora (valid connection data would go here);
INSERT INTO ODS.ODS_CUSTOMER (ID,DESC)
select id, desc from connection to ora (
SELECT ID, DESC
FROM ActualOracleSchema.CUSTOMER
WHERE ID IN (1,2)
);
disconnect from ora;
quit;
DBailey,
This code is also generating 2 SQLs in the database.
This code produces two oracle statements, but only one is executed.
options sastrace=',,,d' sastraceloc=saslog;
proc sql;
connect to oracle as CDBMR (path='PMIS' ........);
create table work.test as select * from connection to cdbmr (
select date_value from mdm.dbdates
where date_value= to_date('30Nov2013')
);
DISCONNECT FROM CDBMR;
quit;
Here's the log:
16 proc sql;
17 connect to oracle as CDBMR (path='PMIS' ..........);
18
19 create table work.test as select * from connection to cdbmr (
20 select date_value from mdm.dbdates
21 where date_value= to_date('30Nov2013')
22 );
ORACLE_4: Prepared: on connection 5
select date_value from mdm.dbdates where date_value= to_date('30Nov2013')
ORACLE_5: Executed: on connection 5
SELECT statement ORACLE_4
NOTE: Table WORK.TEST created, with 1 rows and 1 columns.
What does your log look like?
My Log:
729 /*---- Start of User Written Code ----*/
730
731 options sastrace=',,,d' sastraceloc=saslog;
732 proc sql;
733 connect to oracle (user=XXXXX password=XXXXXX);
734 INSERT INTO ODS.ODS_CUSTOMER
735 SELECT *
736 FROM ORA.CUSTOMER
737 WHERE ID IN (1,2);
12 1705687392 Main 0 SQL (2)
ORACLE_4: Prepared: on connection 2 13 1705687392 Main 0 SQL (2)
SELECT * FROM CUSTOMER 14 1705687392 Main 0 SQL (2)
25 The SAS System 13:28 Saturday, January 18, 2014
15 1705687392 Main 0 SQL (2)
16 1705687392 Main 0 SQL (2)
ORACLE_5: Prepared: on connection 2 17 1705687392 Main 0 SQL (2)
SELECT "ID", "DESC" FROM CUSTOMER WHERE ( ("ID" IN ( 1 , 2) ) ) 18 1705687392 Main 0 SQL (2)
19 1705687392 Main 0 SQL (2)
20 1705687392 Main 0 SQL (2)
ORACLE_6: Executed: on connection 2 21 1705687392 Main 0 SQL (2)
SELECT statement ORACLE_5 22 1705687392 Main 0 SQL (2)
23 1705687392 Main 0 SQL (2)
NOTE: No rows were inserted into ODS.ODS_CUSTOMER.
David - I see only one execution statement in your log, but you are using explicit pass-through this time -- not your original implicit pass-through (libname method) code.
As I stated in my earlier post, SAS may not be able to pass the SQL to Oracle for processing via implicit pass-through (Libname method) because your code contains an INSERT statement. To clarify, the restriction is actually on usage of the INTO clause. (But, the INSERT statement uses the INTO clause so the restriction extends to the INSERT statement.
See pdf page 6 of https://support.sas.com/techsup/technote/ts661.pdf
The purpose of my post here is to explain why you may be seeing two sql statements executing when attempting to use implicit pass-through (libname method) to perform an INSERT.
David - you marked your own reply as a Helpful Answer? You do realize that forum members have taken the time to propose solutions or to provide information, and that the purpose of marking entries as Helpful Answer or Correct Answer is to recognize their contributions?
Forgiveness. I marked the wrong answer as helpful.
DBailey,
I ran your program here and it worked. Ran only 1 sql on database.
How would the adaptation of the command below to insert rather than create?
create table work.test as select * from connection to cdbmr (
20 select date_value from mdm.dbdates
21 where date_value= to_date('30Nov2013')
22 );
If that is your log, then you aren't running a pass-through query. Looks like you're running a normal statement. To use the pass-through, add "as conn" to your connection.
proc sql;
create table ods.ods_customer like ora.customer;
connect to oracle as conn (path='...' user=XXXXX password=XXXXXX);
insert into ods.ods_customer
select * from connection to conn (
select * from customer where id in (1,2)
);
disconnect from conn;
quit;
I don't think that will work, because ods.ods_customer is a SAS table, not on Oracle.
What were you thinking wouldn't work? The create table like command? I think SAS looks at the oracle table structure and creates a new table with it.
No, that's fine, but that table (ODS.ODS_CUSTOMER) lives as a SAS data set, according to the OP. For some reason, I assumed different database and that the explicit pass thru wouldn't see that table.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.