Re: PL/SQL with Decode problem

"Guido Konsolke" <Guido.Konsolke_at_triaton.com> wrote in message news:<1052201151.33878_at_news.thyssen.com>...
> "GM" <george_91_at_yahoo.com> schrieb im Newsbeitrag
> news:7a9eb40b.0305051833.1a707fd7_at_posting.google.com...
> > I am getting an error message when I try to assign the value of a
> > decode query into a PL/SQL variable.
> > The error seems to be caused by the decode's col/expression value
> > which is another select statement.
> >
> > The error does NOT occur if I take out the select and just use a
> > column name but I need to use the query.
> > The code is as follows:
> >
> > DECLARE
> >
> > v_my_var varchar2(20);
> >
> > BEGIN
> >
> > select new_emp_code into v_my_var
> > from(
> > select distinct new_emp_code from(
> > select decode((select distinct emp_code from
> > emp_table), 'A', 'B',
> > 'C') new_emp_code
> > from emp;
> >
> > END;
> >
> > Does anybody know how I can get around this problem?
> >
> > Thanks in advance,
> > George
> (snip)
>
> Hi George,
>
> you *need* to use the query? Well, then you're about to wait
> for ORACLE 20z. Maybe that version will allow the use of it ;-)).
>
> More seriously: you select distinct values from a table, let's say
> 5 rows, values 'B', 'O', 'W', 'I', 'E'. Then you compare this 5 values
> to 'A'. How should this work? You can't have a result set in decode.
>
> To get around this problem you have to change your code massively.
>
> Sorry that I couldn't help better,
> Guido

ok I can get rid of the distincts...here is the simplified code...Would there be a solution with this version of the code?

Thanks,
GM

DECLARE  v_my_var varchar2(20);

 BEGIN  select new_emp_code into v_my_var
 from( select decode((select emp_code from  emp_table), 'A', 'B',

               'C') new_emp_code
 from emp;

 END; Received on Tue May 06 2003 - 06:59:18 CDT