Solved

Is NOT Null Oracle PLSQL

Posted on 2014-11-11
12
516 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
[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
  • 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 70

Assisted Solution

by:Qlemo
Qlemo earned 33 total points
ID: 40436062
Plus, for Oracle an empy string is the same as NULL ...
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

734 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