SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-00932 while using nvl with trunc function

Kkaviraj-OracleMay 26 2006 — edited May 29 2006

What happens when trunc is executed on null?

I am executing the following query:
"select nvl(trunc(null),sysdate) from dual"
and this throws "ORA-00932: inconsistent datatypes: expected NUMBER got DATE, error at Line:1 Column:23".

Whereas "select nvl(null,sysdate) from dual" returns sysdate correctly.
Also "select trunc(null) from dual" returns null only. So when the returned null is passed through nvl why am I getting exception?

This is happening in ver 9.2.0.5.0 and 10.2.0.2.0

*******************
There is another observation, which is an issue that we have found in our code, and while trying to fix that we saw the earlier observation.

SELECT * FROM orgs, dual where
trunc(SYSDATE) between trunc(orgs.effective_start_date) and nvl(trunc(orgs.effective_end_date),trunc(sysdate))
Here effective start date and effective end date for orgs are null for all records.

When we run this query on ver 9.2.0.5.0, this runs without any exception. But when we run this query on ver 10.2.0.2.0, we get the same exception, "ORA-00932: inconsistent datatypes: expected NUMBER got DATE, error at Line:2 Column:95".

The join with dual is fake, in actual scenario we have join with other tables, but since we are able to replicate with dual, removed all other details to keep this simple.

Now if we remove the join with dual, the query works fine in both the env.
SELECT * FROM orgs where
trunc(SYSDATE) between trunc(orgs.effective_start_date) and nvl(trunc(orgs.effective_end_date),trunc(sysdate))

BluShadow

The first parameter to NVL is determining the expected datatype of the returned column, with the trunc function defaulting that to NUMBER because it's parameter is NULL. The second parameter to NVL needs to match that datatype which it doesn't because it is a date.

SQL> select nvl(trunc(sysdate), sysdate) as mydate from dual;

MYDATE
-------------------
26/05/2006 00:00:00

SQL> select nvl(trunc(null), sysdate) as mydate from dual;
select nvl(trunc(null), sysdate) as mydate from dual
                        *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE


SQL> select nvl(trunc(123), sysdate) as mydate from dual;
select nvl(trunc(123), sysdate) as mydate from dual
                       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

SQL>

William Robertson

You need to make that first parameter a null date:

SQL> SELECT NVL(TO_DATE(TRUNC(NULL)),SYSDATE) FROM dual;

NVL(TO_DATE
-----------
26-MAY-2006

1 row selected.

SQL> SELECT NVL(CAST(TRUNC(NULL) AS DATE),SYSDATE) FROM dual;

NVL(CAST(TR
-----------
26-MAY-2006

1 row selected.

SQL> SELECT NVL(TRUNC(TO_DATE(NULL)),SYSDATE) FROM dual;

NVL(TRUNC(T
-----------
26-MAY-2006

1 row selected.

Kkaviraj-Oracle

Thanks for all the reply. I can understand the datatype defaulting and all the proposed solution works.
I believe this defaulting also works by the datatype of the column getting selected. Like if I have column effective_start_date as DATE type in table orgs. Then the following query runs fine, even if the column have all null values in the table.
select nvl(trunc(effective_start_date),sysdate) from orgs -- no exception

But still this does not explain why the following query is failing only in ver 10.2.0.2.0.
SELECT * FROM orgs, dual where
trunc(SYSDATE) between trunc(effective_start_date) and nvl(trunc(effective_end_date),trunc(sysdate))
Here both the date fields are of DATE type and have null values in the table.

Firstly this should have similar behaviour in other envs, like 9.2.0.5.0. And if we remove dual from above query this runs fine in all env.
SELECT * FROM orgs where
trunc(SYSDATE) between trunc(effective_start_date) and nvl(trunc(effective_end_date),trunc(sysdate))

Laurent Schneider

I see the difference between 10.2.0.2 and 8.1.7.4

SQL> select version from v$instance;
10.2.0.2.0

SQL> select nvl(trunc(null), sysdate) from dual;
select nvl(trunc(null), sysdate) from dual
                        *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

and

SQL> select version from v$instance;
VERSION
-----------------
8.1.7.4.0
SQL> select nvl(trunc(null), sysdate) from dual;
NVL(TRUNC(NULL),SYS
-------------------
26.05.2006 16:55:49

but I cannot reproduce your prob

SQL> create table orgs(effective_start_date date,effective_end_date date);
Table created.

SQL> insert into orgs values(null,null);
1 row created.

SQL> SELECT * FROM orgs, dual where trunc(SYSDATE) between 
trunc(effective_start_date) and nvl(trunc(effective_end_date),trunc(sysdate));
no rows selected

Kkaviraj-Oracle

Thanks for your reply. When you say you are not able to reproduce the error, I hope you are not able to reproduce it in ver 10.2.0.2.0. Just wanted to verify I wasn't clear from the posting.

Anyway we are making the following fix to our code so that it works in all circumstances and in all versions.
"trunc(SYSDATE) between to_date(trunc(orgs.effective_start_date)) and nvl(to_date(trunc(orgs.effective_end_date)), trunc(sysdate))"

Basically we are passing the trunc(dateColumn) through to_date, just to make sure that the datatype becomes DATE always. We have tested this and it works.

Do you guys see any issue with this? Or is there any other better suggestion?

Laurent Schneider

I tried

create table orgs(effective_start_date date,effective_end_date date);
insert into orgs values(null,null);
SELECT * FROM orgs, dual where trunc(SYSDATE) between trunc(effective_start_date) and nvl(trunc(effective_end_date),trunc(sysdate));

in 7.3.4.5, 8.1.7.4, 9.2.0.7, 10.1.0.5, 10.2.0.2 and it is always working. Could you provide a reproducible test case (with create table and insert statements)?

thank you

Kkaviraj-Oracle

Sorry, I had missed out one important information in my earlier postings. orgs was a view and not a table.
Here i am putting the complete step to reproduce this problem:

create table orgs(effective_start_date date,effective_end_date date);
create table orgs1(effective_start_date date);

insert into orgs values(null,null); -- values can be sysdate,sysdate also
insert into orgs1 values(null); -- values can be sysdate also

CREATE OR REPLACE FORCE VIEW orgs_v
as (select * from orgs union select effective_start_date, null effective_end_date from orgs1)

SELECT * FROM orgs_v,dual where
trunc(SYSDATE) between trunc(effective_start_date) and nvl(trunc(effective_end_date),trunc(sysdate))

Laurent Schneider

this sounds like a bug and it is definitely an interesting usage of the dual table :-)

I would rewrite your query as

where  trunc(effective_start_date) <= sysdate and (effective_end_date >= trunc(sysdate) or effective_end_date is null)

Since your bug is easily reproducible, you could report it to metalink to improve service...

Kind regards
Laurent

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 26 2006

Added on May 26 2006

8 comments

9,756 views

SQL & PL/SQL

ORA-00932 while using nvl with trunc function

Comments

Post Details