asked on

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

Sean Stuber

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?

johnsone

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.

johnsone

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

awking00

Please post the datatype for t0.creation_time.

awking00

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

nshilling

ASKER

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

nshilling

ASKER

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

awking00

...
AND NVL(TRUNC(t0.creation_time),TO_DATE('01/01/1753','MM/DD/YYYY') = TO_DATE('20070101','YYYYMMDD')

awking00

Needs an extra parenthesis -
...
AND NVL(TRUNC(t0.creation_time),TO_DATE('01/01/1753','MM/DD/YYYY') = TO_DATE('20070101','YYYYMMDD'))

nshilling

ASKER

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?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nshilling

ASKER

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?

Sean Stuber

sorry, looks like we are cross posting - see my previous two posts. :)

nshilling

ASKER

i will have to verify, but I believe the version is 10.2.0.3

awking00

See attached.
question.txt

nshilling

ASKER

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

awking00

Have you tried my suggestion about using trunc(t0.creation_time) = nvl(to_date('20070114','yyyymmdd'), <any other date>)?

awking00

I guess I should have said <any other date guaranteed not to be in the data> :-)

nshilling

ASKER

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

awking00

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

awking00

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.

Sean Stuber

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.

nshilling

ASKER

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?

awking00

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. :-)

nshilling

ASKER

Anyone have anything else to add?

Sean Stuber

do you have anything else to ask?

Sean Stuber

The asker hasn't explained what else is needed.

Sean Stuber

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