Solved

Is NOT Null Oracle PLSQL

Posted on 2014-11-11
12
505 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen 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 76

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 76

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 76

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql ide 10 39
MS SQL 2008 Divide by zero error encountered. Error 3 33
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 30
TSQL query to generate xml 4 31
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…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now