Results 1 to 6 of 6

Thread: A Query Doubt

  1. #1
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    A Query Doubt

    To retrieve rows X to Y from a table I am using the following query
    1)SELECT *
    FROM
    (SELECT rownum cn,empno,ename,sal
    FROM emp
    WHERE rownum < &x) a
    WHERE a.cn > &y

    It is working fine. In its place if I use the following query it is throwing error message.

    2)SELECT *
    FROM
    (SELECT rownum ,empno,ename,sal
    FROM emp
    WHERE rownum < &x) a
    WHERE a.rownum > &y
    It is throughing error message
    ORA 01747 Invalid column spectification.

    Could any one please suggest what is wrong with second query?


  2. #2
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: A Query Doubt

    ORA: 01747 means
    A column name was specified improperly in the current SQL statement
    u rewrite the query as
    SELECT *
    FROM
    (SELECT rownum rn ,empno,ename,sal
    FROM emp
    WHERE rownum < 26)
    wHERE rn > 20


  3. #3
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: A Query Doubt

    I have already given correct query also. Could any body suggest why it is not allowing rownum directly in codition?


  4. #4
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: A Query Doubt

    You can't use > for comparision with ROWNUM.


  5. #5
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: A Query Doubt

    In the following example I have given <
    still it is showing same error message

    SELECT *
    FROM
    (
    SELECT rownum ,empno,ename,sal
    FROM emp
    ) a
    WHERE a.rownum < 10


  6. #6
    Contributing Member
    Join Date
    Nov 2007
    Answers
    53

    Re: A Query Doubt

    Quote Originally Posted by krishnaindia2007 View Post
    In the following example I have given <
    still it is showing same error message

    SELECT *
    FROM
    (
    SELECT rownum ,empno,ename,sal
    FROM emp
    ) a
    WHERE a.rownum < 10
    Dear krishnaindia2007,

    ROWNUM is a PSEUDO Column and it is used only when you are querying data from table directly i.e. when you are using table name after FROM clause. In your second query you are using 'a' as an ALIAS which itself is not a table.

    In case if you are using ROWNUM in a SELECT statement after FROM clause, you have to specify ALIAS name as you did in your first query.

    I hope this will help you up to some extent.

    Have a pleasant time.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact