CODEWITHSUNDEEP
oracle-database
user1208862
user1208862

Reputation: 303

Case statement should not return null values

I have a case statement, which returns null values in a column whereas I dont want the null values when executing the case statement.

SELECT ABC
(
CASE 
 WHEN 
 condition
 THEN 1
 WHEN
condition2
 THEN 2 
 END
 ) AS column_name FROM tablename;

Column_name returns null values as well

So I changed the query to 

SELECT ABC
(
CASE 
 WHEN 
 condition
 THEN 1
 WHEN
condition2
 THEN 2 
 else 3
 END
 ) AS column_name FROM tablename where column_name <>3;

I get an error 'invalid identifier'.

Any thoughts on this?

Upvotes: 0

Views: 5778

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

You are missing a comma after ABC (assuming that ABC is a column):

SELECT ABC ,                          --<---- comma added here
  (
  CASE 
    WHEN 
      condition
    THEN 1
    WHEN
      condition2
    THEN 2 
    ELSE 3
  END
  ) AS column_name 
FROM tablename;

You can also remove those parentheses, not needed really.

It's not clear why you added that WHERE column_name <> 3. Your adjusted CASE turned those NULL into 3. Do you want to show those rows or not? If yes, keep the query as above. If not, you could use this:

SELECT 
  ABC , 
  CASE 
    WHEN 
      condition
    THEN 1
    WHEN
      condition2
    THEN 2 
    ELSE 3
  END AS column_name 
FROM tablename
WHERE (condition) OR (condition2) ;

or (that's closer to your logic):

SELECT *
FROM
  ( SELECT 
      ABC , 
      CASE 
        WHEN 
          condition
        THEN 1
        WHEN
          condition2
        THEN 2 
        ELSE 3
      END AS column_name 
    FROM tablename
  ) AS tmp
WHERE column_name <> 3 ;

Upvotes: 2

StilesCrisis
StilesCrisis

Reputation: 16310

You can use NVL(something, 0) and if something would have generated a NULL, you'll get 0 instead.

Upvotes: 1

Related Questions