CODEWITHSUNDEEP
sqloracle-databasenull
Manual
Manual

Reputation: 1687

how to display a row that is null instead of 0

I am getting a correct result, but now instead of showing me result that is 0 I want them to show me null result . How could I get results null instead of 0 ?

SELECT w.FIRST_NAME,w.LAST_NAME,COUNT(s.SECTION_ID) AS COUNTED_SECTIONS
FROM INSTRUCTOR w LEFT OUTER JOIN SECTION s 
ON w.INSTRUCTOR_ID = s.INSTRUCTOR_ID
GROUP BY w.FIRST_NAME,w.LAST_NAME
ORDER BY w.LAST_NAME;

currently showing

FIRST_NAME                LAST_NAME                 COUNTED_SECTIONS
------------------------- ------------------------- ----------------
Rick                      Chow                                     0 
Marilyn                   Frantzen                                10 
Fernand                   Hanks                                    9 
Charles                   Lowry                                    0
etc

but I want 

FIRST_NAME                LAST_NAME                 COUNTED_SECTIONS
------------------------- ------------------------- ----------------
Rick                      Chow                                      
Marilyn                   Frantzen                                10 
Fernand                   Hanks                                    9 
Charles                   Lowry                                    
etc

I've tried it with NVL and it doesn't work

NVL(COUNT(s.SECTION_ID),NULL) AS COUNTED_SECTIONS

Upvotes: 1

Views: 93

Answers (3)

Nir Alfasi
Nir Alfasi

Reputation: 53525

You can use DECODE in order to decide what you want to display

DECODE(COUNT(s.SECTION_ID),0, NULL, COUNT(s.SECTION_ID)) AS COUNTED_SECTIONS

Upvotes: 1

collusionbdbh
collusionbdbh

Reputation: 701

Try nullif:

SELECT w.FIRST_NAME,w.LAST_NAME, NULLIF(COUNT(s.SECTION_ID), 0) AS COUNTED_SECTIONS
FROM INSTRUCTOR w LEFT OUTER JOIN SECTION s 
ON w.INSTRUCTOR_ID = s.INSTRUCTOR_ID
GROUP BY w.FIRST_NAME,w.LAST_NAME
ORDER BY w.LAST_NAME;

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

I think NULLIF() is available in oracle:

SELECT w.FIRST_NAME,w.LAST_NAME,NULLIF(COUNT(s.SECTION_ID),0) AS COUNTED_SECTIONS
FROM INSTRUCTOR w LEFT OUTER JOIN SECTION s 
ON w.INSTRUCTOR_ID = s.INSTRUCTOR_ID
GROUP BY w.FIRST_NAME,w.LAST_NAME
ORDER BY w.LAST_NAME;

Upvotes: 2

Related Questions