Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2007
    Location
    Chennai,India
    Posts
    12

    Unanswered: any way to modify this CASE statement

    Hello all,

    Is there any other way(without using CASE) to modify this query?

    SELECT CASE
    WHEN type IN (21,22) THEN 16
    WHEN type IN (37,38) THEN 32
    WHEN type IN (69,70) THEN 64
    WHEN type IN (133,134) THEN 128
    WHEN type IN (261,262) THEN 256
    WHEN type IN (517,518) THEN 512
    WHEN type IN (1029,1030) THEN 1024
    AS xxx,

    Please note all the xxx values are in powers of two.

    Thanks in advance.
    Last edited by O'sambo; 12-14-07 at 05:08.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    What are you trying to do? Your case statement is incomplete (no end).
    You do not have an else statement. Do you only select these types in the where clause or do you display nulls for other types

  3. #3
    Join Date
    Dec 2007
    Location
    Chennai,India
    Posts
    12
    The column 'type' will have only those values(21,22,37,38,69,70,133,134,261,262,517,518,1 029,1030).I have edited the query

    SELECT
    type,
    CASE
    WHEN type IN (21,22) THEN 16
    WHEN type IN (37,38) THEN 32
    WHEN type IN (69,70) THEN 64
    WHEN type IN (133,134) THEN 128
    WHEN type IN (261,262) THEN 256
    WHEN type IN (517,518) THEN 512
    WHEN type IN (1029,1030) THEN 1024
    END AS xxx
    From table_name

    I am just looking for another way to achieve the above result without using CASE

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT type, (type/16)*16 AS xxx FROM table_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    Chennai,India
    Posts
    12
    many thanks to everyone;
    Also SELECT type,type & 2032 AS xxx FROM table_name gives the same result.
    This is how it have been written in my project.The guy who had did this left the company.

    I would be happy if you tell me how he had arrived to the number 2032.

    Thanks in advance.

  6. #6
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    The logic to get the number 2032 is by adding 16+32+64+128+256+512+1024 = 2032

    The added numbers are the values after your THEN statement in your first post

    The logic is quit simple, you need to get the results as 2^4, 2^5, 2^6. 2^7, 2^8, 2^9 and 2^10 based on the condition

    The binary value of 2032 = 0111 1111 0000, which means that the value 1 will be available in all the location that corresponds to 16 (2^4), 32 (2^5), 64(2^6), 128 (2^7), 256 (2^8), 512 (2^9) and 1024 (2^10). Thats why he should have choosen 2032

    Now for example, lets take your 1st condition to check 21,
    binary value of 21 = 0000 0001 0101
    binary value of 2032 = 0111 1111 0000
    ---------------
    21 & 2032 = 0000 0001 0000
    ----------------

    0000 0001 0000 will correspond to the value 16 which is your required result. 1 will be available in only 1 place in the result of the above binary

    I will take another example using another value of yours, let it be 70
    binary value of 70 = 0000 0100 0110
    binary value of 2032 = 0111 1111 0000
    ---------------
    70 & 2032 = 0000 0100 0000
    ---------------

    0000 0100 0000 will correspond to the value 64 which is your required result for the case 70

    note : ^ refers to power

  7. #7
    Join Date
    Dec 2007
    Location
    Chennai,India
    Posts
    12

    Thumbs up

    that is a wonderful explanation ..
    Thank you very much.

Posting Permissions

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