Tag Archives: subquery

Null Values? NOT IN my Subquery! #JoelKallmanDay

Leave a reply

I had to learn from this mistake more than once in my SQL career, until it became a kind of reflex: always think about the NULL values when you are writing a NOT IN subquery. Well, you should ALWAYS keep the NULL values in mind as a SQL developer, but in this case the consequences can be really nasty! And it drives me crazy how many times I’ve seen this error in my consulting life, even in production code. So, what is the point here?

Continue reading →

Using Subqueries and Views with PTF or SQL macros

1 Reply

In the last post about SQL macros in 19c I tried to use a subquery as an input for the function. And when I had some DATE calculations in a query, I got ORA-62558: Unsupported data types (DATE) The result looked somewhat confusing at first, but if you think about it, it is logical and maybe not a bug at all. You should just be very explicit while using subqueries and views(!) in this scenario. And because this behavior is not documented, it is worth sharing, I think.

Continue reading →

Subquery Unnesting – if it doesn’t work

Leave a reply

Actually, the CBO usually does a good job optimizing “NOT IN” subqueries. Unless something goes wrong. Recently it took a long time on a 10.2.0.5 database to search for the reason. At last looking in the CBO trace files (event 10053) has given the right hint.
Continue reading →