Home » SQL & PL/SQL » SQL & PL/SQL » Error In quert (oracle 10g)
Error In quert [message #334351] Wed, 16 July 2008 04:54 Go to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
i am getting error in query
select empno,ename,row_number() over(partition by empno order by empno) ctn from emp where ctn>1

ORA-00904: "CTN": invalid identifier

Re: Error In quert [message #334352 is a reply to message #334351] Wed, 16 July 2008 04:57 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You cannot use an alias created in the where clause as an object in the where clause.
Re: Error In quert [message #334367 is a reply to message #334351] Wed, 16 July 2008 05:44 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Actually
You can't reference just given alias in your where clause..

but you can use like the following:
For Ex:

SQL> select * from(select job,avg(sal) salary
  2  from emp
  3  group by job)
  4  where salary>1000;

JOB           SALARY
--------- ----------
CLERK         1037.5
SALESMAN        1400
PRESIDENT       5000
MANAGER   2758.33333
ANALYST         3000



It's not the exact answer for your query..
but a small hint

Thanks

[mod-edit] BOLD tags removed from CODE tags.

[Updated on: Wed, 16 July 2008 07:39] by Moderator

Report message to a moderator

Re: Error In quert [message #334368 is a reply to message #334367] Wed, 16 July 2008 05:47 Go to previous message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Instead of bold letters for salary

It's misspelled as salary

Sorry for the inconvenience.

Thanks
Previous Topic: parameter Package IF THEN Else
Next Topic: ORA-01445: cannot select ROWID from a join view without a key-preserved table
Goto Forum:
  


Current Time: Sat Dec 03 01:22:58 CST 2016

Total time taken to generate the page: 0.13070 seconds