A really complex SQL statement (John Earl)

["Scott Feddersen" <Scott.Feddersen@xxxxxxxxxx>]

I am not sure if this is complex enough but we use it get available
locations.  One file contains a list of every valid location, the other
file contains used locations, so the view has unused active locations.

CREATE VIEW MCLIBD/OFV1002                                
   (BINTYP, BINZON, BINAIS,                               
    BINCOL, BINLEV, BINNUM,                               
    BNSTAT)                                               
AS SELECT                                                 
   OFP1000.BINTYP, OFP1000.BINZON, OFP1000.BINAIS,        
   OFP1000.BINCOL, OFP1000.BINLEV, OFP1000.BINNUM,        
   OFP1000.BNSTAT                                         
FROM MCLIBD/OFP1000 LEFT OUTER JOIN MCLIBD/OFP2000 ON     
   (OFP1000.BINZON = OFP2000.BINZON) AND                  
   (OFP1000.BINAIS = OFP2000.BINAIS) AND                  
   (OFP1000.BINCOL = OFP2000.BINCOL) AND                  
   (OFP1000.BINLEV = OFP2000.BINLEV) AND                  
   (OFP1000.BINNUM = OFP2000.BINNUM)                      
WHERE OFP2000.BINZON IS NULL AND OFP1000.BNSTAT = 'Active'

DECLARE CNextAvlLoc CURSOR FOR                      
   SELECT * FROM OFV1002 WHERE                      
      (BINTYP = :BINTYPW2) AND (BINZON = :BINZONW2) 
   ORDER BY                                         
      BINTYP, BINZON ,BINLEV, BINCOL, BINAIS, BINNUM

Scott Feddersen
Buckle, Inc.