Re: strange proble with to_date function in select query
Date: Fri, 24 Jul 2009 20:45:58 +0200
On Fri, Jul 24, 2009 at 7:34 PM, Stephane Faroult<sfaroult_at_roughsea.com> wrote:
> 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
I believe this is the case. I just was not sure how to write the query
to stop Oracle from doing it.
Indeed the YYYYMMDD is used instead of null values.
> Root cause of the problem is, obviously, a bad design - perhaps this is
> a staging table inherited from another source?
I agree with you completely. At the same time I did not do the design and I could not easily change it.
> 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
Thank you for the hint. I will give it a try.
> If you can, the best solution is by far to redesign the table so as to
> use real Oracle dates.
:-). This I know, unfortunately the redesign might not be an option in this case.