using case statement

Discussion in 'ASP .Net' started by pintu, Mar 12, 2008.

  1. pintu

    pintu Guest

    Hi Friends,
    I want to use "in" clause in case statement as shown below.
    e.g
    select * from employee
    where empid in
    case i_desgn
    when 'HR' then (1,2,3,4,5)
    when 'MD' then 1
    else 7
    end

    Though i did this using if-elsif but there is repetation of query. So
    i want this way to implement..Please help as soon as possible..


    Thanks
    Priyabrata
     
    pintu, Mar 12, 2008
    #1
    1. Advertising

  2. Hello pintu,

    u can't use CASE out of SELECT statement. u need to rewrite your query

    ---
    WBR,
    Michael Nemtsev [.NET/C# MVP] :: blog: http://spaces.live.com/laflour

    "The greatest danger for most of us is not that our aim is too high and we
    miss it, but that it is too low and we reach it" (c) Michelangelo


    p> Hi Friends,
    p> I want to use "in" clause in case statement as shown below.
    p> e.g
    p> select * from employee
    p> where empid in
    p> case i_desgn
    p> when 'HR' then (1,2,3,4,5)
    p> when 'MD' then 1
    p> else 7
    p> end
    p> Though i did this using if-elsif but there is repetation of query. So
    p> i want this way to implement..Please help as soon as possible..
    p>
    p> Thanks
    p> Priyabrata
     
    Michael Nemtsev [MVP], Mar 12, 2008
    #2
    1. Advertising

  3. pintu

    bruce barker Guest

    you were close. in sql case is an expression and needs to be used as a
    compare in a where clause:

    select * from employee
    where case
    when i_desgn = 'HR' and empid in (1,2,3,4,5) then 1
    when i_desgn = 'MD' and empid in (1) then 1
    when empid in (7) then 1
    else 0
    end = 1

    -- bruce (sqlwork.com)

    pintu wrote:
    > Hi Friends,
    > I want to use "in" clause in case statement as shown below.
    > e.g
    > select * from employee
    > where empid in
    > case i_desgn
    > when 'HR' then (1,2,3,4,5)
    > when 'MD' then 1
    > else 7
    > end
    >
    > Though i did this using if-elsif but there is repetation of query. So
    > i want this way to implement..Please help as soon as possible..
    >
    >
    > Thanks
    > Priyabrata
     
    bruce barker, Mar 12, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steve Franks
    Replies:
    2
    Views:
    1,257
    Steve Franks
    Jun 10, 2004
  2. Tee
    Replies:
    3
    Views:
    7,817
    Herfried K. Wagner [MVP]
    Jun 23, 2004
  3. exquisitus
    Replies:
    2
    Views:
    452
    Tor Iver Wilhelmsen
    Apr 24, 2005
  4. Gadrin77
    Replies:
    2
    Views:
    673
    Andy Fish
    May 5, 2004
  5. Janice

    lower case to upper case

    Janice, Dec 10, 2004, in forum: C Programming
    Replies:
    17
    Views:
    1,181
    Richard Bos
    Dec 14, 2004
Loading...

Share This Page