Solved

Oracle query

Posted on 2014-12-10
8
150 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 51

Expert Comment

by:HainKurt
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 51

Accepted Solution

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

where ... and (FIELDA is null or FIELDA != 'CC')
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 51

Expert Comment

by:HainKurt
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

777 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