Ask the Expert

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   

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: