Re: PL/SQL with Decode problem

From: Scott Mattes <Scott_at_TheMattesFamily.ws>
Date: Tue, 06 May 2003 12:41:19 GMT
Message-ID: <P3Ota.12427$Jf.6419235@news1.news.adelphia.net>

What are you trying to do? My guess is that you want to change all records in the emp table based on some join condition with the emp_table and based on a field value change it to A/B/C.

If so, you are going about it wrong, but since I don't have the desc of both tables I can't help with the answer. Decode does not take a select statement for any args (I think), just field names.

"GM" <george_91_at_yahoo.com> wrote in message news:7a9eb40b.0305060359.415d1aed_at_posting.google.com... > "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 - 07:41:19 CDT