Home » Developer & Programmer » Forms » NVL with ORDER BY (SQL)
NVL with ORDER BY [message #283352] Mon, 26 November 2007 22:06 Go to next message
ORAGENASHOK
Messages: 238
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.

Re: NVL with ORDER BY [message #283388 is a reply to message #283352] Tue, 27 November 2007 00:25 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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 ...
Re: NVL with ORDER BY [message #283554 is a reply to message #283388] Tue, 27 November 2007 08:08 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
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
Re: NVL with ORDER BY [message #283625 is a reply to message #283554] Tue, 27 November 2007 14:30 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd like to see your SQL*Plus session, please.
Re: NVL with ORDER BY [message #283739 is a reply to message #283554] Wed, 28 November 2007 01:21 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Hi

Please find the attached for my session for SQL* PLUS
Re: NVL with ORDER BY [message #283757 is a reply to message #283739] Wed, 28 November 2007 01:42 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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.
Re: NVL with ORDER BY [message #283821 is a reply to message #283757] Wed, 28 November 2007 04:17 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Hi,

You mean you are not able to download my attachement right
Re: NVL with ORDER BY [message #283823 is a reply to message #283821] Wed, 28 November 2007 04:20 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Hi,

Please find the attached txt file
Re: NVL with ORDER BY [message #283892 is a reply to message #283352] Wed, 28 November 2007 06:28 Go to previous messageGo to next message
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?
Re: NVL with ORDER BY [message #283925 is a reply to message #283892] Wed, 28 November 2007 07:20 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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
...
Re: NVL with ORDER BY [message #288832 is a reply to message #283554] Tue, 18 December 2007 21:39 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: error when changing item visual attribute property
Next Topic: Hierchy Treee
Goto Forum:
  


Current Time: Wed Dec 07 10:53:50 CST 2016

Total time taken to generate the page: 0.21926 seconds