midrange.com code scratchpad
Name:
Building a dynamic sql statement
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
07/29/2011 03:56:20 pm
IP:
Logged
Description:
Samples of building a dynamic sql statement with lots of possible conditions.
Code:
  1. This builds the sql statement and opens the cursor.
  2.  
  3. D #Rebuild        Pi                                                       
  4. c                   Eval      @sql = 'select * from mins i'                
  5. c                   If        @where <> *blanks                            
  6. c                   Eval      @sql = %trim(@sql) + ' where ' +%trim(@where)
  7. c                   Endif                                                  
  8. c                   Eval      @sql = %trim(@sql) + ' order by ' + @orderby 
  9. c                             + ', ininnm for read only'                   
  10. c                   Callp     #close                                       
  11. c                   Callp     #prepare                                     
  12. c                   Callp     #declare                                     
  13. c                   Callp     #open                                        
  14.  * Force the subfile to clear                                              
  15. c                   Eval      Rr1 = 9801                                   
  16. c                   Callp     #bld@1                                       
  17. P #Rebuild        e                                                        
  18.  
  19. This is an example of how I build up the where portion of the statement - the @c fields are the screen fields.
  20.  
  21. c                   If        @cfaxp <> *blanks                 
  22. c                   Eval      @where = %trim(@where) + #padand  
  23. c                             + %trim(#fmtsqln(@cfaxp:'infaxp'))
  24. c                   Endif                                       
  25.  *---                                                           
  26. c                   If        @cfaxc <> *blanks                 
  27. c                   Eval      @where = %trim(@where) + #padand  
  28. c                             + %trim(#fmtsqlc(@cfaxc:'infaxc'))
  29. c                   Endif                                       
  30.  *---                                                           
  31. c                   If        @cwurl <> *blanks                 
  32. c                   Eval      @where = %trim(@where) + #padand  
  33. c                             + %trim(#fmtsqll(@cwurl:'inwurl'))
  34.  
  35. These are some of the helper functions you see above to format stuff.
  36.  ********************************************************************
  37.  *  Procedure #Padand - add ands to the @where string where needed   
  38.  ********************************************************************
  39. P #Padand         B                                                  
  40. D #Padand         Pi             5                                   
  41. c                   If        @where <> *blanks                      
  42. c                   Return    ' and '                                
  43. c                   Endif                                            
  44. c                   Return    ''                                     
  45. P #Padand         e                                                  
  46.  ********************************************************************
  47.  *  Procedure #Fmtsqlc - format character variables for where stmt   
  48.  ********************************************************************
  49. P #Fmtsqlc        B                                                  
  50. D #fmtsqlc        Pi           256a                                  
  51. D  @inpsql                     256a   Const                          
  52. D  @varname                     12a   Const                          
  53.                                                                      
  54. D  @return        s            256                                   
  55. D  @tmpsql        s            256                                   
  56. D  @like          s             10a                                  
  57. D  @equal         s              4a                                  
  58. D  @x             s              3  0                                
  59.                                                                      
  60. C                   If        @inpsql <> *blanks                     
  61. C                   Select                                           
  62. C                   If        @inpsql <> *blanks             
  63. C                   Select                                   
  64. C*---                                                        
  65. C*---  When @, then looking for blank in the field.          
  66. C*---                                                        
  67. C                   When      %subst(@inpsql:1:1) = '@'      
  68. C                             and %len(%trim(@inpsql)) = 1   
  69. C                   Return    %trim(@varname)                
  70. C                             + ' = '  + ''' ''' + ' '       
  71. C*---                                                        
  72. C*---  When just !, then looking for non-blank field.        
  73. C*---                                                        
  74. C                   When      %subst(@inpsql:1:1) = '!'      
  75. C                             and %len(%trim(@inpsql)) = 1   
  76. C                   Return    %trim(@varname)                
  77. C                             + ' <> '  + ''' ''' + ' '      
  78. C*---                                                        
  79. C*---  When begins with an !, then looking for not the value.
  80. C*---                                                        
  81. C                   When      %subst(@inpsql:1:1) = '!'                    
  82. C                             and %len(%trim(@inpsql)) <> 1                
  83. C                   Eval      @like = ' not like '                         
  84. C                   Eval      @equal = ' <> '                              
  85. C                   Eval      @tmpsql = %subst(@inpsql:2)                  
  86. C*---                                                                      
  87. C*---  Otherwise, look for the value.                                      
  88. C*---                                                                      
  89. C                   Other                                                  
  90. C                   Eval      @like = ' like '                             
  91. C                   Eval      @equal = ' = '                               
  92. C                   Eval      @tmpsql = @inpsql                            
  93. C                   Endsl                                                  
  94. C**            The ' character can mess up my sql statement so I'm escaping
  95. C                   Eval      @x = 1                                       
  96. C                   Dou       @x > 256                                     
  97. C                   If        %subst(@tmpsql:@x:1) = ''''                  
  98. C                   Eval      @tmpsql = %subst(@tmpsql:1:@x) + '''' +      
  99. C                             %subst(@tmpsql:@x+1)                         
  100. C                   Eval      @x = @x + 1                                  C                   Endif                                              
  101. C                   Eval      @x = @x + 1                              
  102. C                   Enddo                                              
  103. C                   If        %scan('%':@tmpsql) > 0                   
  104. C                   eval      @return = @trim + '(' + %trim(@varname) +
  105. C                             ')' + @like                              
  106. C                   Else                                               
  107. C                   Eval      @return = %trim(@varname) + @equal       
  108. C                   Endif                                              
  109. C                   Eval      @return = %trim(@return) + ' ' + ''''    
  110. C                             + %trim(@tmpsql)                         
  111. C                             + '''' + ' '                             
  112. C                   Endif                                              
  113. C                   Return    @return                                  
  114. P #Fmtsqlc        E                                                    
  115.  ********************************************************************  
  116.  *  Procedure #Fmtsqll - format URL/EMAIL variables for where stmt     
  117.  *                   Change to lower case to make it case insenitive   
  118.  ********************************************************************  
  119. P #Fmtsqll        B                                                    
  120. D #fmtsqll        Pi           256a                       
  121. D  @inpsql                     256a   Const               
  122. D  @varname                     12a   Const               
  123.                                                           
  124. D  @return        s            256                        
  125. D  @tmpsql        s            256                        
  126. D  @like          s             10a                       
  127. D  @equal         s              4a                       
  128. D  @x             s              3  0                     
  129.                                                           
  130. c                   If        @inpsql <> *blanks          
  131. C                   Select                                
  132. C*---                                                     
  133. C*---  When @, then looking for blank in the field.       
  134. C*---                                                     
  135. c                   When      %subst(@inpsql:1:1) = '@'   
  136. c                             and %len(%trim(@inpsql)) = 1
  137. c                   Return    %trim(@varname)             
  138. c                             + ' = '  + ''' ''' + ' '    
  139. C*---                                                     
  140. C*---  When just !, then looking for non-blank field.        
  141. C*---                                                        
  142. c                   When      %subst(@inpsql:1:1) = '!'      
  143. c                             and %len(%trim(@inpsql)) = 1   
  144. c                   Return    %trim(@varname)                
  145. c                             + ' <> '  + ''' ''' + ' '      
  146. C*---                                                        
  147. C*---  When begins with an !, then looking for not the value.
  148. C*---                                                        
  149. c                   When      %subst(@inpsql:1:1) = '!'      
  150. c                             and %len(%trim(@inpsql)) <> 1  
  151. c                   Eval      @like = ' not like '           
  152. c                   Eval      @equal = ' <> '                
  153. c                   Eval      @tmpsql = %subst(@inpsql:2)    
  154. C*---                                                        
  155. C*---  Otherwise, look for the value.                        
  156. C*---                                                        
  157. c                   Other                                    
  158. c                   Eval      @like = ' like '               
  159. c                   Eval      @equal = ' = '                 
  160. c                   Eval      @tmpsql = @inpsql                            
  161. c                   Endsl                                                  
  162.  **            The ' character can mess up my sql statement so I'm escaping
  163. c                   Eval      @x = 1                                       
  164. c                   Dou       @x > 256                                     
  165. c                   If        %subst(@tmpsql:@x:1) = ''''                  
  166. c                   Eval      @tmpsql = %subst(@tmpsql:1:@x) + '''' +      
  167. c                             %subst(@tmpsql:@x+1)                         
  168. c                   Eval      @x = @x + 1                                  
  169. c                   Endif                                                  
  170. c                   Eval      @x = @x + 1                                  
  171. c                   Enddo                                                  
  172. c                   Eval      @return = 'lower(' +                         
  173. c                                       %trim(@varname)                    
  174. c                                       + ')'                              
  175. c                   If        %scan('%':@tmpsql) > 0                       
  176. c                   Eval      @return = %trim(@return) + @like             
  177. c                   Else                                                   
  178. c                   Eval      @return = %trim(@return) + @equal            
  179. c                   Endif                                                  
  180. c                   Eval      @return = %trim(@return) + ' '         
  181. c                             + 'lower(' + '''' + %trim(@tmpsql)     
  182. c                             + '''' + ') '                          
  183. c                   Endif                                            
  184. c                   Return    @return                                
  185. P #Fmtsqll        E                                                  
  186.  ********************************************************************
  187.  *  Procedure #Fmtsqln - format numeric variables for where stmt     
  188.  ********************************************************************
  189. P #Fmtsqln        B                                                  
  190. D #fmtsqln        Pi           256a                                  
  191. D  @inpsql                     256a   Const                          
  192. D  @varname                     12a   Const                          
  193.                                                                      
  194. D  @return        s            256                                   
  195. D  @like          s             10a                                  
  196. D  @equal         s              4a                                  
  197. D  @tmpsql        s            256                                   
  198.                                                                      
  199. c                   If        @inpsql <> *blanks                     
  200. C                   Select                                     
  201. C*---                                                          
  202. C*---  When @, then looking for zero in the field.             
  203. C*---                                                          
  204. c                   When      %subst(@inpsql:1:1) = '@'        
  205. c                             and %len(%trim(@inpsql)) = 1     
  206. c                   Return    %trim(@varname)                  
  207. c                             + ' = 0 '                        
  208. C*---                                                          
  209. C*---  When just !, then looking for non-zero field.           
  210. C*---                                                          
  211. c                   When      %subst(@inpsql:1:1) = '!'        
  212. c                             and %len(%trim(@inpsql)) = 1     
  213. c                   Return    %trim(@varname)                  
  214. c                             + ' <> 0 '                       
  215. C*---                                                          
  216. C*---  When begins with an !, then looking for not the value.  
  217. C*---                                                          
  218. c                   When      %subst(@inpsql:1:1) = '!'        
  219. c                             and %len(%trim(@inpsql)) <> 1    
  220. c                   Eval      @like = ' not like '             
  221. c                   Eval      @equal = ' <> '                  
  222. c                   Eval      @tmpsql = %subst(@inpsql:2)      
  223. C*---                                                          
  224. C*---  Otherwise, look for the value.                          
  225. C*---                                                          
  226. c                   Other                                      
  227. c                   Eval      @like = ' like '                 
  228. c                   Eval      @equal = ' = '                   
  229. c                   Eval      @tmpsql = @inpsql                
  230. c                   Endsl                                      
  231. C                                                              
  232. c                   Eval      @return = %trim('trim(char('     
  233. c                             + %trim(@varname)                
  234. c                             + '))')                          
  235. c                   If        %scan('%':@tmpsql) > 0           
  236. c                   Eval      @return = %trim(@return) + @like 
  237. c                   Else                                       
  238. c                   Eval      @return = %trim(@return) + @equal
  239. c                   Endif                                      
  240. c                   Eval      @return = %trim(@return) + ' ' + ''''
  241. c                             + %trim(@tmpsql)                     
  242. c                             + '''' + ' '                         
  243. c                   Endif                                          
  244. c                   Return    @return                              
  245. P #Fmtsqln        E                                                
© 2004-2019 by midrange.com generated in 0.01s valid xhtml & css