How to use case statement in my query so that in the case of "NULL" or 0 I will replace them with 1

Question

I have a query and I want to query in the form so that in the case of "null" or 0 I can replace them with 1.

select 
ZEROIFNULL(SUM(CASE WHEN PROCEDURE_TYPE IN (816) and RAT_TYPE_NAME = '2G' THEN (COUNTER_THROUGHPUT_UL) END)) AS COUNTER_THROUGHPUT_UL_2G,
ZEROIFNULL(SUM(CASE WHEN PROCEDURE_TYPE IN (816) and RAT_TYPE_NAME = '3G' THEN (COUNTER_THROUGHPUT_UL) END)) AS COUNTER_THROUGHPUT_UL_3G,
ZEROIFNULL(SUM(CASE WHEN PROCEDURE_TYPE IN (816) and RAT_TYPE_NAME IN ('4G', 'LTE') THEN (COUNTER_THROUGHPUT_UL) END)) AS COUNTER_THROUGHPUT_UL_4G
from Table_name;

In this above query, there might be a possibility that result of all the column will null or zero and I need to replace them with 1. I used zeroifnull to convert all the null into 0 but can't find the way to convert 0 into 1.

I want to see all three columns as 1.

It seems like you know how to use a CASE statement, so how about CASE WHEN x=0 THEN 1? — forpas, Jun 25 '19 at 16:57
I usually use a CTE when I want to do something like this, so that the CASE statements stay readable. Do you know how to write a CTE? — mgrollins, Jun 25 '19 at 16:58

score 0 · Answer 1 · answered Jun 25 '19 at 18:08

You can use COALESCE() in most databases:

select COALESCE(SUM(CASE WHEN PROCEDURE_TYPE IN (816) and RAT_TYPE_NAME = '2G' THEN (COUNTER_THROUGHPUT_UL) END), 1) AS COUNTER_THROUGHPUT_UL_2G,
       COALESCE(SUM(CASE WHEN PROCEDURE_TYPE IN (816) and RAT_TYPE_NAME = '3G' THEN (COUNTER_THROUGHPUT_UL) END), 1) AS COUNTER_THROUGHPUT_UL_3G,
       COALESCE(SUM(CASE WHEN PROCEDURE_TYPE IN (816) and RAT_TYPE_NAME IN ('4G', 'LTE') THEN (COUNTER_THROUGHPUT_UL) END), 1) AS COUNTER_THROUGHPUT_UL_4G
from Table_name;

I would also simplify the query by moving the common conditions to the WHERE clause:

select COALESCE(SUM(CASE WHEN RAT_TYPE_NAME = '2G' THEN COUNTER_THROUGHPUT_UL END), 1) AS COUNTER_THROUGHPUT_UL_2G,
       COALESCE(SUM(CASE WHEN RAT_TYPE_NAME = '3G' THEN COUNTER_THROUGHPUT_UL END), 1) AS COUNTER_THROUGHPUT_UL_3G,
       COALESCE(SUM(CASE WHEN RAT_TYPE_NAME IN ('4G', 'LTE') THEN COUNTER_THROUGHPUT_UL END), 1) AS COUNTER_THROUGHPUT_UL_4G
from Table_name
where PROCEDURE_TYPE IN (816);

How to use case statement in my query so that in the case of "NULL" or 0 I will replace them with 1

1 Answers1