DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DECODE

  1. #1
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41
    I have a column in a table that can contain the value between 25 and 60, 61 and 86, 87 and 112 etc.

    How would I write a DECODE function to return a 1, 2 and 3 depending on the value of this column?

    25 -> 60, return 1
    61 -> 86, return 2
    87 -> 112, return 3

    Thanks in advance, Keith

  2. #2
    Join Date
    Mar 2001
    Posts
    38

    decode

    select 1 from table_name where column_name between 25 and 60
    union
    select 2 from table_name where column_name between 61 and 86
    union
    select 3 from table_name where column_name between 87 and 112
    el472@hotmail.com

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    If you are using 8.1.6.x - Don't! Use a CASE statement instead:

    SELECT
    ---CASE
    ------WHEN Col1 BETWEEN 25 AND 60 THEN 1
    ------WHEN Col1 BETWEEN 61 AND 86 THEN 2
    ------WHEN Col1 BETWEEN 87 AND 112 THEN 3
    ---END
    FROM
    ---TABLE1

    - Chris

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Follow Chris's advice, use CASE if you are on 8.1.6 or higher. If you are not (and I'm also not sure if CASE is already supported inside PL/SQL) then you can use the following:

    SELECT
    --DECODE(1,
    ----SIGN(SIGN(col1-25)+SIGN(60-col1)), 1,
    ----SIGN(SIGN(col1-61)+SIGN(86-col1)), 2,
    ----SIGN(SIGN(col1-87)+SIGN(112-col1)), 3)
    FROM
    --table1;

    As I've mentioned in another thread, hardly anyone could tell what this construct does by looking at it for just a couple of seconds. On the other hand, if you look at the CASE construct for just one second it is more than clear what is the intention of the code. So, if you can choose between DECODE and CASE it should not really be a very tough decision.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width