Re: No support for CASE keyword??

johnnie (isg cooper) (johnnie_at_prima.netfront.net) wrote: : What is the difference between oracle DECODE and sql-server CASE function?

Oracle's DECODE is a function (for lack of a better word) that is part of the SELECT statement and allows you to substitute one value for another in a result set. For example, if there are 2 values in a column called gender F=Female and M=Male and you want to return the words Female/Male rather than the code F/M, Oracle's DECODE function gives you that capability.

The CASE statement that became available in Sybase 11.5 doesn't appear to be a function that is applied to a column the way DECODE is -- which is similar to using functions like MIN and MAX and SUM. But it looks like it can be used to give similar results. I say "looks like" because I do not have 11.5 yet, so this is based on reading the on-line books. I checked it out when I heard that this was a feature. :-) If you check out at the examples in the book, however, it looks like the CASE statement offers a little more flexibility. In particular, the following example:

3. select stor_id, discount,

    case
    when lowqty is not NULL then lowqty
    else highqty
    end
    from discounts

It's been a long time since I have written an Oracle script and used DECODE, but I don't remember it being able to substitute a value in another column. I remember using it to just substitute literal values.

				Hope this helps
				Teresa Larson

     _________________________________________________________________
    /  Teresa A. Larson                 http://www2.ari.net/jmasino/ /
   /  Group 1 Software                        Voice: (301) 918-0896 /
  /  4200 Parliament Place, Ste 600                tlarson_at_ari.net /
 /  Lanham, MD 20706-1844                    teresa_larson_at_g1.com /
/________________________________________________________________/
                      #include <std_disclaimer>