Decode Returning multiple rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Decode Returning multiple rows

  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Decode Returning multiple rows

    Hello,

    I have a business case where in a table I may have a specific value or it will have value as 'ALL'. While I query the table if particular input matches I need that particular record. If its not matching then entry with ALL should be considered and returned.

    Here is the case that you have try at your end to replicate the issues.

    CREATE TABLE lookup_value (
    country VARCHAR2(2) NOT NULL,
    department VARCHAR2(30) NOT NULL,
    problem_code VARCHAR2(30),
    disposition VARCHAR2(30));

    INSERT INTO lookup_value VALUES ('US','SALES','P0044','EXCHANGE')

    INSERT INTO lookup_value VALUES ('US','SALES','ALL','EXCHANGE')

    INSERT INTO lookup_value VALUES ('US','SALES','P0020','ALL')

    INSERT INTO lookup_value VALUES ('US','SALES','ALL','ALL')

    INSERT INTO lookup_value VALUES ('US','OPERATIONS','P0044','ACKNOELEDGE')

    INSERT INTO lookup_value VALUES ('US','OPERATIONS','ALL', 'ALL')


    SELECT *
    FROM lookup_value
    WHERE country = 'US'
    AND department = 'SALES'
    AND problem_code = DECODE (roblem, problem_code, roblem, 'ALL')
    AND disposition = DECODE (:disp, disposition, :disp, 'ALL')

    If I run the above query passing problem_code = P0044 and disposition = 'EXCHANGE', it returns me 3 rows, where as I need only one.

    COUNTRY DEPARTMENT PROBLEM_CODE DISPOSITION
    -----------------------------------------------------------
    US SALES P0044 EXCHANGE
    US SALES ALL EXCHANGE
    US SALES ALL ALL

    Regards
    Tauseef

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,476

    Thumbs down

    Why are you using DECODE?
    Try this:
    Code:
    SELECT *
      FROM Lookup_Value
     WHERE Country = 'US'
       AND Department = 'SALES'
       AND Problem_Code = UPPER ( NVL(:Problem, 'ALL') )
       AND Disposition = UPPER ( NVL(:Disp, 'ALL')  );
    Last edited by LKBrwn_DBA; 08-21-2014 at 10:28 AM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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