We help IT Professionals succeed at work.

NVL Function not consistently working.

Edward Joell
Edward Joell asked
on
7,909 Views
Last Modified: 2013-12-11
I am using Oracle Version 11G 11.1.0.6.0 64 bit.  We have several views with fields that are returning nulls.  However crystal reports is reading the report of <null> in the data set and printing that into the PDF report.  So I tried to use the NVL function that had worked so well in replacing null values in numeric fields with 0's.  i tried to substitue in empty strings.  However the result was not satisfactory.  About 2/3 of the nulls were still showing up in the result set, whereas about 1/3 had been replaced with a blank.  I tried Coalesce but kept getting data type mismatch errors.  Then I tried using NVL and replacing the Null with a space.  Last night that worked great.  Then today I was given a couple of other views on which to make this change.  And surprise surprise, the NVL funtion had gone back to showing 2/3 of the nulls as nulls.  I tried Coalesce, and this time converted the space literal to an NVARCHAR.  No more datatype mismatch.  However still about 2/3 of the nulls were still being shown as "<null>".  I have no idea why this function is not working as reported in the documentation.  Any help would be appreciated as this project is overdue and I am going to have my head handed to me on  a platter, in an economy with 10% unemployment.
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I don't understand what you're trying to get.

given

NVL(some_value, some_other_value)

what sort of "some_value"  and  "some_other_value" are you working with?

for a given pair, what is the result you are looking for?


what did you do with coalesce differently than nvl?  NVL is just a special case of coalesce that only has 2 options.

that is,  nvl(x,y)  should return the same thing as coalesce(x,y)

Commented:
probably the records that are not captured by the nvl function had a null terminated string instead of null... You can do an update to set them to null then the function will work correctly...

update yourTable
set columnName = NULL
where columnName = ''


or if you dont want to do an update then you should use the NULLIF function.

select NVL(NULLIF(columnName, '', valueIfNull) as someAlias
from yourTable
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
In oracle  '' is null
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I the report you can set an option to treat NULL as default.

FILE --> REPORT OPTIONS

mlmcc
Do not get worried. This problem can be solved easily.
You just have to isolate the problem area and find the solution.
 
Execute the following statement in sql prompt:
SELECT COUNT(*) from <View> WHERE NVL(<fields that is returning null>, 0) IS NULL;
This query should return zero.
Make sure that it does not return any error.

 If is returns zero, then take the base query of the view and try to apply the NVL function on the field which returns NULL.
 If it returns non-zero, then take the base table from the view and query the base table NVL function included, and get its count.

If you still can't figure out the problem, post the query with the NVL function.
CERTIFIED EXPERT
Top Expert 2008

Commented:
NVL returning value has to be the same type as column where NULL valu resides.
So NVL(numeric_type, '0')
and NVL (character_type, 0)
possibly (!!!)
will not work or will work strange.

Author

Commented:
Wow what a response. Thanks everyone for responding so quickly. Let me try to answer the questions.
SDStuber
I am trying to define a view as

Select field1, field2, field3, NVL(field4, ' ')  NameOfField4, NVL(field5, 0) NameOfField5 From buch of joined tables;

field4 is a an nvarchar field5 is a number.  Field5 populates correctly as a 0 for everywhere there is a null.  Field4 does not.  I did not do anything different with coalesce I just did Coalesce(field4, ' ')   Value two was intially going to be an empty string. ('') but when that did not work, I tried it as a space (' ') and that seemed to work at first.  With either value I was getting datatype mismatch when using coalesce until I represented the literals as N'' or N' '.  After which it delivered the same as the NVL function.

Also are yiou saying that the empty string ('') will evaluate as null in Oracle?  but why did 1/3 evaluate properly when we did Select NVL(field4, '')?

tigin44:
Will null terminated strings show up as <null> when viewed in a query results window of SQL Developer and the Oracle EM console's SQL Worksheet? (And for that matter crystal reports?) So according to your example I should be doing:

Select field1, field2, field3, NVL(NULIF(field4, ''), '')  NameOfField4, NVL(field5, 0) NameOfField5 From buch of joined tables;  
I will give that a quick try.

MIMC:
I don't get what you are saying.  Are you saying that in Crystal Reports the default value of the field can be set to Null?  IBut I don't want it to be null I want it to be blank.
Schwertner
Well  if a literal space is evaluated as a char then, that is not strictly the case as the NVL function was able to match an NVarchar field with the char  literal, space.  Whereas Coalesce was not able to handle that until I replaced the char literal with an NChar literal.

All I tried to get an example of the behavior by getting a copy of the query and the results.  And gues what?  Now all the nulls are being properly shown as blank spaces. on the development server, but on the production server it is still an issue despite the SQL being the same in both locations
CREATE OR REPLACE VIEW GISADMIN.EGIS_VW_ROUTES
AS
SELECT DISTINCT
Rt.CYCLEID,
Rt.ORG_CODE,
Rt.NAT_RTE_ID,
Rt.ROUTE_NO,
MAX(NVL(Rt.ROUTE_NAME, ' ')) ROUTE_NAME,
Max(NVL(Rt.ROUTE_DESCRIPTION, ' '))  ROUTE_DESCRIPTION,
Rt.ROUTE_LENGTH_MI,
Sec.F_CLASS,
Max(NVL(Substr(Sec.ASSET_NO,1,8), ' ')) AS ASSET_NO,
MAX(Sec.NUMBER_OF_LANES) AS NUMBER_OF_LANES,
MAX(Lut.SURF_CATEGORY) AS SURF_CAT
 
FROM ROUTES Rt 
	INNER JOIN SECTIONS Sec ON Rt.CYCLEID = Sec.CYCLEID AND Rt.NAT_RTE_ID = Sec.NAT_RTE_ID 
	INNER JOIN LUTSURFACETYPE Lut ON Sec.SURFACE_TYPE = Lut.SURF_TYPE
 
GROUP BY
Rt.CYCLEID,
Rt.ORG_CODE,
Rt.NAT_RTE_ID,
Rt.ROUTE_NO,
--RT.ROUTE_NAME,
--ROUTE_DESCRIPTION,
Rt.ROUTE_LENGTH_MI,
Sec.F_CLASS
--ASSET_Number
 
ORDER BY
Rt.CYCLEID,
Rt.ORG_CODE,
Rt.NAT_RTE_ID,
Rt.ROUTE_NO,
--ROUTE_NAME,
--ROUTE_DESCRIPTION,
Sec.F_CLASS,
NUMBER_OF_LANES,
Rt.ROUTE_LENGTH_MI,
ASSET_NO

Open in new window

OracleScreenShot.jpg
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>Also are yiou saying that the empty string ('') will evaluate as null in Oracle?

yes, easy to verify with simple tests

select 1 from dual where '' is null

select 1 from dual where nvl(null,'') is null


if you were getting different results, either you have bug in oracle's handling of null and you'll need to contact oracle support,  or there was a flaw in your test making it look like your nulls were handled differently.

Author

Commented:
OK well I had already figured that I could not use an empty string as the needed result.  so why does blank work part of the time and not the other part of the time.  And Why behave differently on different servers?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
when you say it "works" or "does not work" what doesn't work about it?

if you do NVL(null, ' ') do you mean you are NOT getting ' ' returned?
where "null" is the literal or a column/variable that is null

if so, then you have a bug in Oracle and need to contact support.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Please try to distinguisch two things:
1. Oracle Server 11g itself
2. Crystal Reports functionality

The sad fact that Crystal displays NULLs somehow
doesn't means automatically that Oracle is involved in the
accident. Possibly there is a possibility to check what returns Oracle,
and how Crystal interprets the entry.

I will recommend to pay attention in the field formatting triggers
of Crystal and to try to reformat the output of the SELECT statement.
Can you post a bigger Screen Shot of results from production server?

The screenshot is too small to figure out the fields and characters.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I am saying that in Crystal there is an option you can set to allow Crystal to treat NULL values as a default value.  I believe the default for strings is '' and the default for numers is 0.

mlmcc

Author

Commented:
OK one at a time
1. sdstuber; when I say say it is not working I mean that NVL(FieldWithNull, ' ') is at times returning <Null> as viewed in Orcale SQL Developer and Jan Borchers SQL Developer and crystal reports and Oracle Enterprise Manager SQL Worksheet.  In about 5 out of the 15 rows in which that field is returning a null processing it with the NVL function continues to return a null.
2. schwertner: Personally I have no view or access to the Crystal Reports view.  What I am seeing is what is returned in SQL worksheet and SQL developer. I would have expected most reporting tools to show a blank upon encountering a null. But I am not a report writer expert.  The only one I have experience with is that in MS Access.
3. suresh; I don't think I can. I took that screen shot from the production server to which I was connect view two step remote desktop connection and pasted directly into paint then uploaded the resulting jpg file.  I will ask around and and see.
4. mlmcc; I figured there was but I didn't want to tell our so-called Crystal Reports expert her job, particularly when I have not experience in using the tool.  I will pass it along.

Author

Commented:
While I was typing the above I got an email.  I then went to the production server to verify.  I am an idiot.  It never occurred to me to check this.  Here is the issue. Somehow someone entered data in to the field of the personal geodatabase from which this data was imported the string value "<Null>" for fields that were null.  (but only some of the rows.)  Needless to say when I used the NVL functions to get rid of those nulls in those fields it had no effect on those rows.  I verified that this was the case by running select Route_Name, Asset_no from egis_vw_routes where asset_no = "<Null>" , and sure enough all those rows where apparently NVL wasn't working showed up in the resultset.  stupid! Stupid! STUPID! This is the first thing I should have suspected.  If it had been SQL Server I would have.  I guess I just let my anti-Oracle bias get in the way of a proper evaluation.  dumb Dumb DUMB!!!  

I am so sorry to have wasted all of your time on a problem that was not a problem.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Since NULL means absence of a value, those fields have a value therefore aren't NULL.  Change them back to NULL or add an additional test for that value.

mlmcc

Author

Commented:
Nope can't change the imported data. Have already inserted case statement into those views.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if there isn't a problem anymore, you can accept your own answer or have the question deleted
or if some of the above was helpful in forcing you to re-evaluate your testing then select those and close
CERTIFIED EXPERT
Top Expert 2008

Commented:
2. schwertner: Personally I have no view or access to the Crystal Reports view.  What I am seeing is what is returned in SQL worksheet and SQL developer. I would have expected most reporting tools to show a blank upon encountering a null. But I am not a report writer expert.  The only one I have experience with is that in MS Access.


Please use only SQL*Plus after installing Oracle client. The tools you are using change the output according their rules and ideas of the authors. They are unreliable ....

Author

Commented:
I am working on the server so I don't
Now why should I be getting this;

SQL> SELECT DISTINCT
  2  Rt.CYCLEID,
  3  Rt.ORG_CODE,
  4  Rt.NAT_RTE_ID,
  5  Rt.ROUTE_NO,
  6  CASE
  7  WHEN Substr(Sec.ASSET_NO,1,8) = '<Null>' THEN ' '
  8  ELSE NVL(Substr(Sec.ASSET_NO,1,8), N' ')
  9  END AS ASSET_NO
 10  FROM ROUTES Rt
 11     INNER JOIN SECTIONS Sec ON Rt.CYCLEID = Sec.CYCLEID AND Rt.NAT_RTE_ID =
Sec.NAT_RTE_ID
 12  /
ELSE NVL(Substr(Sec.ASSET_NO,1,8), N' ')
     *
ERROR at line 8:
ORA-12704: character set mismatch

But when I remove the case

SQL> SELECT DISTINCT
  2  Rt.CYCLEID,
  3  Rt.ORG_CODE,
  4  Rt.NAT_RTE_ID,
  5  Rt.ROUTE_NO,
  6   NVL(Substr(Sec.ASSET_NO,1,8), N' ') AS ASSET_NO
  7  FROM ROUTES Rt
  8     INNER JOIN SECTIONS Sec ON Rt.CYCLEID = Sec.CYCLEID AND Rt.NAT_RTE_ID =
Sec.NAT_RTE_ID
  9 /

I get

   CYCLEID   ORG_CODE NAT_RTE_ID     ROUTE ASSET_NO
---------- ---------- -------------- ----- --------
         3      41630 FWS-ALRI-120   120   10042591
         3      43635 FWS-DAHO-010   010   10045053
         3      42520 FWS-CASA-102   102   10040607
         3      42550 FWS-PEDE-119   119
         3      41526 FWS-SAND-010B  010   10014475
         3      41630 FWS-ALRI-130   130   10042599
         3      42280 FWS-WOCR-100   100   10015689
         3      43581 FWS-PASW-010   010   10018390

3132 rows selected.
CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Sorry it has taken so long to get back to you guys.  I found the answer last night and then spent 4 hours implementing it. By Midnight EST I was in no shape or mood to remain on the computer any more.  
Here is what I found.  As stated by Schewertner and sdstbuer many times, Oracle's GUI tools are less than perfect. Boy are they LESS than perfect.
I knew that there was a problem with the agreement of the datatypes in the various components of the Case statement.  The field is an NVarchar2 so I was attempting to convert the output of the components to NVarchar2 using the edit view  panel of SQL Developer.  Each time I tried to convert the literal space to an Nvarchar using N' ' I got an error of "unexpected Token". I tried other things and kept getting datatype mismatch.  So I took the query to the SQL worksheet on the Enterprise manager console which refused to run the query then to the sql worksheet in both SQL Developers and finally SQLPlus.  However with each unsucessful test I was becoming more frustrated and less complete in testing my alternatives.

So Last night I took a deep breath and went back to the Oracle documentation for the case statement, (making sure I am actually looking at the SQL version not the PL/SQL version.  It explained that all the components of the Case statement must return the same datatype.   so I reduced the query all the way to
CASE
When '11' = '12' then 'Z''
ELSE 'B'
END
Then I increased the complexity.
CASE
WHEN N11' = N'12' THEN N'Z'
ELSE N'B'
End

Then  
CASE
WHEN N11' = N'12' THEN N'Z'
ELSE NVL(Sec.Asset_No, N'B')
End
Finally I discovered when I made all the literals Nvarchar it worked. then I added in the actual fields then the NVL then the substring until I had
SELECT
CASE
WHEN Upper(substr(Sec.ASSET_NO,1,8)) LIKE N'%NULL%' THEN N' '
ELSE NVL(substr(Sec.ASSET_NO,1,8), N' ')
END ASSET_NO,
From Sections sec

I need the upper because in some instances the literal '<Null>' was entered as '<null>' and I also needed wild card because it appeared sometimes that the literal was entered as '(null)'.  So isubstring of an NVarvhar returns an NVChar which must be compared to an NVARCHAR, Which means the Then must be NVARCHAR and the result of the NVL in the else must be an NVARCHAR as well.  (now I know I tried alternative first but because of the Edit panel''s idosyncracies it didn't work there and I failed to try it anywhere else.  So I ended up building the query in Notepad, testing it in Borchers SQL developer and creating the view in SQLPus. for all of the fifty eleven view.  (Could not test query from command line as they returned too many rows to view all the results in the command line window. And I no longer trusted any of Oracles tools.)

I still have not had an opportunity to test all of the suggestiona on why NVL might not work with a particular field. But since this project is on "Hero" hours and I have other work to do that I have been neglecting I will save that for a rainy day.  Oh as as to why the the literal '<Null>' s were in there,; sup;posedly ArcCatalog wrote them as such into Personal Geodatabase after versioning occurred.  (Since the Personal Geodatabase in MS Access, and I know howthat behaves I am having difficulty accepting this "explanation".)

I propose to split the points between myself, Schwertner and SDStuber, Schwertner and SDStuber for hangin in there throught the entire thread and actually coming up with the correct answer to problem although after it was solved and me well for solving the problem.  
I'd say Schertner and SDStuber 50 points each and refund 25 points back to me.  If there is no objection I will implement that tonight.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
no objection,  however I do want to raise one caveat with the limitation of GUI's.  It's not really a gui problem per se, rather a human problem.

Every tool has to solve the problem of what does "null" look like?
Look at the snippet below. Any of the rows null?  yes.
How many of them? 1
Which one? Don't know without more info.

I don't see that as a problem in SQL*Plus, only my own limitation imposed by how I tested.


Personally I prefer Toad because it allows me to color-code null.




SQL> select * from strings;
 
STR
------------------------------------------------------------------
 
 
 
 
 
 
 
 
 
 
 
 
10 rows selected.
 
SQL> select count(distinct str) from strings;
 
COUNT(DISTINCTSTR)
------------------
                 9
 
SQL> select distinct str from strings;
 
STR
------------------------------------------------------------------
 
 
 
 
 
 
 
 
 
 
 
 
10 rows selected.

Open in new window

Author

Commented:
My main complain was with the view pane refusing to accept a perfectly legitimate Oracle funtion (N'dd')  and SQL worksheet in EM console refusing to run a pasted statement that included a crlf.  My main problem with SQLPlus other than the fact I have never liked going back to the command line since Windows 95 came out, is that there have been a number of ongoing issues when attempting to connect to the development server (which you have worked on in a different question.) but also the results for a query of any size cannot all be displayed in the command line window so I don't know what I am getting.  I preferred the old SQL Server Query Analyzer (which has has gone away, replace by something similar to SQL Developer's SQL Worksheet).  Borchers' SQL developer seems pretty good except for its propensity to lose its connection.

I was thinking that suresh hung in there right to the end so I was thinking about giving him the 25 points I was going to refund to myself.  If Anyomne has object to my proposed point distribution please let me know.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Points doesn't have meaning, distribution of points - also.
The main thing is to make important conclussions that will help
you in the future to do the job without entering in stres situation.

GUIs have different tasks:

- OEM (even it has SQL worksheet) is for administration of
  Oracle instances and has so many bugs and strange things
  (especially the answer speed) that I prefer in many cases
  not to use it.
- SQLDeveloper is java product and has so many bugs
- TOAD, PLSQLDeveloper (I do not use PLSQLDeveloper)
  are prety good, but due the complexity TOAD more often
  then desired refussed to service as expected.
-SQL*Plus is primitive CLI, but the complexity is not so big
 and this make it reliable, excluding the format of the output

We have to use every tool we have in the areas where they work reliable.
Understant - annoying, confusing ...
But in every car repaire service you will see how many tools they use ...

Author

Commented:
Found the primary reason for the problem myself and it tunrns out not to be an issue with Oracle but with the data.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.