DECODE function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: DECODE function

  1. #1
    Join Date
    Oct 2002
    Posts
    1

    Question DECODE function

    Hello,

    is there a way to give the DECODE function a range of values as the second parameter instead of just one value?

    this is what i am doing right now:

    DECODE (patient_age, '1', 'less than 15', '2', 'less than 15')etc....

    I would like to do : if age is between 1 and 15 , then "less than 15'

    Any help, hints would be appreciated!
    Thank you

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    No - you cannot do directly what you are asking.

    However, there are multiple ways around it:

    Code:
    CASE
       WHEN patient_age BETWEEN 1 AND 14 THEN
          'less than 15'
       ELSE
          '15 or over'
    END
    OR

    Code:
    DECODE ( TRUNC(patient_age/15),0,'less than 15', '15 or over')
    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Aug 2001
    Location
    Manchester, UK
    Posts
    86
    Select Patient_age,
    DECODE (sign(15-patient_age),1,'Over 15',-1,'Below 15','15 Years Old')
    from table_name;

    Sign function returns three values 1, if positive, -1 if negative or 0.

    HTH,
    Anurag.
    -- Anurag.
    OCP Application Developer
    ---------------------------------------------------------
    "Be not afraid of growing slowly. Be afraid only of standing still."

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    So how is this better than Chris's sollution?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2001
    Location
    Manchester, UK
    Posts
    86
    as Chris said,
    No - you cannot do directly what you are asking.

    Case can be used only in 9i onwards and in PL/SQL only. Question is for Decode.


    Truncate can do the job but what if I want to know if the age is equal to 15.
    -- Anurag.
    OCP Application Developer
    ---------------------------------------------------------
    "Be not afraid of growing slowly. Be afraid only of standing still."

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by OCPIP
    Truncate can do the job but what if I want to know if the age is equal to 15.
    It can be done by using TRUNC also, no problem:
    Code:
    DECODE (TRUNC(patient_age/15), (patient_age-15)+1, '15 Years Old', 0,'less than 15', '15 or over')
    There is allways more than one way to skin the cat...
    Last edited by jmodic; 10-28-2002 at 06:45 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Aug 2001
    Location
    Manchester, UK
    Posts
    86
    You are a Champ.

    Never did this using trunc.

    There is allways more than one way to skin the cat...
    I will always remember this.
    -- Anurag.
    OCP Application Developer
    ---------------------------------------------------------
    "Be not afraid of growing slowly. Be afraid only of standing still."

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by OCPIP
    as Chris said,
    No - you cannot do directly what you are asking.

    Case can be used only in 9i onwards and in PL/SQL only. Question is for Decode.


    Truncate can do the job but what if I want to know if the age is equal to 15.

    case can be used since 8i, in SQL actually, in PL/SQL you have to put them in dynamic SQL

    in 9i when you case in PL/SQL then itīs more like case in other languages, you can use case as well in SQL

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