Alternate SQL Row-Selection Criteria

Hey, Ted:

For years I've been building flexible report applications by creating record-selection strings for Open Query File (OPNQRYF) at runtime, based on user input. For example, a user may select purchase order records by vendor number one time, and by a date field the next. Now I am embedding SQL SELECT statements in RPG programs and am finding it difficult to incorporate the same flexibility.

It seems the SQL precompiler wants to know what fields I will be using for record selection. Do I have to code multiple, almost-identical SQL statements?

--Chuck

Coding multiple almost-identical SQL statements is one way to handle this situation, but I don't like it. When you modify one statement, you have to make sure you keep the others in sync. Here are two other methods that I have used.

One method is to use dynamic SQL. This is the more or less the same technique OPNQRYF uses. In the following code fragment, assume that variable SQLCommand has been initialized with a proper SQL SELECT statement. You must first use the PREPARE command to build a prepared statement from the variable. Next you open a cursor (a file pointer) to the statement, fetch the records, and close the cursor.

D SqlCommand      s            512a       
                                          
D zInput          ds                  inz 
 * define the host variables here
                                          
C/exec sql                                
C+  prepare SqlStmt from :SQLCommand      
C+                                        
C/end-exec                                
C/exec sql                                
C+                                        
C+  declare Input cursor for SqlStmt      
C+                                        
C/end-exec                                
C/exec sql                                
C+                                              
C+  open Input                                  
C+                                              
C/end-exec                                      
C                   dow       '1'               
C/exec sql                                      
C+                                              
C+  fetch Input into :zInput                    
C+                                              
C/end-exec                                      
C                   if        SQLStt <> '00000' 
C                   leave                       
C                   endif                       
C                                               
C****** do something with the retrieved row
C                                               
C                   enddo                       
C/exec sql                                      
C+                                        
C+  close Input                           
C+                                        
C/end-exec                                

Using dynamic SQL is widely discouraged, mainly for performance reasons, but in a case where flexibility is necessary, you have to trade off performance. You can't have your cake and eat it, too.

One thing I don't like about using dynamic SQL is that the Display Program References (DSPPGMREF) command does not show that the program uses the queried files. If DSPPGMREF doesn't know that your program uses a certain file, chances are good that your documentation package doesn't, either. This brings me to the second method, which is to force the database to ignore certain portions of the WHERE clause.

To understand this method, first be aware that SQL allows two literals to be compared in the WHERE clause. For example, the following statements are valid.

select * from customer where '1' = '1'
select * from customer where '0' = '1'

The first command selects all of the rows in the table. The second one does not select any rows. I know it looks strange, but you can use this type of logic to enable and disable portions of the WHERE clause. The WHERE clause in the following example allows selection of all rows, selection for rows with a ZIP code within a certain range, or selection by customer group.

D SelectAll       s              1a   inz('0')
D SelectByClass   s              1a   inz('0')
D SelectByZip     s              1a   inz('0')
                                          
D iClass          s              1a       
D iFromZip        s              5a       
D iThruZip        s              5a       
                                          
D zInput          ds                  inz 
D  zCusno                        5p 0     
D  zCusName                     20a       
D  zCusZip                      10a       
D  zCusClass                     3a

C/exec sql                                        
C+                                                
C+  declare Input cursor for                      
C+                                                
C+      SELECT cusno, cusname, cuszip, cusclass   
C+       FROM customers                           
C+      WHERE                                     
C+          (                                     
C+            (:SelectAll = '1')                  
C+            OR                                  
C+            (:SelectByClass = '1' AND           
C+             cusclass = :iClass)                
C+            OR                                  
C+            (:SelectByZip = '1' AND             
C+             substr(cuszip,1,5)                 
C+                BETWEEN :iFromZip AND :iThruZip)
C+          )                                     
C/end-exec
C/exec sql                                        
C+                                                
C+  open Input                                    
C+                                                
C/end-exec                                        
C                   dow       '1'                 
C/exec sql                                        
C+                                                
C+  fetch Input into :zInput                                  
C+                                                
C/end-exec                                        
C                   if        SQLStt <> '00000'   
C                   leave                         
C                   endif                         
C                                                 
C****** do something with the retrieved row
C
C                   enddo
C/exec sql               
C+                       
C+  close Input          
C+                       
C/end-exec

Notice the host variables SelectAll, SelectByClass, and SelectByZip. At runtime, one of these host variables will be given a value of 1, to indicate the type of record selection to be performed. If SelectAll has a value of 1, the first condition of the WHERE clause is satisfied and all records are selected.

WHERE 
 (                                     
  ('1' = '1')                  
OR                                  
  ('0' = '1' AND           
   cusclass = ' ')                
OR                                  
  ('0' = '1' AND             
   substr(cuszip,1,5)                 
      BETWEEN '     ' AND '     ')
)     

If SelectByClass has a value of 1, the second condition is evaluated.

WHERE 
 (                                     
  ('0' = '1')                  
OR                                  
  ('1' = '1' AND           
   cusclass = 'B') 
OR                                  
  ('0' = '1' AND             
   substr(cuszip,1,5)                 
      BETWEEN '     ' AND '     ')
 )     

This is precompiled SQL, but I have a hard time believing that performance could be any better than dynamic SQL. When I look run the Print SQL Information (PRTSQLINFO) command for a program that uses the technique, I see message SQL5065 ("access plan not found"). This makes sense to me. I don't see how the system could create an access plan over such an iffy WHERE clause. But DSPPGMREF shows me that the program uses the queried file.

Let me make a few more comments. First, focus on function, not performance. I don't write queries with performance in mind; I write the code necessary to accomplish a task and only tune a query if performance is not acceptable.

Second, don't forget about the third-party query-product vendors. You may find some of them more appropriate than embedded SQL/400 for dynamic queries.

--Ted

Sponsored By INFORM DECISIONS

ELIMINATE THE COSTS OF PRE-PRINTED FORMS, LABOR AND POSTAGE WITH inFORM Decisions iDocs™ 'Suite' iSeries based e-Forms, e-Checks, e-Mail, e-FAX, and Document Retrieval from the Web are available as individual modules or as a complete e-Document processing 'Suite'. Click to Download the Complete Suite or Individual Modules today www.inFORMDecisions.com or call (800) 858-5544