Solved

Oracle query

Posted on 2014-12-10
8
154 Views
Last Modified: 2014-12-13
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
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40491803
where NVL(FieldA,'') <> 'CC'
0
 
LVL 32

Expert Comment

by:awking00
ID: 40491937
Since Oracle treats an "empty string" as null, you need to change the nvl statement to something like -
where NVL(FIELDA,'XX') != 'CC'
0
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 40491954
or you can use something like

where ... and (FIELDA is null or FIELDA != 'CC')
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 32

Expert Comment

by:awking00
ID: 40491960
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
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40491998
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40492452
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40493010
LNNVL()
is an interesting option, in particular take note it reverses the filter to EQUALS CC

see:

no points please
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40493423
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question