Dynamic Where and Order by in RPG4

Welcome page
Tools box

Cette page a été mise à jour le20 janvier 2009
Contact

page d'accueil
Boite à outils

Versionning 


I have been asked on the internet how to code dynamic SQL SELECTs in RPG programs, having this code as simple as possible, and as powerfull as in any web application.

J’ai été interpelé sur internet sur la meilleure méthode pour écrire un SELECT SQL dynamique en RPG, en gardant le code le plus simple possible, et aussi souple que n’importe quelle application Web.

I'm  happy to show how it is easy to code a completely dynamic select sql statement, with a dynamic where and a dynamic OrderBy

Je suis heureux de montrer à quel point c’est simple de coder un select sql dynamique, avec un where dynamique et un order by dynamique.

To illustrate this tip, the best start point is the user screen : look at this one  :

Pour illustrer cet exemple, le meilleur point de depart c’est l’écran utilisateur. Regardez celui-ci :

Screenshot 1 

The line 4 that start with a ~ is the where. The sign “near equal” returns the idea of choosing.

La 4° ligne, qui commence par un ~, est le where. Le signe « proche de » suggère l’idée d’un choix

The line 6 that start with a ^ is the order by. Such as the little triangle in the head of a sortable column (on a web page)

La 6° ligne, qui commence par un ^, est le order-by. Comme le petit triangle dans l’entête des colonnes triables sur les pages web. 

I deliberately don't use the words "where" or "order-by" on the screen, because it is sometimes not easy to explain the difference between sorting and selecting.

Délibérément, je n’utilise pas les mots “sélection” ou “tri” sur l’écran, parce qu’il est souvent difficile d’expliquer la différence entre trier et sélectionner.

And user purpose is not to understand my vocabulary, but to have an intuitive screen (even when it’s a green screen).

Et le but de l’utilisateur n’est pas de comprendre mon vocabulaire, mais d’avoir un écran intuitif (même s’il est vert)

The word "Filter" sticks a little more to this target.

Le mot “filtre” colle un peu mieux à l’objectif

My purpose is here to show how to code the where and the order-by to generate just the necessary code.

Mon but ici est de montrer une methode pour coder les clauses where et order-by, avec juste le code nécessaire. Et du code simple à maintenir.

The filter part of the screen DDS :

La partie “filtre” de l’écran DDS

[CODE]
the where :
A                                  4  2'~'                                  
A            FBCUSNUM  R        B  4  4REFFLD(CUSNUM QIWS/QCUSTCDT)         
A                                      EDTCDE(3)                            
A            FBLSTNAM  R        B  4 11REFFLD(LSTNAM QIWS/QCUSTCDT)         
A            FBINIT    R        B  4 20REFFLD(CUSREC/INIT QIWS/QCUSTCDT)    
A            FBSTREET  R        B  4 24REFFLD(CUSREC/STREET QIWS/QCUSTCDT)  
A            FBCITY    R        B  4 38REFFLD(CUSREC/CITY QIWS/QCUSTCDT)    
A            FBSTATE   R        B  4 45REFFLD(CUSREC/STATE QIWS/QCUSTCDT)   
A            FBZIPCOD  R        B  4 48REFFLD(CUSREC/ZIPCOD QIWS/QCUSTCDT)  
A                                      EDTCDE(3)                            
A            TBCUSNUM  R        B  5  4REFFLD(CUSNUM QIWS/QCUSTCDT)         
A                                      EDTCDE(3)                            
A            TBZIPCOD  R        B  5 48REFFLD(CUSREC/ZIPCOD QIWS/QCUSTCDT)  
A                                      EDTCDE(3)                            
the order :
A                                  6  2'^'        
A            OBCUSNUM       1Y 0B  6  4RANGE(0 9)
A                                      EDTCDE(4)  
A            OBLSTNAM       1Y 0B  6 11RANGE(0 9)
A                                      EDTCDE(4)  
A            OBINIT         1Y 0B  6 20RANGE(0 9)
A                                      EDTCDE(4)  
A            OBSTREET       1Y 0B  6 24RANGE(0 9)
A                                      EDTCDE(4)  
A            OBCITY         1Y 0B  6 38RANGE(0 9)
A                                      EDTCDE(4)  
A            OBSTATE        1Y 0B  6 45RANGE(0 9)
A                                      EDTCDE(4)  
A            OBZIPCOD       1Y 0B  6 48RANGE(0 9)
A                                      EDTCDE(4)  
[/CODE]

this excerpt is the filter for the format B of my DSPF. 

The filter is in the controller of my B subfile

  • - the subfile fields are prefixed SB
  • - in the controller,
  • ** the from-fields are prefixed FB
  • ** the to-fields are prefixed TB
  • ** the order-fields are prefixed OB

Cet extrait est le filtre, le format B de mon DSPF.

Le filtre est dans le contrôleur du sous-fichier B

  • -         Les champs du sous-fichier sont préfixés SB
  • -         Dans le contrôleur
  • ** les champs « à partir de » sont préfixés FB
  • ** les champs « jusqu’à » sont préfixés TB
  • ** les champs « triés par » sont préfixés OB

As you can see on the screenshot, the user keys the filter values in the lines 4 & 5

For numeric values, 
line 4 is the minimum value and 
line 5 is the maximum value

For char values, 
line 4 is the like value,

comme vous pouvez le voir sur la copie d’écran, l’utilisateur saisi les valeurs de filtre sur les lignes 4 & 5

Pour les valeurs numériques
La ligne 4 permet de saisir « à partir de »
La ligne 5 permet de saisir « jusqu’à »

Pour les valeurs « caractère »
La ligne 4 permet de saisir le « like »

And in line 6 the user chooses the order the columns (IE in what order I have to insert the column names into the order-by clause)

Et sur la ligne 6, l’utilisateur choisit l’ordre des colonnes, c’est-à-dire, l’ordre dans lequel je dois ajouter les colonnes dans la clause “order-by”

If the user don't change the criteria (on the previous screen shot), generated sql is : 

Si l’utilisateur conserve les critères présentés (voir la copie d’écran ci-dessus), le sql généré est le suivant :


[CODE]

Select * from qiws/qcustcdt  order by LSTNAM   ,INIT  ,STREET   ,CITY     ,STATE    ,ZIPCOD    optimize for 15 rows         
[/CODE]  

How to do this :

Comment faire cela :

first, I need some fields :

Tout d’abord, j’ai besoin de quelques zones


[CODE]

d mySelect        s          32787    varying
d myFrom          s          32787    varying
d myWhere         s          32787    varying
d WhereAnd        s          32787    varying
d mySelectMore    s          32787    varying
d MyorderCount    c                   const(9)                   
d MyOrder         ds                  qualified                  
d Field                         20    dim(MyOrdercount) varying  
d OrderBy         s             20    varying                    
d mySFW           s           5000                               
[/CODE]

This code is compatible with old versions of the SQL of SystemI ; Dynamic SQL string resides in a 5000 fixed-length chars field

Ce code est compatible avec les vieilles versions de SQL du SystemI : le sql dynamique est stocké dans une zone de 5000 caractères, fixe

But it's not my subject.

Mais ce n’est pas le sujet

How to build the dynamic sql string :

Voici comment construire ce select dynamique :

Initialize the variables :

Initialiser les variables :


[CODE]

mySelect = 'Select * ';
myFrom = 'from qiws/qcustcdt ';
whereAnd = 'Where ';           
myWhere = '';                  
[/CODE]

up to now, I have build a SQL statement : this code

Jusqu’ici, j’ai construit un ordre SQL : ce code

[CODE]mySFW = myselect + myfrom + mywhere;  [/CODE]

would be prepared correctly.

Peut déjà être préparé correctement

Now it's time to build the Where clause. Have a particular attention to the variable named WhereAnd

Maintenant, c’est le moment de construire la clause “Where”. J’attire votre attention sur la variable WhereAnd


[CODE]
if FBCUSNUM <> 0;                                
   myWhere = myWhere + whereAnd +                
      'cusnum >=     ' + %char(fbcusnum)       ;
   whereand = ' and ';                           
endif;                                           
if 0 < tBCUSNUM and tbcusnum < 999999;           
   myWhere = myWhere + whereAnd +                
      'cusnum <=     ' + %char(tbcusnum)       ;
   whereand = ' and ';                           
endif;                                           
[/CODE]

For each char field, there is only one field in the filter. I use it to build a LIKE

Pour chaque colonne alphabétique, il y a une seule valeur pour définir le filtre. Je l’utilise pour définir un “like” 


[CODE]

if FBLSTNAM <>'' and FBLSTNAM <>'%';              
   myWhere = myWhere + whereAnd +                 
      'lstnam like ''' + %trim(FBLSTNAM) + '%''';
   whereand = ' and ';                            
else;                                             
   FBLSTNAM = '%';                                
endif;                                            
[/CODE]

Note: the tip here is in the independence of each IF.
Each IF is coded the same way and generated code match the user requirement.

It’s easy to add a new criterion: duplicate an IF.
And it's also easy to remove a criterion: remove an IF.

Note : le truc ici est dans l’indépendance de chaque IF. Chaque IF est codé de la même manière, et le code généré satisfait le besoin de l’utilisateur.

C’est facile d’ajouter un nouveau critère : dupliquer un IF. Et pour supprimer un critère, enlever le IF.

With this filter (screenshot2) :

Avec ce filtre (ScreenShot2)

ScreenShot 2

I got

  • fbcusnum=111111
  • tbcusnum=999999
  • fblstnam="%o"

Je reçois

  • fbcusnum=111111
  • tbcusnum=999999
  • fblstnam="%o"

I obtain this select :

Et j’obtient ce select :

[CODE]
Select * from qiws/qcustcdt Where cusnum >=     111111 and lstnam like '%o%' order by STATE    ,CITY      optimize for 15 rows      
[/CODE]   

That's all for the where clause.

C’est tout pour la clause “where”

Now, I build the OrderBY clause:

Maintenant, je construit la clause “OrderBy”

I do this in 2 steps:

Je fais cette opération en deux étapes

First is to convert ordinal numbers (in fields OB*) into field names.

Tout d’abord, je convertis les numéros de colonnes en nom de colonnes

Note: take care of usage, it's a little counter-intuitive with the “order by 1, 2” that you may currently use in your sql statements. Order-by values don’t belong to field names in the select but belong to the name of the files on the user screen.

Note : attention à l’utilisation un tantinet contre-intuitive par rapport à “order by 1, 2” que vous utilisez peut-être dans vos SQL. Ici les “orderby” ne dépendent pas des noms de colonnes du select, mais dépendent du nom des colonnes sur l’écran.

To do this orderBy, I use an array to save the actual field names depending on their related OBfield.

Pour preparer cet “orderby”, j’utilise un tableau pour sauver le nom réel des colonnes selon leur Obfield

Sample : (see upper screenshot)
obstate = 1
obcity = 2
other OB* are = zero

Example : (voir l'écran ci-dessus)
obstate = 1
obcity = 2
les autres OB* sont = zero

[CODE]
clear MyOrder;                                                                      
if 0< OBcusnum and OBcusnum <= MyOrderCount;                                        
   MyOrder.Field(OBcusnum)+=MyOrder.comma(OBcusnum) +'cusnum';                      
   MyOrder.Comma(OBcusnum) =',';                                                    
endif;                                                                              
if 0< OBLSTNAM and OBLSTNAM <= MyOrderCount;                                        
   MyOrder.Field(OBlstnam)+=MyOrder.comma(OBlstnam) +'lstnam';                      
   MyOrder.Comma(OBlstnam) =',';                                                    
endif;                                                                              
if 0< OBINIT and OBINIT <= MyOrderCount;                                           
   MyOrder.Field(OBinit  )+=MyOrder.comma(OBinit  ) +'init  ';                     
   MyOrder.Comma(OBinit  ) =',';                                                   
endif;                                                                             
if 0< OBSTREET and OBSTREET <= MyOrderCount;                                       
   MyOrder.Field(OBstreet)+=MyOrder.comma(OBstreet) +'street';                     
   MyOrder.Comma(OBstreet) =',';                                                   
endif;                                                                             
if 0< OBCITY and OBCITY <= MyOrderCount;                                           
   MyOrder.Field(OBcity  )+=MyOrder.comma(OBcity  ) +'city  ';                     
   MyOrder.Comma(OBcity  ) =',';                                                   
endif;                                                                             
if 0< OBSTATE and OBSTATE <= MyOrderCount;                                         
   MyOrder.Field(OBstate )+=MyOrder.comma(OBstate ) +'state ';                     
   MyOrder.Comma(OBstate ) =',';                                                   
endif;                                                                             
if 0< OBZIPCOD and OBZIPCOD <= MyOrderCount;                                       
   MyOrder.Field(OBzipcod)+=MyOrder.comma(OBzipcod) +'zipcod';                     
   MyOrder.Comma(OBzipcod) =',';                                                   
endif;                                                                             
[/CODE]
 

This code loads the array MyOrder with STATE in first occurs and CITY in second.

Ce code charge le tableau MyOrder avec “STATE” dans le poste 1 et “CITY” dans le poste 2

Note: As for building the where, the code is easy to read, it's easy to add or remove a criterion.

Note : de même que pour le where, le code est facile à lire, et c’est très facile d’ajoutr ou d’enlever un critère.

The second step of the orderby process is to add these sorting fields into the select.

La seconde étape consiste à ajouter la liste des colonnes de tri au select

This code does the job:

Ce code fait le travail

[CODE]
OrderBy=' order by ';                             
mySelectMore='';                                  
for iFor = 1 to MyOrderCount;                     
   if myorder.field(iFor)<> '';                   
      mySelectMore+=OrderBy + MyOrder.Field(iFor);
      OrderBy=',';                                
   endif;                                         
endfor;                                           
mySelectMore+= ' optimize for 15 rows';           
[/CODE]

You now are ready to prepare the built sql based on

Vous êtes maintenant prêt pour préparer ce SQL :

[CODE]
mySFW = myselect + myfrom + mywhere + myselectmore;                      
[/CODE]

The obtained select is:

Le SQL obtenu est le suivant :

[CODE]
Select * from qiws/qcustcdt Where cusnum >=     111111 and lstnam like '%o%' order by STATE    ,CITY      optimize for 15 rows     
[/CODE]  

And user screen shows

ScreenShot3 

Attached to this page, these is

Vous trouverez attaché à cette page

QCUSTFM : the DSPF used on this tip

QCUSTFM : le DSPF utilisé dans cette démo

QCUSTRWOC : the SQLRPGLE program, without any copy (easier to install, QCUSTR need JP4INC)

QCUSTRWOC : le programme SQLRPGLE, sans aucune copie (plus facile à installer, QCUSTR a besoin de JP4INC)

To compile this SQLRPGLE program, remember to add QIWS library in your library list.

Pour compiler ce programme SQLRPGLE, n’oubliez pas d’ajouter QIWS dans votre liste de bibliothèques

When running QCUSTRWOC, use the F5 key to “apply” the filter again after changing the filter values.

Quand vous utilisez QCUSTRWOC, utilisez F5 pour “ré-appliquer” le filtre.

It’s a rule that comes from my users : they hate when I erase their input, just because they have keyed Enter.

C’est une règle qui vient des utilisateurs : ils détestent que j’écrase leur saisie, juste parce qu’ils ont appuyé sur la touche Entrée.

last version : How to upload to my 400

 QCUSTFM.MBR
 QCUSTRWOC.MBR
 QCUSTR.MBR

Compilation commands I have used :

CHGPFM FILE(JPLTOOLS/QPGMSRC)             
       MBR(QCUSTFM)                       
       SRCTYPE(DSPF)                      
       TEXT('display file for QIWS/QCUSTCDT')                     

CHGPFM FILE(JPLTOOLS/QPGMSRC)    
       MBR(QCUSTRWOC)            
       SRCTYPE(SQLRPGLE)         
       TEXT('display QIWS/QCUSTCDT')             

CRTDSPF FILE(JPLTOOLS/QCUSTFM) SRCFILE(JPLTOOLS/QPGMSRC) RSTDSP(*YES)                                                                          

CRTSQLRPGI OBJ(JPLTOOLS/QCUSTRWOC) SRCFILE(JPLTOOLS/QPGMSRC) OBJTYPE(*MODULE) OPTION(*NOSEQSRC *XREF *SECLVL) DBGVIEW(*SOURCE) tgtrls(v5r3m0)                                                                             
CRTPGM PGM(JPLTOOLS/QCUSTRWOC) BNDDIR(QC2LE) ACTGRP(JPLTOOLS)                                                                          



This tip is a free source text; you can redistribute it and/or modify it as much as you need. Just add as comment in the code a reference to this site : http://jplamontre.free.fr/jpltools.htm ... and send me an email Contact with "jpltools" in the subject (it's for my antispam rules). I will be happy to know what you have done with the jpltools.This tool is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY :

THIS TOOLBOX IS PROVIDED ''AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE PROVIDER OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS TOOLBOX, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.