Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Subquery Conundrum
 
From:   David Wellman

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016