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
Solved

Is NOT Null Oracle PLSQL

Posted on 2014-11-11
12
510 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
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)

 
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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
pivot rows to columns 1 34
join actual table rows based on the column 25 21
Toad 12.10 Enterprise visual interface 4 20
Oracle Nested table uses ? 2 30
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

839 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