CODEWITHSUNDEEP
sqloracle-databaseoracle11g
jot
jot

Reputation: 185

Oracle Case statement to write zero when the query returns null

I have an Oracle query with Union ALL.


 SELECT ROUND(AVG(BATTERYVOLTAGE),2)AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 06:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 06:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT ROUND(AVG(BATTERYVOLTAGE),2)AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 07:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 07:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT ROUND(AVG(BATTERYVOLTAGE),2)AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 08:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 08:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT ROUND(AVG(BATTERYVOLTAGE),2)AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 09:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 09:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT ROUND(AVG(BATTERYVOLTAGE),2)AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 10:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 10:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT ROUND(AVG(BATTERYVOLTAGE),2)AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 11:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 11:05:00', 'dd/mm/yyyy HH24:MI:SS');

When I do this query I get an output like this

AVGVOLTAGE
----------
9.5



12.7
12.7

As you can see that the values of 2,3,4 query is null. Is there any way i can return a zero if the data is not found? I have been following this https://www.oracletutorial.com/oracle-basics/oracle-case/ and found no success. :( . Thank you so much for your time.

Upvotes: 0

Views: 191

Answers (1)

Juwon Kim
Juwon Kim

Reputation: 26

Use decode or nvl in select statement.

Examples:

1.using decode statement

select
decode(BATTERYVOLTAGE,null,'NOTFOUND',BATTERYVOLTAGE)
from 
MYTABLE

2.using nvl statement

select
nvl(BATTERYVOLTAGE,'NOTFOUND')
from
MYTABLE

Please be sure that "data not found" means "NULL"

For more infos, please check this url.

Upvotes: 1

Related Questions