Date:         Thu, 30 Jun 2011 17:32:33 +0000
Reply-To:     "DUELL, BOB (ATTCINW)" <bd9439@ATT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "DUELL, BOB (ATTCINW)" <bd9439@ATT.COM>
Subject:      Re: SQL pass through create table in Oracle?
Comments: To: Ya Huang <ya.huang@AMYLIN.COM>
In-Reply-To:  <201106301723.p5UAlN0n024972@waikiki.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Hi Ya,

You can create an Oracle table with pass-thru in an EXECUTE statement (not a SELECT statement).  Each should be passed in a separate EXCECUTE:

proc sql;
  connect to oracle(path=&URL user=&UID pass=&PSS);
  execute (
     create table &schema..junk1 as
     select CASE_ID, CASE_NUM
     from   LSS_ESD_DATA_VIEW.lss_esd_rpt_case
     ) by oracle;

  execute (
     create table &schema..junk2 as
     select case_id, event_seq_num
     from   LSS_ESD_DATA_VIEW.lss_esd_rpt_event_assess
     ) by oracle;

   create table sasdatasetname as
   select * from connection to oracle (
     select *
     from &schema..junk1 a,
          &schema..junk2 b
      where a.case_id=b.case_id
     );
quit;

This of course assumes you have CREATE TABLE rights in whatever &schema refers to.


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ya Huang
Sent: Thursday, June 30, 2011 10:23 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SQL pass through create table in Oracle?

I can o this:

proc sql;
  connect to oracle(path=&URL user=&UID pass=&PSS);
    select *
    from connection to oracle
    (
     select *
     from (select CASE_ID, CASE_NUM
           from LSS_ESD_DATA_VIEW.lss_esd_rpt_case) a,
          (select case_id, event_seq_num
           from LSS_ESD_DATA_VIEW.lss_esd_rpt_event_assess) b
      where a.case_id=b.case_id
     );
quit;

But I wonder if this is too much of nesting.

On Thu, 30 Jun 2011 13:16:31 -0400, Ya Huang <ya.huang@AMYLIN.COM> wrote:

>Hi there,
>
>It seems that the only way I can get the data I want from Oracle fast
>enough is to do as much as possible data manipulation in Oracle, so that
the
>result data is much smaller to get throught the network faster. But
>sometimes, I can't get all joins done in one sql step, therefore I have
>to have two pass through, then join the data locally in SAS. I wonder
>if I can create temp tables (like I can create table in SAS WORK) in
Oracle,
>then join them over there in Oracle, then pass through the result data.
>
>I tried something like this:
>
>proc sql;
>  connect to oracle(path=&URL user=&UID pass=&PSS);
>    select *
>    from connection to oracle
>    (create table &schema..junk1 as
>     select CASE_ID, CASE_NUM
>     from LSS_ESD_DATA_VIEW.lss_esd_rpt_case;
>
>     create table &schema..junk2 as
>   select case_id, event_seq_num
>     from LSS_ESD_DATA_VIEW.lss_esd_rpt_event_assess;
>
>     select *
>     from &schema..junk1 a,
>          &schema..junk2 b
>      where a.case_id=b.case_id;
>     );
>quit;
>
>
>But I got the message "ERROR: ORACLE prepare error: ORA-24333: zero
>iteration count. ".
>
>Am I using the correct syntax?
>
>Thanks
>
>Ya