Re: Select clause in the decode syntax

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 21 Dec 2001 19:48:35 -0000
Message-ID: <1008964023.23521.0.nnrp-02.9e984b29@news.demon.co.uk>

Depends on the version of Oracle.
Your initial clause is not valid for decode(), so you probably need to look at the CASE statement (8.1.6 onwards).

For a decode though:

create table t1(n1 number);
create table t2(n1 number);

insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);

insert into t2 values(4);

insert into t2 values(5);
insert into t2 values(6);

 select decode(n1,1,(select max(n1) from t2),n1) from t1

DECODE(N1,1,(SELECTMAX(N1)FROMT2),N1)

---

                                    6
                                    2
                                    3

The (select statement) must be in parentheses or you get error:
ORA-00936: missing expression

The embedded statement must
return zero or one rows or you get error ORA-01427: single-row subquery returns more than one row

One variant of the CASE syntax is:

select

        case when n1 = 1
                then (select max(n1) from t2)
        else n1
        end case

from t1

      CASE

---

         6
         2
         3

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Ravindra Harve wrote in message
<835dd0ad.0112210749.3083a34a_at_posting.google.com>...


>Is it possible to have a select syntax in the decode function ?

>

>Eg: decode(cs_applicant_case_goals.goal_type <> 1421,

>cs_applicant_case_goals.description, select codes.description from

>codes where codes.code = 1421) as description

>

>If the above statement is not possible, is there any other way to get

>a solution ?

>

>Regards

>Ravi

Received on Fri Dec 21 2001 - 13:48:35 CST