Re: using case statement

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 12 Mar 2008 22:34:08 +0100
Message-ID: <63r0ihF29asjkU2@mid.individual.net>

On 12.03.2008 10:58, Carlos wrote:
> On 12 mar, 08:47, Robert Klemme <shortcut..._at_googlemail.com> wrote:

>> On Mar 12, 4:38 am, Pintu <priyabratabeh..._at_gmail.com> wrote:
>>
>>> Hi Friends,
>> Did we meet before?
>>
>>>  I want to use "in" clause in case statement as shown below.
>> Why?
>>
>>> select * from employee
>>> where empid in
>>>                case i_desgn
>>>                 when 'HR' then (1,2,3,4,5)
>>>                 when 'MD' then 1
>>>                 else   7
>>>                 end
>> As you have discovered this does not work.  What are your conclusions?
>>
>>> Though i did this using if-elsif but there is repetation of query. So
>>> i want this way to implement..
>> What kind of database supports if-elsif in a query?  Certainly not
>> Oracle but this is an Oracle group.  I suggest you take a closer look
>> at boolean logic.
>>
>>> Please help as soon as possible..
>> We always do.  But trying to push the community might prove counter
>> productive.
>>
>> robert

>
> carlos_at_db01.myserver> CREATE TABLE EMPLOYEE (ID_N NUMBER, C_NAME
> VARCHAR2(10));
>
> Tabla creada.
>
> carlos_at_db01.myserver>
> carlos_at_db01.myserver> INSERT INTO EMPLOYEE (ID_N, C_NAME)
> 2 VALUES (1, 'EMP1');
>
> 1 fila creada.
>
> carlos_at_db01.myserver>
> carlos_at_db01.myserver> INSERT INTO EMPLOYEE (ID_N, C_NAME)
> 2 VALUES (2, 'EMP2');
>
> 1 fila creada.
>
> carlos_at_db01.myserver>
> carlos_at_db01.myserver> INSERT INTO EMPLOYEE (ID_N, C_NAME)
> 2 VALUES (3, 'EMP3');
>
> 1 fila creada.
>
> carlos_at_db01.myserver>
> carlos_at_db01.myserver> INSERT INTO EMPLOYEE (ID_N, C_NAME)
> 2 VALUES (4, 'EMP4');
>
> 1 fila creada.
>
> carlos_at_db01.myserver>
> carlos_at_db01.myserver> INSERT INTO EMPLOYEE (ID_N, C_NAME)
> 2 VALUES (5, 'EMP5');
>
> 1 fila creada.
>
> carlos_at_db01.myserver>
> carlos_at_db01.myserver> COMMIT;
>
> Confirmación terminada.
>
> carlos_at_db01.myserver> select *
> 2 from employee
> 3 where ID_N in
> 4 ( select level
> 5 from dual
> 6 connect by level <= decode( '&i_desgn', 'HR', 5,
> 'MD', 1))
> 7 /
> Introduzca un valor para i_desgn: HR
> antiguo 6: connect by level <= decode( '&i_desgn',
> 'HR', 5, 'MD', 1))
> nuevo 6: connect by level <= decode( 'HR', 'HR', 5,
> 'MD', 1))
>
> ID_N C_NAME
> ---------- ----------
> 1 EMP1
> 2 EMP2
> 3 EMP3
> 4 EMP4
> 5 EMP5
>
> carlos_at_db01.myserver> /
> Introduzca un valor para i_desgn: MD
> antiguo 6: connect by level <= decode( '&i_desgn',
> 'HR', 5, 'MD', 1))
> nuevo 6: connect by level <= decode( 'MD', 'HR', 5,
> 'MD', 1))
>
> ID_N C_NAME
> ---------- ----------
> 1 EMP1
>
> carlos_at_db01.myserver>
>
> HTH.
>
> Cheers.
>
> Carlos.

Why do you make it so complicated?

Kind regards

        robert Received on Wed Mar 12 2008 - 16:34:08 CDT