Re: strange proble with to_date function in select query

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

Thanks,
Bartek

--
http://www.freelists.org/webpage/oracle-l