Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: 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...
    Kishore

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    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
    Kishore

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One stinking little comma!
    Code:
    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

  5. #5
    Join Date
    Mar 2004
    Posts
    84
    Thanks a lot Pat, you are awesome...
    Kishore

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Pat?

    What the hell is that?

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2004
    Posts
    84
    I am migrating the oracle related scripts to SQL Server.:-)
    Kishore

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    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

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by kingno1
    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...
    Last edited by Brett Kaiser; 06-21-04 at 16:45.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How do you do >, <, <>, <=, >=?

    I actually figured out how to do it....

    Anyone?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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