Stephane, On Fri, Jul 24, 2009 at 7:34 PM, Stephane Faroult<sfaroult@xxxxxxxxxxxx> 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 -- //www.freelists.org/webpage/oracle-l