Skip to Main Content

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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 26 2006
Added on May 26 2006
8 comments
9,758 views