Solved

Is NOT Null Oracle PLSQL

Posted on 2014-11-11
12
512 Views
Last Modified: 2014-11-22
I need to show all the values where DVSN_NM is not null.  (Null) shows in the field. I have tried IS NOT NULL and > 0 and < > 0 and nothing seems to work.  Please advise.  Any ideas appreciated.  The column is Characters.


LEFT JOIN(
                SELECT EMP_ID, SSN_ID, DVSN_NM
                FROM dbo.V_PAT_PART
                 WHERE row_exp_d=TO_DATE('01/01/9999','MM/DD/YYYY')
                 AND PLAN_ID IN ('1287')
                 AND DVSN_NM IS NOT NULL
                ) C ON A.EMP_ID=C.EMP_ID AND A.SSN_ID=C.SSN_ID
0
Comment
Question by:leezac
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40436029
Perhaps it does contain '(Null)' did you try:
 . . . 
        AND (DVSN_NM IS NOT NULL
         OR  DVSN_NM = '(Null)' )
   . . .

Open in new window

0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 400 total points
ID: 40436044
You say that "(Null)" shows in the field.  But, if the column actually has a null value, then Oracle will display nothing for that column when you query it, not something like: "(Null)" or "<NULL>", etc.  If you do see a value like that displayed, then your application isn't really doen't store nulls there.  Rather, it stores a value that looks something like "(Null)".

For an Oracle column that is a "character" (that is VARCHAR2, or CHAR, etc.) you should never use a comparison like "> 0" or "<> 0", since that will force Oracle to do a datatype converion on the column values, attempting to convert them all to numbers.  That will cause a performance penalty, plus it will cause an error if any values in that column cannot convert to numbers automatically.  You may safely use a comparison like this: > ' ', that is: the "greater than" symbol, a space, then a single quote, then another space, then another single quote.
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 33 total points
ID: 40436062
Plus, for Oracle an empy string is the same as NULL ...
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 38

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE earned 34 total points
ID: 40436096
Try to 'see' what's in there:

SELECT EMP_ID, SSN_ID, '"' ||  DVSN_NM || '"'
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40436127
And this is simply an Oracle SQL question, not a PL\SQL question.
0
 

Author Comment

by:leezac
ID: 40436140
Double quotes show  
when I ran SELECT EMP_ID, SSN_ID, '"' ||  DVSN_NM || '"'
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 33 total points
ID: 40436475
Double quotes are a bad thing in Oracle and shouldn't be used.

Is DVSN_NM char or varchar2?  CHAR pads spaces when 'empty'.

Try this:
...
AND trim(DVSN_NM) IS NOT NULL
...
0
 

Author Comment

by:leezac
ID: 40437659
Perfect.   Where do  I need to post to get help with Oracle SQL in Experts-Exchange.   I have more questions I need help with.  The AND trim(DVSN_NM) IS NOT NULL
worked great.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40437667
The Oracle Topic Area is probably best.

If your question is related to this, I can probably answer it here.
0
 

Author Comment

by:leezac
ID: 40437991
Quick question please.  How do I create a column in sql to show a column not in table where I show data (this example would be if DVSN_NM = ABC then "Iceland".  I would have 31 DVSN_NM's.  

SELECT PLAN_N,  DVSN_NM,
   SUM(Case when BALANCE>0 THEN Balance Else 0 end) as "Assets", -- Assets
   SUM(Case when BALANCE>0 THEN 1 Else 0 end) as "# Parts w/ Bal" -- Assets
       FROM(
      SELECT A.PLAN_ID, DVSN_NM,  A.AS_OF_D
      FROM dbo.V_BAL_MO A
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40437999
I don't see how that is related to this question.  That should be a asked as a new question.

When doing so, please provide sample data and expected results.  I don't understand what you are trying to do.
0
 

Author Closing Comment

by:leezac
ID: 40459509
Thank you!
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

756 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