?
Solved

Is NOT Null Oracle PLSQL

Posted on 2014-11-11
12
Medium Priority
?
527 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 1600 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 132 total points
ID: 40436062
Plus, for Oracle an empy string is the same as NULL ...
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 38

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE earned 136 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 132 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

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

764 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