george_91_at_yahoo.com said...
> "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;
>
declare
v_my_var varchar2(20);
begin
for x in (select emp_code from emp_table) loop
if x.emp_code = 'A' then
v_my_var = 'B';
else
v_my_var = 'C';
end if;
end loop;
end;
--
/Karsten
DBA > retired > DBA
Received on Tue May 06 2003 - 10:47:21 CDT
