equallent to decode in oracle?

Hi, Here is my oracle statement. How can I change it to be compatible with SQL Server?

update propertytable set visible =decode(propertyid, 1,0, 2,0, 3,1, 5,1, 6,1, 7,0, 9,1, 10,1, 11,0, 14,1, 30,1, 38,1, 60,0, 232,0, 233,0, 415,1, 605,0) where parentid between 2000006001 and 2000006020

Thanks...I'd use the SQL-92 syntax, the [url=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp]CASE]/url] operator.

-PatP|||Thanks Pat,
I have tried it But, have a question on how to handle the UPDATE. Is there anything wrong in the below statement?
UPDATE Propertytable set visible =
CASE
WHEN propertyid =1 THEN 0
WHEN propertyid=2 THEN 0
WHEN propertyid =3 THEN 1
WHEN propertyid =5 THEN 1
WHEN propertyid =6 THEN 1
WHEN propertyid =7 THEN 0
WHEN propertyid =9 THEN 1
WHEN propertyid =10 THEN 1
WHEN propertyid =11 THEN 0
WHEN propertyid =4 THEN 1
WHEN propertyid =30 THEN 1
WHEN propertyid =38 THEN 1
WHEN propertyid =60 THEN 0
WHEN propertyid =232 THEN 0
WHEN propertyid =233 THEN 0
WHEN propertyid =415 THEN 1
WHEN propertyid =605 THEN 0
END,
where parentid between 2000006001 and 2000006020|||One stinking little comma!UPDATE Propertytable
SET visible =
CASE
WHEN propertyid =1 THEN 0
WHEN propertyid=2 THEN 0
WHEN propertyid =3 THEN 1
WHEN propertyid =5 THEN 1
WHEN propertyid =6 THEN 1
WHEN propertyid =7 THEN 0
WHEN propertyid =9 THEN 1
WHEN propertyid =10 THEN 1
WHEN propertyid =11 THEN 0
WHEN propertyid =4 THEN 1
WHEN propertyid =30 THEN 1
WHEN propertyid =38 THEN 1
WHEN propertyid =60 THEN 0
WHEN propertyid =232 THEN 0
WHEN propertyid =233 THEN 0
WHEN propertyid =415 THEN 1
WHEN propertyid =605 THEN 0
END
WHERE parentid between 2000006001 and 2000006020-PatP|||Thanks a lot Pat, you are awesome...|||Pat?

What the hell is that?

UPDATE Propertytable
SET visible =
CASE
WHEN propertyid IN (1,2,11,60,232,233,605) THEN 0
WHEN propertyid IN (3,5,6,9,10,4,30,38,415) THEN 1
END
WHERE parentid between 2000006001 and 2000006020|||I am migrating the oracle related scripts to SQL Server.:-)|||What the hell is that?For somebody comming from an Oracle background, the right way to do it. Actually, listing the elements is preferred syntactically for a lot of reasons, although your solution is a lot easier to type.

-PatP|||I am migrating the oracle related scripts to SQL Server.:-)

I was refering to Pat not using IN...

You do know that if it's not one of those values, you'll get a Null value

EDIT: Did the version after 8i get CASE?

DECODE is sooooooooooo painful...

Tried to write CASE as a udf in Oracle 8i once...gave up...|||Yes, if you don't include an ELSE clause to cover missing values then you'll get a NULL for values that aren't listed. In most cases, that is exactly what I'd like, although kingno1 might or might not like that behavior.

Once you get used to Decode(), it really isn't bad. Oracle users are so accustomed to it that they consider it natural.

Yes, Oracle 9 got a lot of nifty additions that bring it much closer to the SQL-92 standard. There are still a number of behaviors for otherwise standard SQL constructs that Oracle has long supported/encouraged in PL/SQL that will pretty likely keep PL/SQL from ever reaching standards compliance, but it is doing a lot better than it did in the past.

I would have loved to have had the rights to sell bleacher space for folks to watch you trying to code CASE using Oracle 8i. I could have made a fortune!

-PatP|||How do you do >, <, <>, <=, >=?

I actually figured out how to do it...

Anyone?sql