[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1866
  • Last Modified:

When Oracle NVL function is used in a query, it is returning different results - how/why?

I have the following query that utilizes NVL function.  When it is used it does not return any results, but if the NVL functions are removed, the query does.  Based on the date being used in the NVL function, this should not change the results set being displayed in in fact the field has a NULL value, so I am wondering why NVL is rendering different results...any thought on NVL and why this could be or any other suggestions?  This is Oracle 10g.

SELECT t0.id_number "Case ID",
       t0.creation_time "Create Date",
       t2.s_title "Case Type",
       t3.s_title "Condition",
       t4.s_last_name "Contact Last Name",
       t5.s_login_name "Owner",
       t6.s_title "Priority",
       t7.s_title "Severity",
       t1.site_id "Site ID",
       t8.s_title "Status"
FROM table_case t0,
     table_site t1,
     table_gbst_elm t2,
     table_condition t3,
     table_contact t4,
     table_user t5,
     table_gbst_elm t6,
     table_gbst_elm t7,
     table_gbst_elm t8
WHERE     (t0.case_reporter2site = t1.objid)
      AND (t0.calltype2gbst_elm = t2.objid)
      AND (t0.case_state2condition = t3.objid)
      AND (t0.case_reporter2contact = t4.objid)
      AND (t0.case_owner2user = t5.objid)
      AND (t0.respprty2gbst_elm = t6.objid)
      AND (t0.respsvrty2gbst_elm = t7.objid)
      AND (t0.casests2gbst_elm = t8.objid)
      AND (    NVL (TO_DATE (t0.creation_time,'MM/DD/YYYY HH:MI:SS AM'),
                    TO_DATE ('01/01/1753 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM')
               ) > TO_DATE ('2007/01/14 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
           AND NVL (TO_DATE (t0.creation_time,
                    TO_DATE ('01/01/1753 12:00:00 AM',
                             'MM/DD/YYYY HH:MI:SS AM'
                    )
              ) < TO_DATE ('2007/01/15 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
);


When NVL is removed, the expected resultset is returned:

SELECT t0.id_number "Case ID",
       t0.creation_time "Create Date",
       t2.s_title "Case Type",
       t3.s_title "Condition",
       t4.s_last_name "Contact Last Name",
       t5.s_login_name "Owner",
       t6.s_title "Priority",
       t7.s_title "Severity",
       t1.site_id "Site ID",
       t8.s_title "Status"
FROM table_case t0,
     table_site t1,
     table_gbst_elm t2,
     table_condition t3,
     table_contact t4,
     table_user t5,
     table_gbst_elm t6,
     table_gbst_elm t7,
     table_gbst_elm t8
WHERE     (t0.case_reporter2site = t1.objid)
      AND (t0.calltype2gbst_elm = t2.objid)
      AND (t0.case_state2condition = t3.objid)
      AND (t0.case_reporter2contact = t4.objid)
      AND (t0.case_owner2user = t5.objid)
      AND (t0.respprty2gbst_elm = t6.objid)
      AND (t0.respsvrty2gbst_elm = t7.objid)
      AND (t0.casests2gbst_elm = t8.objid)
      AND t0.creation_time > TO_DATE ('2007/01/14 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
      AND t0.creation_time < TO_DATE ('2007/01/15 00:00:00', 'YYYY/MM/DD HH24:MI:SS');

The interesting thing is that the creation_time field is NOT NULL, so the NVL is just a safety net/precautionary thing that is in place since the process that is generating the SQL is also generating SQL for other tables/views, so it needs to be there.  For this instance we KNOW that  the column is NOT NULL so the NVL should never come into play...

One thought is that there is some localization happening that is converting the MM/DD into a DD/MM or something, but we are not as familiar with that.

Any help would be greatly appreciated
0
nshilling
Asked:
nshilling
  • 10
  • 9
  • 9
  • +1
3 Solutions
 
sdstuberCommented:
why does the NVL version do a TO_DATE on t0.creation_time  but the non-NVL version does not?

is t0.creation_time a string type or a date/timestamp type?
0
 
johnsoneSenior Oracle DBACommented:
It appears your format mask is missing and parenthesis on the second NVL are in the wrong place.  I would think you should get an error, but try this.

I removed all the references to midnight as that is the default time on a date if one is not specified.
0
 
johnsoneSenior Oracle DBACommented:
OK, it lost my code.  This should be it.
SELECT t0.id_number "Case ID",
       t0.creation_time "Create Date",
       t2.s_title "Case Type",
       t3.s_title "Condition",
       t4.s_last_name "Contact Last Name",
       t5.s_login_name "Owner",
       t6.s_title "Priority",
       t7.s_title "Severity",
       t1.site_id "Site ID",
       t8.s_title "Status"
FROM table_case t0,
     table_site t1,
     table_gbst_elm t2,
     table_condition t3,
     table_contact t4,
     table_user t5,
     table_gbst_elm t6,
     table_gbst_elm t7,
     table_gbst_elm t8
WHERE     (t0.case_reporter2site = t1.objid)
      AND (t0.calltype2gbst_elm = t2.objid)
      AND (t0.case_state2condition = t3.objid)
      AND (t0.case_reporter2contact = t4.objid)
      AND (t0.case_owner2user = t5.objid)
      AND (t0.respprty2gbst_elm = t6.objid)
      AND (t0.respsvrty2gbst_elm = t7.objid)
      AND (t0.casests2gbst_elm = t8.objid)
      AND (    NVL (t0.creation_time,
                    TO_DATE ('01/01/1753','MM/DD/YYYY')
               ) > TO_DATE ('2007/01/14', 'YYYY/MM/DD')
           AND NVL (t0.creation_time,
                    TO_DATE ('01/01/1753',
                             'MM/DD/YYYY'
                    )
              ) < TO_DATE ('2007/01/15', 'YYYY/MM/DD')
);

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
awking00Commented:
Please post the datatype for t0.creation_time.
0
 
awking00Commented:
Also, if you're just trying to get records for 1/14/2009, why not just
... where trunc(t0.creation_time) = to_date('20090114','yyyymmdd')
0
 
nshillingAuthor Commented:
apologies...the NVL version looks like:

SELECT t0.id_number "Case ID",
       t0.creation_time "Create Date",
       t2.s_title "Case Type",
       t3.s_title "Condition",
       t4.s_last_name "Contact Last Name",
       t5.s_login_name "Owner",
       t6.s_title "Priority",
       t7.s_title "Severity",
       t1.site_id "Site ID",
       t8.s_title "Status"
FROM table_case t0,
     table_site t1,
     table_gbst_elm t2,
     table_condition t3,
     table_contact t4,
     table_user t5,
     table_gbst_elm t6,
     table_gbst_elm t7,
     table_gbst_elm t8
WHERE     (t0.case_reporter2site = t1.objid)
      AND (t0.calltype2gbst_elm = t2.objid)
      AND (t0.case_state2condition = t3.objid)
      AND (t0.case_reporter2contact = t4.objid)
      AND (t0.case_owner2user = t5.objid)
      AND (t0.respprty2gbst_elm = t6.objid)
      AND (t0.respsvrty2gbst_elm = t7.objid)
      AND (t0.casests2gbst_elm = t8.objid)
      AND (NVL (t0.creation_time, TO_DATE ('01/01/1753 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM')) > TO_DATE ('2007/01/14 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
           AND NVL (t0.creation_time, TO_DATE ('01/01/1753 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM')) < TO_DATE ('2007/01/15 00:00:00', 'YYYY/MM/DD HH24:MI:SS'))
0
 
nshillingAuthor Commented:
datatype for creation_time is DATE:

 TITLE                                              VARCHAR2(80)
 S_TITLE                                            VARCHAR2(80)
 ID_NUMBER                                          VARCHAR2(255)
 CREATION_TIME                                      DATE

the above is just a subset fo the fields on the table
0
 
awking00Commented:
...
AND NVL(TRUNC(t0.creation_time),TO_DATE('01/01/1753','MM/DD/YYYY') = TO_DATE('20070101','YYYYMMDD')
0
 
awking00Commented:
Needs an extra parenthesis -
...
AND NVL(TRUNC(t0.creation_time),TO_DATE('01/01/1753','MM/DD/YYYY') = TO_DATE('20070101','YYYYMMDD'))
0
 
nshillingAuthor Commented:
in this example, the time used is 00:00:00, but that is not the case for every query...all time values should be considered.  Even so - same query...one uses NVL and one doesn't and we are getting different results.  The one with NVLs does not return data the one without does - how can this be?
0
 
sdstuberCommented:
it makes sense that NVL would return no rows if creation_time is NULL

01/01/1753 is NOT > 2007/01/14    so all NULL rows should disappear from the result set.

if your data is not null then why are you using a "safety net"

furthermore,

AND t0.creation_time > TO_DATE ('2007/01/14 00:00:00', 'YYYY/MM/DD HH24:MI:SS')  

is FALSE for any NULL t0.creation_time  so it has a built in "safety net"  even if you were wrong about the column's contents.
0
 
sdstuberCommented:
but...  given your last NVL version and your original non-NVL version.  They should return the same results in all cases regardless of whether there are any rows with NULL creation_time or not.

if you're getting different results then either your queries aren't quite what you think they are or you're hitting a bug
0
 
sdstuberCommented:
what is your database version?  I know 10g  but what is the full release and patch level ?10.1.??.??  10.2.??.??

there are reported bugs in oracle support related to NVL in 10g.
0
 
nshillingAuthor Commented:
regarding the "safety net"...

during testing we found that a NULL column did not evaluate correctly to specified criteria, however your last statement about how it evaluates to FALSE anyway is making me think we should re-test.

I am not an Oracle expert, so I don't can't speak to specifics on teh testing that was done, as that was years ago and I was not involved...that is what I am being told.

So, if we could put aside WHY NVLs are being used and when, and just examine the two queries (one using NVLs and one without) - why would there be different resutls being generated?
0
 
sdstuberCommented:
sorry, looks like we are cross posting - see my previous two posts. :)
0
 
nshillingAuthor Commented:
i will have to verify, but I believe the version is 10.2.0.3
0
 
awking00Commented:
See attached.
question.txt
0
 
nshillingAuthor Commented:
the null creation_time question was the first one I asked, and it returns 0 rows.  The application (not the database) enforces this value to be populated so in 99% of the situations we are in this column is populated.  I have a feeling this problem is data related (aside from an Oracle bug), and this would be the first check (which I already did).

As a side note, having these NVL functions in place also greatly affect performance.  I am guessing (again, my lack of Oracle expertise) is that it is not using indexes since a function is in place...
0
 
awking00Commented:
Have you tried my suggestion about using trunc(t0.creation_time) = nvl(to_date('20070114','yyyymmdd'), <any other date>)?
0
 
awking00Commented:
I guess I should have said <any other date guaranteed not to be in the data> :-)
0
 
nshillingAuthor Commented:
but, time values are relevant for the query, so correct me if I am wrong, but it looks like using trunc would remove the time, which needs to be considered in the evaluation of the query
0
 
awking00Commented:
>>so correct me if I am wrong, but it looks like using trunc would remove the time<<
That's precisely what it's doing. Rather than say where to_date('20070114 12:15:33','yyyymmdd hh24:mi:ss') is greater than or equal to to_date('20070114 00:00:00','yyyymmdd hh24:mi:ss') and is less than to_date('20070115 00:00:00','yyyymmdd hh24:mi:ss'), using trunc(t0.creation_time) = to_date('20070114','yyyymmdd') will simply find any records that occurred on 2007/01/14 regardless of the time factor, which I think is your intent.
0
 
awking00Commented:
Another way of looking at it. Assume you want to find any value between 2 and 3, you could say where value >= 2 or value <= 3, or you could simply say where trunc(value) = 2, which would include 2.1, 2.83, etc.
0
 
sdstuberCommented:
BUT...

trunc(t0.creation_time) = to_date('20070114','yyyymmdd')

is NOT the same thing as

 t0.creation_time > TO_DATE ('2007/01/14 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
AND t0.creation_time < TO_DATE ('2007/01/15 00:00:00', 'YYYY/MM/DD HH24:MI:SS');

because to_date('20070114','yyyymmdd')  is not included by the AND conditions (  >  vs >= )

plus,  trunc on the column would negate the use of any indexes (unless you specifically created a function based index for it)
but the range based query does allow use of indexes.

0
 
nshillingAuthor Commented:
the time is part of the query - if the user wants to search on something from 3PM on a certain date until 11AM on another, then we must not ignore time.  TRUNC is not an option when the time needs to be considered - correct?
0
 
awking00Commented:
nshilling and sdstuber, forgive me for adding what was apparently unnecessary input to this thread. My whole premise that I stated early on was based on the need to find records that occurred on 2007/01/14 given that the date range in the example was from midnight on that date until midnight the next. If that were the case, then what I offered would have worked (granted it wouldn't have used any existing indexes on the creation_time, but that could have been addressed). Obviously, the statement that the user might want to do a search based on a specific timeframe rather than an entire day makes pursuing this premise moot. I would, however, like to see if there were any difference in the nvl portion using this premise, just for potentially verifying or refuting the nvl issue. :-)
0
 
nshillingAuthor Commented:
Anyone have anything else to add?
0
 
sdstuberCommented:
do you have anything else to ask?
0
 
sdstuberCommented:
The asker hasn't explained what else is needed.
0
 
sdstuberCommented:
I believe

http:#26182951
http:#26182984
http:#26183075

answer the question

26183075 is a little iffy since there was no verification that the asker was, in fact, hitting one of the bugs reported on Oracle's Support site
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 10
  • 9
  • 9
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now