Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2014
    Posts
    3

    Unanswered: Decode retruning 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
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > it returns me 3 rows, where as I need only one.
    which one should be returned?

    should only 1 row always be returned?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2014
    Posts
    3
    Hello,

    While I pass the value to parameter and both the values exists I should get below row - roblem = 'P0044' & :disp = 'EXCHANGE'

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

    If roblem = 'P0010' & :disp = 'EXCHANGE', I expect

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

    If not of the parameter value is present in the table, I expect

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

    Regards
    Tauseef

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one option:

    Code:
    SELECT *
      FROM lookup_value l
     WHERE l.country = 'US' AND l.department = 'SALES'
           AND l.problem_code =
                  CASE
                     WHEN :problem IN
                             (SELECT l1.problem_code
                                FROM lookup_value l1
                               WHERE l1.country = l.country
                                     AND l1.department = l.department)
                     THEN
                        :problem
                     ELSE
                        'ALL'
                  END
           AND l.disposition =
                  CASE
                     WHEN :disp IN
                             (SELECT l1.disposition
                                FROM lookup_value l1
                               WHERE l1.country = l.country
                                     AND l1.department = l.department)
                     THEN
                        :disp
                     ELSE
                        'ALL'
                  END;
    Code:
    SQL> /
    Enter value for problem: 'P0044'
    Enter value for disp: 'EXCHANGE'
    
    CO DEPARTMENT                     PROBLEM_CODE                   DISPOSITION
    -- ------------------------------ ------------------------------ ------------------------------
    US SALES                          P0044                          EXCHANGE
    
    SQL> UNDEFINE PROBLEM
    SQL> UNDEFINE DISP
    SQL> /
    Enter value for problem: 'P0010'
    Enter value for disp: 'EXCHANGE'
    
    CO DEPARTMENT                     PROBLEM_CODE                   DISPOSITION
    -- ------------------------------ ------------------------------ ------------------------------
    US SALES                          ALL                            EXCHANGE
    
    SQL> UNDEFINE PROBLEM
    SQL> UNDEFINE DISP
    SQL> /
    Enter value for problem: 'PX'
    Enter value for disp: 'EX'
    
    CO DEPARTMENT                     PROBLEM_CODE                   DISPOSITION
    -- ------------------------------ ------------------------------ ------------------------------
    US SALES                          ALL                            ALL
    
    SQL>

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    (Not tested)
    Code:
    SELECT country , department , problem_code , disposition
     FROM  (SELECT t.*
             FROM  lookup_value AS t
             WHERE country    = 'US'
              AND  department = 'SALES'
              AND  problem_code IN (:problem , 'ALL')
              AND  disposition  IN (:disp    , 'ALL')
             ORDER BY
                   DECODE(problem_code , 'ALL' , 2 , 1) ASC
                 , DECODE(disposition  , 'ALL' , 2 , 1) ASC
           )
     WHERE rownum = 1
    ;

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I tested it - seems to be OK!

Posting Permissions

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