Reputation: 35
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
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
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
Reputation: 31746
Use something like this.
AND 1 = (
CASE
WHEN field_name = 'Oracle'
AND data IS NULL THEN 0
ELSE 1
END
);
Upvotes: 1
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