Re: PL/SQL with Decode problem

"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 Received on Tue May 06 2003 - 01:12:40 CDT