Date:         Mon, 14 Nov 2005 10:44:05 -0500
Reply-To:     joep Steeman <jsteeman@BUSINESSDECISION.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         joep Steeman <jsteeman@BUSINESSDECISION.COM>
Subject:      Re: Acces to oracle

On Mon, 14 Nov 2005 05:15:26 -0800, jeanjou <lavigne_olivier@YAHOO.FR> wrote:

>But if I use sql pass trough, the result is Oracle table, isn't it?
>I have a big doubt about the use of sql pass through.
>
>For example:
>
>I use
>
>proc sql;
>connect to oracle (user=&user orapw=&pw path=&alias schema=&schema);
>create titi as select * from connection to oracle
>(select table_name as tabname from select
>        distrib,no_client,cotisation,mois annee
>        from contrats
>        where cotisation >1000);
>disconnect from oracle;
>quit;
>
>The table titi is created on SAS side?
>And it's faster than than a "classic" proc sql?
>
>Best regards,
>Olivier.

Olivier,

The syntax would be:

proc sql;
connect to oracle (user=&user orapw=&pw path=&alias schema=&schema);
create titi as select * from connection to oracle
  ( select  distrib,  no_client,  cotisation , mois annee
    from contrats
    where cotisation >1000);
disconnect from oracle;
quit;

So you actually don't create a table on the oracle side. The results of the remote query will get
passed to you, so 500K instead 30M records over the network.

Regards, Joep