CODEWITHSUNDEEP
mysqlsqlsql-server
Carlos80
Carlos80

Reputation: 433

SQL Exclude Zero from Case Column

I have a query inserting values into a table and am using CASE to determine the value for each row. I was wondering if it is possible at the end of it to then exclude null or zero from the calculated column?

   CASE
       WHEN Value = 0 THEN Value2
       WHEN Value2 < Value1 THEN Value3
       WHEN Value2 > Value1 THEN Value4
   END AS Final_Value

This produces a few hundred thousand rows, of which a good few thousand are now zero or null. Can I exclude these in the same piece of code?

Thanks

Upvotes: 1

Views: 4707

Answers (5)

LukStorms
LukStorms

Reputation: 29667

Review the CASE WHEN so it won't result in 0 or NULL

For example:

CASE
WHEN Value = 0 THEN Value2
WHEN Value2 <= Value1 THEN Value3
WHEN Value2 > Value1 THEN Value4
ELSE Value2
END AS Final_Value

Of course, this one assumes that Value2, Value3 and Value4 are NOT NULL and not 0.

Upvotes: 0

Andre Duarte
Andre Duarte

Reputation: 61

If it's not possible to filter in the where clause, so there is a simple solution i use in some cases, with a subselect.

Example:

select X.* from (
SELECT 
     ACCGRPID, 
     LOCID, 
     USERID4, 
     LOBNAME, 
     REINSTYPE, 
     INCEPTDATE, 
     EXPIREDATE, 
     Latitude, 
     Longitude, 
     CountryRMSCode, 
     StreetAddress, 
     CityName, 
     ACCGRPNUM, 
     ACCGRPNAME,
     CASE
        WHEN Value = 0 THEN Value2
        WHEN Value2 < Value1 THEN Value3
        WHEN Value2 > Value1 THEN Value4
     END AS Final_Value
    from <table_name>) X
where X.Final_Value != 0 and X.Final_Value is not null

Upvotes: 0

Element Zero
Element Zero

Reputation: 1751

   ;WITH CTE AS (
       select
          ACCGRPID, 
          LOCID, 
          USERID4, 
          LOBNAME, 
          REINSTYPE, 
          INCEPTDATE, 
          EXPIREDATE, 
          Latitude, 
          Longitude, 
          CountryRMSCode, 
          StreetAddress, 
          CityName, 
          ACCGRPNUM, 
          ACCGRPNAME,    
          CASE
                 WHEN Value = 0 THEN Value2
                WHEN Value2 < Value1 THEN Value3
                 WHEN Value2 > Value1 THEN Value4
          END AS Final_Value
   )
   select * from CTE
   where  Final_Value <> 0 
          and Final_Value is not null

Upvotes: 0

Siraj ul Haq
Siraj ul Haq

Reputation: 885

0 or null values can be avoided in the end of query like 

SELECT  
ACCGRPID, 
LOCID, 
USERID4, 
LOBNAME, 
REINSTYPE, 
INCEPTDATE, 
EXPIREDATE, 
Latitude, 
Longitude, 
CountryRMSCode, 
StreetAddress, 
CityName, 
ACCGRPNUM, 
ACCGRPNAME,    
CASE
   WHEN Value = 0 THEN Value2
   WHEN Value2 < Value1 THEN Value3
   WHEN Value2 > Value1 THEN Value4
END AS Final_Value
FROM  <table_name>
WHERE Final_Value <> 0

Upvotes: 0

SqlZim
SqlZim

Reputation: 38033

Use the where clause:

select 
   ...
 , case 
  when value = 0
   then value2
  when value2 < value1
   then value3
  when value2 > value1
   then value4
  end as final_value
from  ...
where case
       when value = 0 then value2
       when value2 < value1 then value3
       when value2 > value1 then value4
   end <> 0

Upvotes: 1

Related Questions