If you have a subquery, can it reference a file defined at a higher level without having to redefine that file at the level it is being used?

I researched the topic on the Internet and found information about correlated references, but I?m not sure if I can use this with SQL 400.

Here is sample query. The correlated reference occurs in the second to the last line. The where-cause references file03 -- defined at a higher level in the inner join following the union statement. 

 SELECT  DISTINCT                                                     
  FILE01.CODE01, FILE01.TYPE01, FILE01.ACCT01, FILE01.STAT01,     
  FILE01.DESC01                                                       
 FROM  FILE01                                                         
 WHERE   FILE01.CUST01  = 123 AND                                   
 (FILE01.CODE01 NOT IN 
      (SELECT DISTINCT FILE02.CODE01             
         FROM  FILE02                                               
         INNER  JOIN FILE01 ON                                     
                  FILE01.CODE01 = FILE02.CODE01 AND                             
                  FILE01.ACCT01 = FILE02.ACCT01                                 
         WHERE  FILE02.ORDR01 = 001 AND                           
                  FILE02.SUFF01 = '' AND                                           
                  FILE02.CUST01 = 123 AND                                     
                  FILE02.TYPE02 = 'ADD' AND                                       
                  FILE02.TYPE03 = 'AUTH' and 
            FILE02.CODE02  = 0 ))                                                       
UNION ALL 
 SELECT  DISTINCT                                                     
  FILE01.CODE01, FILE01.TYPE01, FILE01.ACCT01,                     
  FILE01.STAT01, FILE01.DESC01                                       
 FROM  FILE01                                                         
 INNER JOIN FILE03 ON                                           
      FILE03.CODE01 = FILE01.CODE01 AND 
      FILE03.ACCT01 = FILE01.ACCT01 
 WHERE   FILE01.CUST01  = 123 AND                                 
 (FILE01.CODE01 NOT IN 
      (SELECT  DISTINCT FILE02.CODE01               
      FROM  FILE02                                                       
        INNER  JOIN FILE01 ON                                     
                  FILE01.CODE01 = FILE02.CODE01 AND                             
                  FILE01.ACCT01 = FILE02.ACCT01                                             
      WHERE   FILE02.ORDR01 = 001 AND                                     
            FILE02.SUFF01 = '' AND 
            FILE02.CUST01 = 123 AND                 
            FILE02.TYPE02 = 'ADD' AND 
            FILE02.TYPE03 = 'AUTH' and             
            FILE02.CODE02 = FILE03.CODE02))                                     
ORDER BY 1, 2

Yes, that type of correlated reference is supported by DB2 UDB for iSeries.

==================================
MORE INFORMATION ON THIS TOPIC
==================================

Check out this Search400.com Featured Topic: Database issues resolved

Search400.com's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: Tips, tutorials and more.

This was first published in May 2004

Back to top