Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    6

    Unanswered: Multiple conditions on the decode condition

    I would like to say (sudo) decode (middle name NOT NULL AND firstname.length > 1, ..) but I have no idea how to add multiple conditions to the first parameter of a Oracle decode. Any ideas? Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You cascade the DECODE():
    Code:
    DECODE(middle name, NULL, {null condition},  
      DECODE(SIGN(LENGTH(firstname)-1),1,{length >= 1 condition}, {len <= 0 cond}))

    OR, use the CASE statement...(easier).
    Last edited by LKBrwn_DBA; 03-11-13 at 14:59.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2013
    Posts
    6
    Thanks! Let me try that!

  4. #4
    Join Date
    Mar 2013
    Posts
    6
    I am not sure that will accomplish what I am trying to do. If each passes on condition it will generate the output (condition statement). I need it to match both at the same time. Can you do multiple in the same condition field? Or would it be better to do it in a case?

  5. #5
    Join Date
    Mar 2013
    Posts
    6
    Would this work by putting the second decode inside the first one?

    DECODE(p.middlename, NULL, DECODE(SIGN(LENGTH(p.firstname)-1),1,*matches both*))

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by wkolcz View Post
    Would this work by putting the second decode inside the first one?

    DECODE(p.middlename, NULL, DECODE(SIGN(LENGTH(p.firstname)-1),1,*matches both*))
    That is how I coded it, but you are missing the first "no match" if middle name is null:
    Code:
    DECODE(middle name, NULL, *NO MATCH*, DECODE(SIGN(LENGTH(firstname)-1),1,*MATCHES BOTH*, *NO MATCH*))
    Or use the CASE statement:
    Code:
    CASE 
      WHEN middle_name IS NOT NULL
       AND LENGTH(firstname) > 1
      THEN ** MATCH **
      ELSE ** NO MATCH **
    END AS name_test
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DECODE is just fine in simple (kind of) situations, but as soon as you start nesting them, it is difficult to read and (even worse) maintain. Two months after you wrote it, you'll spend some time to figure out what it returns under which circumstances.

    In such cases, I'd suggest use of CASE.

Posting Permissions

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