Archives of the TeradataForum

Message Posted: Fri, 16 Feb 2001 @ 17:21:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Michael,

The query is working (well, runs with rc=0 !) because it is being treated as a correlated subquery. I think you'll find that if you were to run this one past NCR support you'll get the answer that it's running correctly.

I think the reasoning goes like this: because Teradata supports correlated sub-queries the parser allows this 'incorrect' sql. What's actually happening (as you've spotted in the explain) is that the sub-query is being treated as a two table join between mbtemp1 and mbtemp2. The sub-query contains no join criteria between these tables, hence the product join and because of this product join and the fact that your select list names the column from the outer table (mbtemp1), the NOT IN criteria excludes all rows from the answer set. (If that doesn't make sense, let me know)

Cheers,

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>