OraFAQ Forum: SQL & PL/SQL » alias name in where clause

Home » SQL & PL/SQL » SQL & PL/SQL » alias name in where clause

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

alias name in where clause [message #608469]

Thu, 20 February 2014 03:43

anniepeteroracle
Messages: 47
Registered: March 2012
Location: bangalore

Member

Hi all,

select * from (select ename "desc" from emp ) where desc like 'S%'

ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:
Error at Line: 5 Column: 53

this error is due to desc used in where clause ,my requirement is that with out changing alias name in side sub-query i need to run this query.

Report message to a moderator

Re: alias name in where clause [message #608472 is a reply to message #608469]

Thu, 20 February 2014 04:14

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Hi,

alias name has to follow the rules for database object names. For 11gR2, they are described here:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements008.htm#SQLRF00223

desc = "DESC" != "desc"

As anybody decided to enclose lowercase alias name inside double quotes, it has to be enclosed inside double quotes everywhere.

Report message to a moderator

Re: alias name in where clause [message #608479 is a reply to message #608472]

Thu, 20 February 2014 04:27

msol25
Messages: 396
Registered: June 2011

Senior Member

hi,

Please follow the code tags for giving your sql,pl/sql code.

Please do not use sql reserved word(desc) for giving alias name.Please find
correct query as per your need :


select    * 
from     ( select   name "desc" 
           from emp 
         ) where "desc" like 'A%'

Report message to a moderator

Re: alias name in where clause [message #608502 is a reply to message #608479]

Thu, 20 February 2014 06:14

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

"desc" is NOT a reserved word (DESC is). Razz

Report message to a moderator

Re: alias name in where clause [message #608517 is a reply to message #608469]

Thu, 20 February 2014 07:17

parthiv_t
Messages: 15
Registered: July 2011
Location: ahmedabad

Junior Member

DESC is reserved word.
you can't use as a allies.

Report message to a moderator

Re: alias name in where clause [message #608525 is a reply to message #608517]

Thu, 20 February 2014 08:20

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

"Can't" is not the correct word:

SQL> col desc format a4
SQL> select dummy "DESC" from dual;
DESC
----
X

... but you can also poke a screwdriver into your eye... Smile

Report message to a moderator

Previous Topic:	no of counts from case when
Next Topic:	Future Partitions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 04:51:16 CDT 2024