Dynamic query statement for SQLRPGLE

655 pts.
Tags:
AS/400
SQLRPGLE
Hi, I would like to build a dynamic query, (File, field and no of fields). User may send one or multiple fields of different data type has to be joined together into to build a query statement. Currently, I prepare a statement , works good for one field.
PREPARE Qry_Stmt FROM :Stmt1;
When I try for multiple fields, it doesn't work , since from and to field count is different. Thanks in advance.
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    This is usually done using concatenation. Can you post more of your code so we can see if the syntax is correct?
    126,110 pointsBadges:
    report
  • carlosdl
    Yes, the problem is probably in the query you are constructing.

    If you provide more details I'm sure you will get great suggestions to solve your problem.
    84,580 pointsBadges:
    report
  • as400dev
    Find find the part of the code. Here I try to get the filename and filed names from DSPF.

    DQryds            DS                  Dim(99999) qualified Inz            
    DQryflds                       120A                                       
     *                                                                        
    D Fields          S             10A                                       
    D Qryflds         S            120A                                       
    D Index           S             10i 0 inz(0)                              
    D Load            S             10i 0 inz(0)                              
    D Cmd             S            100A                                       
    D Stmt1           S            100A                                       
    D Filename        S             10A                                       
    D Squote          S              1    INZ(X'7D')                          
    D SelFld          S              5i 0 inz(0)                              
      Exec sql                                                
                Declare C2 cursor For Qry_Stmt;               
      Exec SQL                                                
                PREPARE Qry_Stmt FROM :Stmt1;                 
      Exec sql                                                
                Open C2;                                      
                                                              
      DoU SQLCOD <> 0;                                        
      Exec Sql                                                
           Fetch C2 into :Qryflds;                            
           If (SQLCOD <> 0);                                  
              Leave;                                          
           EndIf;                                             
           Index += 1;                                        
           Qryds(Index).Qryflds = Qryflds;                    
      Enddo;                                                  
                                                              
      Exec sql                                                
           Close C2;                                          
     /End-free                                                
     *                                                        
    C                   Dow       Load <  Index               
    C                             and Load < 9999             
    C                   Eval      Load += 1                   
    C                   Eval      RRN1 += 1              
    C                   Eval      Rdata = Qryds(Load)    
    C                   Write     REPORTS                
    C                   EndDo                            
    
    655 pointsBadges:
    report
  • as400dev
    Please ignore the previous one.
    DFLDNAME, DFILENAME --> Input from Screen
    DFLDNAME -->FLDCHAR10, FLDDEC20, FLDTIME and so on 		    
    DFILENAME --> FILEA
    
    RDATA --> output of the field values ( FLDCHAR10, FLDDEC20, FLDTIME )
    Field names, File name, No of fields are dynamic 
    ------------------------------------
    DQryds            DS                  Dim(99999) qualified Inz      
    DQryflds                       120A                                 
     *                                                                  
    D Fields          S             10A                                 
    D Qryflds         S            120A                                 
    D Index           S             10i 0 inz(0)                        
    D Load            S             10i 0 inz(0)                        
    D Cmd             S            100A                                 
    D Stmt1           S            100A                                 
    D Filename        S             10A                                 
    D Squote          S              1    INZ(X'7D')                    
    D SelFld          S              5i 0 inz(0)                        
    
    C                   Eval      Stmt1 = 'Select ' + %Trim(DFLDNAME) +     
    C                             ' From ' + DFILENAME                      
                                                                            
     *                                                                      
     // Select ACCTNO From LNMAST                                           
     /Free                                                                  
         Exsr ClrQryFld;                                                    
         Clear Qryds;                                                       
         Index = 0;                                                         
         Load  = 0;                                                         
                                                                            
      //Exec sql                                                            
      //          SET OPTION commit=*none;                                  
      Exec sql                                                              
                Declare C2 cursor For Qry_Stmt;                             
      Exec SQL                                                              
                PREPARE Qry_Stmt FROM :Stmt1;                               
      Exec sql                                                              
                Open C2;                                                    
                                                                            
      DoU SQLCOD <> 0;                                                      
      Exec Sql                                                              
           Fetch C2 into :Qryflds;                                          
           If (SQLCOD <> 0);                                                
               Leave;                                           
            EndIf;                                              
            Index += 1;                                         
            Qryds(Index).Qryflds = Qryflds;                     
       Enddo;                                                   
                                                                
       Exec sql                                                 
            Close C2;                                           
      /End-free                                                 
      *                                                         
     C                   Dow       Load <  Index                
     C                             and Load < 9999              
     C                   Eval      Load += 1                    
     C                   Eval      RRN1 += 1                    
     C                   Eval      Rdata = Qryds(Load)          
     C                   Write     REPORTS                      
     C                   EndDo      
    655 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: