• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Oracle query

Experts,

Can someone helpme with this query.

I would like the if statement to check for  FIELDA NE "CC" but I want to include those records where FieldA is equal nulls.
0
morinia
Asked:
morinia
  • 3
  • 2
  • 2
  • +1
1 Solution
 
HainKurtSr. System AnalystCommented:
where NVL(FieldA,'') <> 'CC'
0
 
awking00Commented:
Since Oracle treats an "empty string" as null, you need to change the nvl statement to something like -
where NVL(FIELDA,'XX') != 'CC'
0
 
HainKurtSr. System AnalystCommented:
or you can use something like

where ... and (FIELDA is null or FIELDA != 'CC')
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
awking00Commented:
CREATE TABLE fields
(id number
,fielda varchar2(2));

insert into fields values(1,'CC');
insert into fields values(2,'DD');
insert into fields values(3,NULL);
insert into fields values(4,'CC');
insert into fields values(5,'EE');

SQL> select * from fields
  2  where nvl(fielda,'') != 'CC';

        ID FI
---------- --
         2 DD
         5 EE

SQL> select * from fields
  2  where nvl(fielda,'XX') != 'CC';

        ID FI
---------- --
         2 DD
         3
         5 EE
0
 
HainKurtSr. System AnalystCommented:
with x as (
  select 1 id, 'AA' FIELDA from dual
  union select 2, 'BB' from dual
  union select 3, '' from dual
  union select 4, Null from dual
  union select 5, 'CC' from dual
) select * from x where (FIELDA is null or FIELDA != 'CC')

ID	FIELDA
1	AA
2	BB
3	
4	

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
or you can use something like

where ... and (FIELDA is null or FIELDA != 'CC')

or you may use:
where ... and LNNVL(FIELDA = 'CC')

Open in new window

0
 
PortletPaulCommented:
LNNVL()
is an interesting option, in particular take note it reverses the filter to EQUALS CC

see:

no points please
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Apart from the official docs provided above, this one is quite "funny" to read ;-)

http://blog.moreno-campos.com/2009/09/conditional-function-lnnvl-the-bullsht-detector/

This built-in function becomes very handy in situations described here. Once you get used to the "weird" style ("reverse boolean filter"), you won't miss this thing ;-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now