Re: strange proble with to_date function in select query
Date: Fri, 24 Jul 2009 19:34:19 +0200
I presume that you have a query rewrite occurring that changes the order in which the various conditions are evaluated. In the example that fails, it is likely that the subquery is unnested, Oracle evaluates values that you don't want (formats) BEFORE they are filtered out because the optimizer believes that's the smart thing to do and you're toast. I suspect that histograms may play a part here, if you are running Oracle 10 and they are automatically computed. I also suspect that SUBMITDATE is not null, possibly YYYYMMDD is what is used when you have no known value, etc.
Root cause of the problem is, obviously, a bad design - perhaps this is a staging table inherited from another source? What you want is the inner query to *stay*nested. Although I don't like hints, the NO_UNNEST hint is a possibility; another solution is adding
and rownum > 0
inside the subquery, which should have the same effect but may be less explicit for people who will maintain the query.
If you can, the best solution is by far to redesign the table so as to use real Oracle dates.
HTH S Faroult
> I have a strange problem with to_date function when I use it in select query.
> The following query does not give any ORA-nnnnn errors:
> [snipped to avoid over quoting]
> gives expected result without any ORA- errors.
> Could somebody give me any hint how can I solve this problem?
> Thanks in advance,