SQL case statement

From: Charlene <charlene_ml_at_yahoo.co.uk>
Date: 10 Mar 2003 03:53:34 -0800
Message-ID: <c6621b22.0303100353.47f6f9c0@posting.google.com>

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 - 05:53:34 CST