Faster way to select top n rows from oracle connection

[MikeAAC]

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;

[Reeza]

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

[MikeAAC]

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

[r_behata]

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;

[r_behata]

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;

[kiranv_]

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;

[SuryaKiran]

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;