Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
TOP for Oracle
Message
 
 
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Miscellaneous
Thread ID:
00869250
Message ID:
00869265
Views:
14
>Does Oracle PL/SQL have an equivalent to the SELECT TOP x records like MS TSQL?

The answer is not exactly, but you can get what you want by calling a SP in Oracle which will return a cursor for you:
CREATE OR REPLACE PACKAGE ArticleGetMostPopular AS
 Type RetCursor Is Ref Cursor;
 Procedure ReturnCursor(
    nCount  IN  number,
    pCursor OUT RetCursor);
END;
/
show error
CREATE OR REPLACE PACKAGE BODY ArticleGetMostPopular AS
 Procedure ReturnCursor(
    nCount  IN  number,
    pCursor OUT RetCursor) is
 Begin
  open pCursor for
    select * FROM 
     (SELECT Article.ID, COUNT(*) AS Count
      FROM Article
      WHERE SomeCriteriaHere
      GROUP BY Article.ID
      ORDER BY SomeCriteriaHere DESC)
      where ROWNUM <= nCount;
 End;
END;
/
show error
I left out the GROUP BY in the private message. In VFP to call the SP:
nCount = 10  && get top 10 records
lcSQL = "{call ArticleGetMostPopular.ReturnCursor(" + TRANSFORM(nCount) + ")}"
lnRetVal = SQLEXEC(lnHandle, lcSQL, 'crsMostPopular')
Mark McCasland
Midlothian, TX USA
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform