Charlene wrote:
> Hi,
>
> I'm failing in getting Oracle to accept a CASE statement that returns
> back multiple rows. Seems a simple concept to me but I guess that CASE
> can only return one row.
>
> I'm trying to do it in pure SQL (PL/SQL is not an option) whereby I do
> a:
> select *
> if a certain condition is true, else I do a recursive select through
> self-referencing tables.
>
> Is there any other way of doing what I want to do?
>
> select staff_id from staff cs
> where
> (nvl(cs.staff_id, 0) between 4000 and 5000) and
> cs.org_unit_fk IN (
> select case when 1 = 0 then (
> SELECT org_unit_id FROM organisation_unit
> ) else (
> SELECT org_unit_id FROM organisation_unit
> START WITH org_unit_id = 23
> CONNECT BY PRIOR org_unit_id = parent_org_unit_fk
> ) end as y from dual
> )
>
> I added some dummy conditions and test data within the statement just
> to get it accepted by Oracle, but failed.
>
> Is it possible to do something similar without resorting to PL/SQL?
>
> Thanks
>
> Charlene
Received on Mon Mar 10 2003 - 10:13:44 CST
