Re: SQL case statement

From: Charlene <charlene_ml_at_yahoo.co.uk>
Date: 10 Mar 2003 09:19:35 -0800
Message-ID: <c6621b22.0303100919.c5e86fa@posting.google.com>

Solution is so obvious! (or rather, there is another way of doing what I want)
...use a like instead of an equals and then I can pass in the query/bind parameter either as a number, or as a % sign!

Though how I order by org_unit_id is another problem :-(

Charlene

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 like '23'
      CONNECT BY PRIOR org_unit_id = parent_org_unit_fk

  ) end as y from dual
)

charlene_ml_at_yahoo.co.uk (Charlene) wrote in message news:<c6621b22.0303100353.47f6f9c0_at_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 - 11:19:35 CST