BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidCaliman
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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;

View solution in original post

24 REPLIES 24
DBailey
Lapis Lazuli | Level 10

are both of the tables in oracle?

DavidCaliman
Calcite | Level 5

No.

ODS.ODS_CUSTOMER -> SAS table

ORA.CUSTOMER-> Oracle table

DBailey
Lapis Lazuli | Level 10

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;

DavidCaliman
Calcite | Level 5

DBailey,

This code is also generating 2 SQLs in the database.

DBailey
Lapis Lazuli | Level 10

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?

DavidCaliman
Calcite | Level 5

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.

Fugue
Quartz | Level 8

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.

Fugue
Quartz | Level 8

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?

DavidCaliman
Calcite | Level 5

Forgiveness. I marked the wrong answer as helpful.

DavidCaliman
Calcite | Level 5

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         );

DBailey
Lapis Lazuli | Level 10

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;

Reeza
Super User

I don't think that will work, because ods.ods_customer is a SAS table, not on Oracle.

DBailey
Lapis Lazuli | Level 10

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.

Reeza
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 24 replies
  • 1910 views
  • 0 likes
  • 7 in conversation