OraFAQ Forum: Forms » NVL with ORDER BY

Home » Developer & Programmer » Forms » NVL with ORDER BY (SQL)

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

NVL with ORDER BY [message #283352]

Mon, 26 November 2007 22:06

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

Hi all,

when i am using nvl with order by clause it will show an error as SELECTED FAILURE.

Ex:
select nvl(emp_no,' ') from emp where emp_id like '123%' order by emp_id;

The above is the sample query.I am also planned to use the join in the above query.

Report message to a moderator

Re: NVL with ORDER BY [message #283388 is a reply to message #283352]

Tue, 27 November 2007 00:25

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

'SELECTED FAILURE' is not an Oracle error, as far as I can tell. Could you post the whole example (copy-paste your SQL*Plus session)?

Otherwise, check 'emp_no' datatype. If it is NUMBER, you can not NVL it to a character (' '; is it s space?). You'll have to use something like

SELECT NVL(emp_no, 1)  --> 1 instead of ' '
FROM emp ...

Report message to a moderator

Re: NVL with ORDER BY [message #283554 is a reply to message #283388]

Tue, 27 November 2007 08:08

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

Thank You Its working fine with replacement of space into '1'

Even when i am trying to combine the one or more another tables into the query and with the ORDER BY clause it will not work.

For Example :

select nv(a.emp_no,' '),nvla.ename,' '),nvl(b.dept,' ')
from emp a,dept b
where a.emp_no=b.emp_no
order by a.emp_no

It shows the Error : ORA-01791 not a SELECTed expression

Report message to a moderator

Re: NVL with ORDER BY [message #283625 is a reply to message #283554]

Tue, 27 November 2007 14:30

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'd like to see your SQL*Plus session, please.

Report message to a moderator

Re: NVL with ORDER BY [message #283739 is a reply to message #283554]

Wed, 28 November 2007 01:21

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

Hi

Please find the attached for my session for SQL* PLUS

Attachment: My_session.doc
(Size: 41.00KB, Downloaded 1513 times)

Report message to a moderator

Re: NVL with ORDER BY [message #283757 is a reply to message #283739]

Wed, 28 November 2007 01:42

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Please, read the OraFAQ Forum Guide to find out what kinds of attached files are (not) welcome here.

I may not (and do not want to) download .DOC files from an unknown source. SQL*Plus session can be attached directly or as a pure .TXT file.

Report message to a moderator

Re: NVL with ORDER BY [message #283821 is a reply to message #283757]

Wed, 28 November 2007 04:17

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

Hi,

You mean you are not able to download my attachement right

Report message to a moderator

Re: NVL with ORDER BY [message #283823 is a reply to message #283821]

Wed, 28 November 2007 04:20

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

Hi,

Please find the attached txt file

Attachment: My_Session.txt
(Size: 0.32KB, Downloaded 988 times)

Report message to a moderator

Re: NVL with ORDER BY [message #283892 is a reply to message #283352]

Wed, 28 November 2007 06:28

scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England

Senior Member

I think what Littlefoot meant was to show the SQL*Plus session with your query and the error you are getting?

Report message to a moderator

Re: NVL with ORDER BY [message #283925 is a reply to message #283892]

Wed, 28 November 2007 07:20

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Of course; that's exactly what I meant.

Something like this example:

SQL> select ename, sal, nvl(comm, -1) comm_or_not
  2  from emp;

ENAME             SAL COMM_OR_NOT
---------- ---------- -----------
SMITH             800          -1
ALLEN            1600         300
WARD             1250          -1
JONES            2975          -1
...

Report message to a moderator

Re: NVL with ORDER BY [message #288832 is a reply to message #283554]

Tue, 18 December 2007 21:39

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Have you solved your problem?

"The alias [of a column] can be used in the ORDER BY clause"

"To order by a lengthy select list expression, you can specify its position, rather than duplicate the entire expression, in the ORDER BY clause"

SELECT ename, deptno, sal 
	FROM emp 
	ORDER BY 2 ASC, 3 DESC

David

Report message to a moderator

Previous Topic:	error when changing item visual attribute property
Next Topic:	Hierchy Treee

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 27 08:23:40 CDT 2024