SQL and Data Selection/Filtering

Hi, 

I have an inquiry/maintenance program with a 'position to' field for
data selection/filtering.

Within the program, I am building an SQL command string using the
'position to' value.

 

Sample: 

 

Select recust#, reparent, cmcust, cmname From datalib.refile inner join
datalib.cmfile on recust# = cmcust Where cmname like 'A%' Order by
cmname

 

Note: the A in 'A%' is coming from the position to field.  This works
fine in the program but when I leave the position to field blank (no
filter, show all),

I get nothing back.

 

Sample:

 

Select recust#, reparent, cmcust, cmname From datalib.refile inner join
datalib.cmfile on recust# = cmcust Where cmname like '%' Order by cmname

 

I have checked the syntax of the built command string and have captured
the string thru debug and tried them manually and it works but not
within the RPG program.

 

I have also tried it with no Where selection and get the same result.

 

I am new to sql and probably have something hosed up royally so any help
will be greatly appreciated.

 

 

d SqlResult       ds                      

d  recust#                1      9s 0     

d  reparent              10     18s 0     

d  reina                 19     19a       

d  relchdt               20     45z       

d  relchby               46     55a       

d  readddt               56     81z       

d  readdby               82     91a       

d  reorig                92     92a       

d  retype                93     93a       

d  rehlevel              94     95s 0     

d  reancest              96    104s 0     

d  cmcust               105    111a       

d  cmname               112    141a       

 

Note: field recust# is 9.0 numeric and cmcust is 7. alpha in their
respective files, still the manual and 

internal executions work fine if a position to value is specified.

 

d Sql             s           1000a                       

d*                                                        

d Select          c                   const('Select -     

d                                     recust#, -          

d                                     reparent, -         

d                                     reina, -            

d                                     relchdt, -          

d                                     relchby, -          

d                                     readddt, -          

d                                     readdby, -          

d                                     reorig, -           

d                                     retype, -           

d                                     rehlevel, -         

d                                     reancest, -         

d                                     cmcust, -           

d                                     cmname')            

d*                                                        

d From            c                   const('From datalib.refile -    

d                                     inner join datalib.cmfile')      

d*                                                                     

d Where1          c                   const('on recust# = cmcust')     

d Where2          c                   const('on reparent = cmcust')    

d Where3          c                   const('Where cmname like')       

d Where4          c                   const('Where cmname >=')         

d Where5          c                   const('Where retype =')          

d Where6          c                   const('Where cmname not like')   

d*                                                                     

d OrderBy         c                   const('Order by cmname')         

d*                                                                     

d rQuote          c                   const(X'7D')                     

d comma           c                   const(',')                       

d pcent           c                   const('%')                       

d Enter           c                   const(X'F1')                     

d Spacer          c                   const(X'40')                     

 

d iwposi          s             30a                (screen input field)

 

d FetchNxt        pr      

d CloseCur        pr


 

/free

 

Sql = *blank;

 

Sql = %trim(sql) + Select;

 

Sql = %trim(sql) + ' ' + From;

 

Sql = %trim(sql) + ' ' + Where1;

 

Sql = %trim(sql) + ' ' + Where3 + rQuote + %trim(iwposi);

 

Sql = %trim(sql) + pcent + rQuote;

 

Sql = %trim(sql) + ' ' + OrderBy;

 

/end-free

c                                                                       

c/Exec Sql                                                              

c+ set option naming = *sql , datfmt = *iso                             

c/End-Exec                                                              

c                                                                       

c/Exec Sql                                                              

c+ Prepare S1 From :Sql                                                 

c/End-Exec                                                              

c                                                                       

c/Exec Sql                                                              

c+ Declare C1 Cursor For S1                                             

c/End-Exec                                                              

c                                                                       

c/Exec Sql                                                              

c+ Open C1                                                              

c/End-Exec                                                              

c                                                                       

 /free                                                                  

 

   dow 1 = 1; 

 


                                                                     

   FetchNxt();                                                       

 


       if sqlstate <> '00000';


         CloseCur();


         leave;


       endif;


 


 

 

 

 

 

  **********************************************************************


  * Read From Sql Command


  **********************************************************************


 pFetchNxt         b


 dFetchNxt         pi


 c/Exec Sql


 c+ Fetch Next From C1 Into :SqlResult


 c/End-Exec


 pFetchNxt         e


 


  **********************************************************************


  * Close Sql File


  **********************************************************************


 pCloseCur         b


 dCloseCur         pi


 c/Exec Sql


 c+ Close C1


 c/End-Exec


 pCloseCur         e


 


 

 

 

 

All men should believe in something..... I believe I'll have another
beer.