Working with correlated references
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

    Requires Free Membership to View

    Login

    Register today to access targeted resources from our editorial writers and independent industry experts including news, tips, and advice to help you do your job more efficiently and effectively. Stay informed on the hottest topics and biggest challenges faced by IT professionals working with iSeries products and services.

    By submitting your registration information to Search400.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of Search400.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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