Re: using case statement

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Wed, 12 Mar 2008 02:58:30 -0700 (PDT)
Message-ID: <f40d23f2-9091-4868-8aac-320d50a8cac2@c33g2000hsd.googlegroups.com>

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. Received on Wed Mar 12 2008 - 04:58:30 CDT