Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: help with decode command: is there a way to include a select statement in the result of a decode function
I want to stay dynamic want to give this from sqlplus command line is tht
possible didn't work for me
In article <3570afaf.22983939_at_192.86.155.100>, tkyte_at_us.oracle.com wrote:
>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.
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. Received on Thu May 28 1998 - 14:12:24 CDT