CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Popplar
Popplar

Reputation: 279

Return single value when checking table rows values

Im trying to return a single value from a table with a lot of rows if a condition is met. For example, I have a table (ID (pk), CODE (pk), DESCRIPTION) which has a lot of rows. How can I return in a single row if..

SELECT CASE
    WHEN CODE IN ('1', '2') THEN '100'
    WHEN CODE IN ('2', '3') THEN '200'
    WHEN CODE IN ('5', '7') THEN '300'
END AS ASDASD
FROM TABLE
WHERE ID = 1;

The problem is that CODE must check for both and not just one of them. The code as it is will return if for example that ID has got the code '2'.

ASDASD
NULL
'200'

And I want to return just '200' because that ID has got code '2' and '3'.

Upvotes: 1

Views: 1026

Answers (3)

forpas
forpas

Reputation: 164089

You need to check the number returned by a query like this:

SELECT COUNT(DISTINCT CODE) FROM TABLE WHERE ID = 1 AND CODE IN ('1', '2')

If this number is 2 then ID = 1 has both CODE values '1' and '2'. 

SELECT 
  CASE
    WHEN (SELECT COUNT(DISTINCT CODE) FROM TABLE WHERE ID = 1 AND CODE IN ('1', '2')) = 2 THEN '100'  
    WHEN (SELECT COUNT(DISTINCT CODE) FROM TABLE WHERE ID = 1 AND CODE IN ('2', '3')) = 2 THEN '200'
    WHEN (SELECT COUNT(DISTINCT CODE) FROM TABLE WHERE ID = 1 AND CODE IN ('5', '7')) = 2 THEN '300'
  END AS ASDASD
FROM TABLE

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming codes are not duplicated for a particular id:

SELECT ID,
       (CASE WHEN SUM(CASE WHEN CODE IN ('1', '2') THEN 1 ELSE 0 END) = 2
             THEN '100'
             WHEN SUM(CASE WHEN CODE IN ('2', '3') THEN 1 ELSE 0 END) = 2
             THEN '200'
             WHEN SUM(CASE WHEN CODE IN ('5', '7') THEN 1 ELSE 0 END) = 2
             THEN '300'
     END) AS ASDASD
FROM TABLE
WHERE ID = 1
GROUP BY ID;

I added ID to the SELECT, just because this might be useful for multiple ids.

Upvotes: 1

GMB
GMB

Reputation: 222462

You could try and use condition aggregation, as follows :

SELECT CASE
    WHEN MAX(DECODE(code, '1', 1)) = 1 AND MAX(DECODE(code, '2', 1)) = 1 
        THEN '100'
    WHEN MAX(DECODE(code, '2', 1)) = 1 AND MAX(DECODE(code, '3', 1)) = 1 
        THEN '200'
    WHEN MAX(DECODE(code, '5', 1)) = 1 AND MAX(DECODE(code, '7', 1)) = 1 
        THEN '300'
END AS asdasd
FROM TABLE
WHERE ID = 1;

DECODE() is a handy Oracle function that compares an expression (code) to a series of values and returns results accordingly. Basically, condition MAX(DECODE(code, '1', 1)) = 1 ensures that at least one row has code = '1'.

PS : are you really storing numbers as strings ? If code is a number datatype, please remove the single quotes in the above query.

Upvotes: 1

Related Questions