Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL case statement

Re: SQL case statement

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Mon, 10 Mar 2003 08:13:44 -0800
Message-ID: <3E6CB9B7.8B674BF2@exesolutions.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US