Home » SQL & PL/SQL » SQL & PL/SQL » case or decode (oracle 9.2.0.1.0 windows)
case or decode [message #366249] Fri, 12 December 2008 05:04 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i know this is a silly question.....
Can we use select statment inside case or decode function
i need to get the dname from dept table for deptno in emp table

this can be done in simple query

select ep.empno,ep.ename,ep.sal,dp.dname,dp.deptno 
from emp ep,dept dp
where dp.deptno=ep.deptno


but is it possible to do through case or decode function

something like this
select ep.empno,ep.ename,ep.sal,(case when ep.deptno is 10 
then (select dp.dname from dept where dp.deptno=10)
when ep.deptno 20 then (select dp.dname from dept where dp.deptno=10)
when ep.deptno 30 then (select dp.dname from dept where dp.deptno=10))
dept_name,deptno from emp ep,dept dp
where dp.deptno=ep.deptno


sample ouput like this
empno	ename	sal	dname	      deptno
7782	CLARK	2450	ACCOUNTING	10
7566	JONES	2975	RESEARCH	20
7521	WARD	1250	SALES	        30
7839	KING	5000	OPERATIONS	40


thanxs and regards
Re: case or decode [message #366251 is a reply to message #366249] Fri, 12 December 2008 05:13 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why don't you try it for yourself and see if that works. By doing it that way you learn a lot.

To answer your question yes it is possible.

Regards

Raj
Re: case or decode [message #366253 is a reply to message #366251] Fri, 12 December 2008 05:20 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
By doing it that way you learn a lot.

./fa/449/0/

Michel
Previous Topic: Can any one help me in tuning this query (merged)
Next Topic: typical query
Goto Forum:
  


Current Time: Wed Dec 07 20:49:07 CST 2016

Total time taken to generate the page: 0.09239 seconds