Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: USING CASE in the WHERE Clause of a select

    Hi,

    I SQL Server you are allowed to use a CASE structure in the WHERE or ORDER BY clause of a SELECT. How can I simulate this in ORACLE(8.1.7)

    In SQL I could have used:
    select...
    from..
    where..
    order by CASE WHEN @p=1 then Name When @p=2 then Desc End

    How should I do this in ORACLE?

    Thank you,
    Florian

  2. #2
    Join Date
    Sep 2003
    Posts
    8

    Re: USING CASE in the WHERE Clause of a select

    Originally posted by mar765
    Hi,

    I SQL Server you are allowed to use a CASE structure in the WHERE or ORDER BY clause of a SELECT. How can I simulate this in ORACLE(8.1.7)

    In SQL I could have used:
    select...
    from..
    where..
    order by CASE WHEN @p=1 then Name When @p=2 then Desc End

    How should I do this in ORACLE?

    Thank you,
    Florian
    ----------------------------
    In Oracle,U can also use CASE like the following:

    select...
    from..
    where..
    order by CASE WHEN p=1 then Name ELSE p=2 then Desc End

  3. #3
    Join Date
    Sep 2003
    Posts
    4

    Re: USING CASE in the WHERE Clause of a select

    Hi,

    Thanks for the reply

    I haven't test it for the order by, but I have tried to make it work in the wheer clause and it doesn't seem to work.

    Any idea how I could simulate the case for the where clause?(version 8.1.7)

    Thanks,
    Florian

    Originally posted by xujb
    ----------------------------
    In Oracle,U can also use CASE like the following:

    select...
    from..
    where..
    order by CASE WHEN p=1 then Name ELSE p=2 then Desc End

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    case does work in the where clause just remeber it returns a value

    i.e.

    select * from x
    where y = (case when z=2 then 1 else 3 end)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •