page d'accueil
Boite à outils

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 :

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

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 :

How to do this :

Comment faire cela :

first, I need some fields :

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

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 :

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

Jusqu’ici, j’ai construit un ordre SQL : ce 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

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” 

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)

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 :

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

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

You now are ready to prepare the built sql based on

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

The obtained select is:

Le SQL obtenu est le suivant :

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.