Re: help with decode command: is there a way to include a select statement in the result of a decode function

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 28 May 1998 18:41:17 GMT
Message-ID: <3570afaf.22983939@192.86.155.100>

A copy of this was sent to ramdan_at_mailexcite.com (dan) (if that email address didn't require changing) On Thu, 28 May 1998 17:36:14 GMT, you wrote:

>i want to use the decode command but the result of the search should be a
>select statement
>
>i.e. select decode(payrate, 1, 'text'
> 2, column name
> 3, "a select statement with a where
> clause)
>from labor;
>
>is this possible
>select decode(payrate, 1, 'hi'
> 2, laborcode
> 3, select status from stat where age = 32)
>from labor;
>
>I tried quotes around select etc nothing works any way to do it?
>

You can use pl/sql to do it, for example:

SQL> create or replace function get_dname( p_deptno in number ) return varchar2   2 as
  3 l_dname dept.dname%type;
  4 begin

  5     select dname into l_dname from dept where deptno = p_deptno;
  6     return l_dname;

  7 end;
  8 /
Function created.

SQL> select ename, decode( mod(rownum,2), 0, to_char(deptno),

  2                                       1, get_dname(deptno) ) dept

  3 from emp
  4 /

ENAME DEPT

---------- --------------------
smith      RESEARCH
allen      30
ward       SALES
jones      20
martin     SALES

....

>Dan Ramriez
>
>
>ramdan_at_mailexcite.com
>Development Analyst
>Information Systems
>Commonwealth Edison
>
>The contents of this message express only the sender's opinion. This message does not necessarily reflect the policy or views of my employer, Commonwealth Edison. All responsibility for the statements made in this Usenet posting resides solely and completely with the sender.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu May 28 1998 - 13:41:17 CDT