A Query Doubt

[krishnaindia2007]

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?

[susarlasireesha]

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

[krishnaindia2007]

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

[debasisdas]

You can't use > for comparision with ROWNUM.

[krishnaindia2007]

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

[sreekumar_nair_it]

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.