[O'sambo]

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.

[pdreyer]

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

[O'sambo]

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

[r937]

SELECT type, (type/16)*16 AS xxx FROM table_name

[O'sambo]

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.

[parangiri]

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

[O'sambo]

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