Decode Returning multiple rows

[tauseefcafe]

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

[LKBrwn_DBA]

Why are you using DECODE?
Try this:

SELECT *
  FROM Lookup_Value
 WHERE Country = 'US'
   AND Department = 'SALES'
   AND Problem_Code = UPPER ( NVL(:Problem, 'ALL') )
   AND Disposition = UPPER ( NVL(:Disp, 'ALL')  );