CODEWITHSUNDEEP
oracle-database
Gatorsdog
Gatorsdog

Reputation: 35

Oracle query case statement in the where clause

I have a column field_name and column another name is data If the column name is Oracle and the Data coulmn is Null I don't want that returned in the query.

I tried this and it doesnt work

AND field_name = Case When field_name= 'Oracle' And Data IS Null
 field_name <> 'Oracle'
Else field_name
End

Upvotes: 1

Views: 6125

Answers (4)

MT0
MT0

Reputation: 168806

Your statement is missing a THEN clause and the field_name <> 'Oracle' is invalid:

AND field_name = CASE
                 WHEN field_name = 'Oracle' AND Data IS NULL
                 THEN 'FALSE'
                 ELSE field_name
                 End

Which can be simplified to:

AND ( field_name <> 'Oracle' OR ( Data IS NOT NULL AND field_name IS NOT NULL ) )

Upvotes: 1

Jignesh
Jignesh

Reputation: 88

Here is the query that you can use.

    with tmp as ( 
    select 'Oracle' as field_name , 1 as data from dual union all
    select 'Oracle' as field_name , null as data from dual union all
    select NULL  as field_name , null as data from dual union all
    select 'Test' as field_name , null as data from dual )
    Select * from tmp
    Where 1 = case when field_name = 'Oracle' and data is null 
              then 0 
              else 1
              end

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31746

Use something like this.

AND   1 = (
    CASE
        WHEN field_name = 'Oracle'
             AND   data IS NULL THEN 0
        ELSE 1
    END
);

Upvotes: 1

kfinity
kfinity

Reputation: 9091

You don't want a case statement, just this:

AND NOT (field_name = 'Oracle' AND data is null)

You could do it with a case statement, but it'd be a little weird. You want the first case to always evaluate to false, so you need the part after THEN to be something that cannot be equal to field_name. In this case, I added some letters ("not") to the beginning of the string, which can't be equal to the original string.

AND field_name = Case When field_name= 'Oracle' And Data IS Null
  THEN 'not' || field_name
  Else field_name
  End

Upvotes: 0

Related Questions