We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

order by in subquery

Medium Priority
1,803 Views
Last Modified: 2013-12-11
  CURSOR amd
   IS
      SELECT   SID, NAME, phoneno, CLASS
          FROM (SELECT ROWNUM c, SID, NAME, CLASS, phoneno
                  FROM student
                 WHERE CLASS = :classes AND branch = :student.br
                       AND edate IS NULL)
         WHERE c <= 20
      ORDER BY SID;

is working fine in forms 6i.
but i want ORDER BY IN subquery

   CURSOR amd
   IS
      SELECT   SID, NAME, phoneno, CLASS
          FROM (SELECT ROWNUM c, SID, NAME, CLASS, phoneno
                  FROM student
                 WHERE CLASS = :classes AND branch = :student.br
                       AND edate IS NULL order by SID)
         WHERE c <= 20;
In SQL*Plus both working fine but in forms the 2nd creates problems

I know it is odd to use order by in subquery but i need it here because of rownum
any solutions
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Muhammad Ahmad ImranDatabase Developer

Author

Commented:
No, it still acts as a sub-query.

??
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, that was just an example illustrating the fix for the problem with your order by and rownum usage.

To solve the subquery problem you will have to follow one of the other suggestions.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
http:#24763954  answers the question.  bulk of it explains what is wrong, last two lines provide alternatives for forms limitations
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.