topic Re: Faster way to select top n rows from oracle connection in New SAS User

Faster way to select top n rows from oracle connection

MikeAAC — Wed, 19 Dec 2018 18:45:25 GMT

Hi all.

Assume the code below. I am using 'inobs=1000' or 'obs=1000' options on the proc sql statement to select the top 1000 records. Is there a faster way to get the same result? I thought I could use the WHERE or SELECT TOP statement but I getting errors.

PROC SQL;

CONNECT TO ORACLE as EstCon

(PATH=mypath authdomain=Oracledev);

CREATE TABLE MyTable AS

SELECT *

FROM CONNECTION TO EstCon (

SELECT

t1.ID, t2.name, t3.gender, t4.Age

FROM Table1 t1

INNER JOIN table2 t2 ON (t1.ID = t2._ID)

INNER JOIN table3 t6 ON (t1.ID = t3.ID)

INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID));

DISCONNECT FROM EstCon;

QUIT;

Re: Faster way to select top n rows from oracle connection

Reeza — Wed, 19 Dec 2018 18:37:32 GMT

You can use select top, but it has to be in the inner most query since it's only available in Oracle, not SAS.

Re: Faster way to select top n rows from oracle connection

MikeAAC — Wed, 19 Dec 2018 19:12:05 GMT

Thanks Reeza.

I tried it but I got this error msg:

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: SELECT TOP 100 * FROM table1 t1 inner join table2 on (t1.ID = t2.ID)...

Re: Faster way to select top n rows from oracle connection

r_behata — Wed, 19 Dec 2018 19:17:58 GMT

The most efficient way is to filter the rows in oracle :

PROC SQL;

   CONNECT TO ORACLE as EstCon

    (PATH=mypath authdomain=Oracledev);

            CREATE TABLE MyTable AS

            SELECT *

            FROM CONNECTION TO EstCon (

                 SELECT 
                       t1.ID,  t2.name, t3.gender, t4.Age

                      FROM Table1 t1

                      INNER JOIN table2 t2 ON (t1.ID = t2._ID)

                      INNER JOIN table3 t6 ON (t1.ID = t3.ID)

                      INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID)
				WHERE rownum  <=1000
);

             DISCONNECT FROM EstCon;

QUIT;

This is the alternate query to filter in sas:

PROC SQL outobs=1000;

   CONNECT TO ORACLE as EstCon

    (PATH=mypath authdomain=Oracledev);

            CREATE TABLE MyTable(obs=2) AS

            SELECT *

            FROM CONNECTION TO EstCon (

                 SELECT 

                       t1.ID,  t2.name, t3.gender, t4.Age

                      FROM Table1 t1

                      INNER JOIN table2 t2 ON (t1.ID = t2._ID)

                      INNER JOIN table3 t6 ON (t1.ID = t3.ID)

                      INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID));

             DISCONNECT FROM EstCon;

QUIT;

Re: Faster way to select top n rows from oracle connection

r_behata — Wed, 19 Dec 2018 19:22:16 GMT

May be try this :

PROC SQL;

   CONNECT TO ORACLE as EstCon

    (PATH=mypath authdomain=Oracledev);

            CREATE TABLE MyTable AS

            SELECT *

            FROM CONNECTION TO EstCon (

             SELECT * FROM (   SELECT 
                       t1.ID,  t2.name, t3.gender, t4.Age

                      FROM Table1 t1

                      INNER JOIN table2 t2 ON (t1.ID = t2._ID)

                      INNER JOIN table3 t6 ON (t1.ID = t3.ID)

                      INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID)) SAMP
				WHERE rownum  <=1000
				ORDER BY rownum
);

             DISCONNECT FROM EstCon;

QUIT;

Re: Faster way to select top n rows from oracle connection

kiranv_ — Wed, 19 Dec 2018 19:29:14 GMT

try rownum.

PROC SQL;

   CONNECT TO ORACLE as EstCon

    (PATH=mypath authdomain=Oracledev);

            CREATE TABLE MyTable AS

            SELECT *

            FROM CONNECTION TO EstCon (
          select * from 

               (  SELECT

                       t1.ID,  t2.name, t3.gender, t4.Age

                      FROM Table1 t1

                      INNER JOIN table2 t2 ON (t1.ID = t2._ID)

                      INNER JOIN table3 t6 ON (t1.ID = t3.ID)

                      INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID))
WHERE ROWNUM <= 1000);

             DISCONNECT FROM EstCon;

QUIT;

Re: Faster way to select top n rows from oracle connection

SuryaKiran — Wed, 19 Dec 2018 21:34:08 GMT

If you want to limit the results of a query to see the first 1000 where order by matters than you might use a sub-query technique additionally.

SELECT *
FROM (SELECT t1.ID,  t2.name, t3.gender, t4.Age
          FROM Table1 t1
             INNER JOIN table2 t2 ON (t1.ID = t2._ID)
             INNER JOIN table3 t6 ON (t1.ID = t3.ID)
             INNER JOIN table4 ON (t1.PARTY_ID = t4.PARTY_ID)
          Order by t1.PARTY_ID)
			 
WHERE ROWNUM <=1000;