Handling empty or null values In SELECT statement using case

Question

I'm trying to use Case Statement for the following

 SELECT .. to_number(nvl(il.var1,0)) * to_number(nvl(il.var2,0)) * to_number(nvl(il.var3,0))/1000000 AS calculated_value,

Sometimes, either var1 or var2 or var3 will have alphabets inside(dirty data) in which my query will return an error.

How do I structure my query with case statement in which if the result of the equation does not return me valid numeric or if var1 | var2| var3 is not integer, set calculated_value as "0" or "Empty" for that row only?

Dirty data? So why are these columns strings in the first place? With an appropriate column data type there would be no problem at all. — Thorsten Kettner
– Thorsten Kettner, Commented Feb 1, 2018 at 10:05
If you are on Oracle 12.2 or can upgrade to it, you can use the default n on conversion error parameter to to_number. — William Robertson
– William Robertson, Commented Feb 1, 2018 at 10:45

Littlefoot · Accepted Answer · 2018-02-01 09:22:12Z

1

Try to check whether those VARx really are numbers. For example:

from ...
where regexp_like(il.var1, '^\d+$')
  and regexp_like(il.var2, '^\d+$')
  and regexp_like(il.var3, '^\d+$')

[EDIT] Aha, you'd still want to get some result.

Then you'd use something like this: if VARx isn't a number, use "0" (zero) and the final result will be 0.

select case when not regexp_like(il.var1, '^\d+$') then 0
            else il.var1
       end
       *
       case when not regexp_like(il.var2, '^\d+$') then 0
            else il.var2
       end
       as result
from ...

answered Feb 1, 2018 at 9:22

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gene Over a year ago

Sorry, what is the purpose of the *, im getting syntax errors.

Littlefoot Over a year ago

Multiplying, as that's what I saw you doing.

Bibin Mathew · Accepted Answer · 2018-02-01 09:40:02Z

1

  SELECT 
  CASE WHEN ((REGEXP_LIKE (il.var1,'^-?\d+(\.\d+)?$')
              OR  (REGEXP_LIKE (il.var2,'^-?\d+(\.\d+)?$') 
              OR (REGEXP_LIKE (il.var3,'^-?\d+ (\.\d+)?$'))
       THEN 0
  ELSE
     to_number(nvl(il.var1,0)) * to_number(nvl(il.var2,0)) * to_number(nvl(il.var3,0))/1000000 AS calculated_value

edited Feb 1, 2018 at 9:40

answered Feb 1, 2018 at 9:27

Bibin Mathew

4653 silver badges11 bronze badges

2 Comments

Littlefoot Over a year ago

Hm, that should be vice versa, shouldn't it? As you put it, the result will be 0 if VARx are numbers.

Gene Over a year ago

^ Yes, my fields are now all 0

Collectives™ on Stack Overflow

Handling empty or null values In SELECT statement using case

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related