SQLRPGLE with dynamic query

90 pts.
Tags:
SQLRPGLE
I have employee1 table with field student_id,name,marks and table with field name, suppose NEHA is present in name field, I have a table with name NEHA. In name table marks,id and subject field is there. I want to fetch subject from name table(neha) where marks >10. I need to write SQLRPGLE program to fetch name from employee1 by dynamic query as name is changing each time. I have share the code. In this code each time different name is fetched from emoployee1 but the problem in this code is not able to fetch SUBJECT column from NEHA table.In debug its's coming subject='   '. But the same query run perfectly in STRSQL. Could anyone suggest any solution?
D stmt1           s            500A   inz                                   
 D subject         s             10A                                         
 D @subject        s             10A                                         
 D name            s             20A                                         
 D @Name           s             20A                                         
 D STUdent_ID      s             10P 0                                       
 d marks           s             10P 2                                       
  /Free                                                                      
   EXEC Sql                                                                  
    declare c1 scroll cursor  for                                            
    select name,student_id,marks from employee1;                             
                                                                                                                                                      
   EXEC Sql                                                                  
    open c1; 

   EXEC Sql                                                              
     fetch first from c1 into :@name,:student_id,:marks;                  
                                                                      
   dow  sqlcod=0;                                                       
    stmt1='select subject from ' + %Trim(@name)+ ' where marks>10';    
                                                                       
   EXEC Sql                                                     
    declare c2 scroll cursor for                                
    select subject from @name where marks>10;   
        
   EXEC Sql                                                   
    open c2;                                                  
                                                              
   EXEC sql                                                   
    fetch first from c2 into :@SUBJECT;    
     If SQLCOD = 0;                                            
      dsply subject;           
     EndIf; 
                  
                              
   EXEC Sql                    
     Prepare s2 from :stmt1;    
                                          

   EXEC Sql                                                                       
    execute s2;                                                                   
                                                                               
   EXEC SQL                                                                       
     Fetch next from c1 into :name,:student_id,:marks;                             
                                                                               
   EXEC SQL                                                                       
     Fetch next from c2 into :subject;                                                  
                                                                               
   enddo;                                                                        
   EXEC Sql                                                                       
    close c1;                                                                     
   EXEC Sql                                                                       
    close c2;     
                                                                
     *inlr = *on;                                                                  
  /END-FREE                                                                    


Software/Hardware used:
as/400,sqlrpgle
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

With the information you gave us, we would like to know do you have
a business case for asking this question or is it a homework question.

If you have a good business
case for it, please let us know. It is really not right for us to be doing
homework for people. It deprives them of the extra knowledge they get when
doing the research themselves.

Discuss This Question: 5  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.
  • nabamita
    no its business case problem
    90 pointsBadges:
    report
  • Splat
    How are you going to select specific information without a WHERE clause?
    12,845 pointsBadges:
    report
  • Splat
    Whoops. Sorry. I missed the scroll bar at the right.

    Still, I don't see any attempt to select rows containing a character string.
    12,845 pointsBadges:
    report
  • nabamita
    'select subject from ' + %Trim(@name)+ ' where marks>10'; 
    query to select rows.
     
    90 pointsBadges:
    report
  • Splat
    Shouldn't there be an : preceding the %Trim(@name) as in 'select subject from :' + %Trim(@name)+ ' where marks>10'; 
    12,845 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: