How to fetch single records using case statements-CodePudding

CREATE TABLE test_table (
    e_id     NUMBER(10),
    e_value  NUMBER(10)
);

INSERT INTO test_table VALUES(11,123);
INSERT INTO test_table VALUES(11,123);
INSERT INTO test_table VALUES(11,null);

COMMIT;

I want to fetch single record from the table test_table if any one of the value is null in the column e_value then it should print No else Yes

My Attempt:

SELECT e_id,
CASE 
WHEN e_value IS NULL THEN 'No'
ELSE 'Yes'
END is_answer
FROM test_table;

Current Output:

 ------ ----------- 
| e_id | is_answer |
 ------ ----------- 
|   11 | Yes       |
|   11 | Yes       |
|   11 | No        |
 ------ -----------

Expected Output:

 ------ ----------- 
| e_id | is_answer |
 ------ ----------- 
|   11 | No        |
 ------ -----------

CodePudding user response：

Maybe try putting it into a CTE and then selecting from that

with tagged_is_answer AS(
SELECT e_id,
CASE 
WHEN e_value IS NULL THEN 'No'
ELSE 'Yes'
END is_answer
FROM test_table;
),
ordered_tagged_is_answer as (
select e_id
,is_answer
,row_number() over(partition by e_id order by is_answer asc) is_answer_order 
from tagged_is_answer
)
select e_id,is_answer from ordered_tagged_is_answer where is_answer_order=1

/*will pick No's before Yes's*/

CTE's are created in-memory so make sure your table is not bigger than your memory.

CodePudding user response：

Order by e_value with the NULLS FIRST and only get the first row:

SELECT e_id,
       CASE WHEN e_value IS NULL THEN 'No' ELSE 'Yes' END is_answer
FROM   test_table
ORDER BY e_value NULLS FIRST
FETCH FIRST ROW ONLY;

Which, for the sample data, outputs:

E_ID IS_ANSWER

11 No

E_ID	IS_ANSWER
11	No

db<>fiddle here

CodePudding user response：

you can use GROUP BY and COUNT:

SELECT e_id,
CASE 
WHEN count(e_id) = count(e_value) THEN 'Yes'
ELSE 'No'
END is_answer
FROM test_table
GROUP BY e_id;

The count will return number of non-null values

db<>fiddle here