DATA Step, Macro, Functions and more

Duplicate SQL running on Oracle

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Duplicate SQL running on Oracle

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.


Accepted Solutions
Solution
‎02-18-2014 03:05 PM
Super Contributor
Posts: 578

Re: Duplicate SQL running on Oracle

Posted in reply to DavidCaliman

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


All Replies
Super Contributor
Posts: 578

Re: Duplicate SQL running on Oracle

Posted in reply to DavidCaliman

are both of the tables in oracle?

Contributor
Posts: 58

Re: Duplicate SQL running on Oracle

No.

ODS.ODS_CUSTOMER -> SAS table

ORA.CUSTOMER-> Oracle table

Super Contributor
Posts: 578

Re: Duplicate SQL running on Oracle

Posted in reply to DavidCaliman

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;

Contributor
Posts: 58

Re: Duplicate SQL running on Oracle

DBailey,

This code is also generating 2 SQLs in the database.

Super Contributor
Posts: 578

Re: Duplicate SQL running on Oracle

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?

Contributor
Posts: 58

Re: Duplicate SQL running on Oracle

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.

Super Contributor
Posts: 307

Re: Duplicate SQL running on Oracle

Posted in reply to DavidCaliman

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.

Super Contributor
Posts: 307

Re: Duplicate SQL running on Oracle

Posted in reply to DavidCaliman

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?

Contributor
Posts: 58

Re: Duplicate SQL running on Oracle

Forgiveness. I marked the wrong answer as helpful.

Contributor
Posts: 58

Re: Duplicate SQL running on Oracle

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

Solution
‎02-18-2014 03:05 PM
Super Contributor
Posts: 578

Re: Duplicate SQL running on Oracle

Posted in reply to DavidCaliman

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;

Super User
Posts: 19,817

Re: Duplicate SQL running on Oracle

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

Super Contributor
Posts: 578

Re: Duplicate SQL running 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.

Super User
Posts: 19,817

Re: Duplicate SQL running on Oracle

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. 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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