Re: strange proble with to_date function in select query

From: Bartek <>
Date: Fri, 24 Jul 2009 20:45:58 +0200
Message-ID: <>


On Fri, Jul 24, 2009 at 7:34 PM, Stephane Faroult<> wrote:
> Bartek,
> 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 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.


Received on Fri Jul 24 2009 - 13:45:58 CDT

Original text of this message