| Renumérotation de lignes
| | Suiv. |
Le but est de renuméroter une table de 10 en 10.
Pour ceci on suppose que notre table contient un champ NUM et un champ TEXTE.
La stratégie est la suivante :
- créer une correspondance entre les anciens et les nouveaux numéros de lignes,
- recopier les données en remplaçant les anciens numéros par les nouveaux.
Création de la correspondance
SELECT Count(tab.texte)*10 AS numnou, Tab.num
FROM Tab, Tab AS Tab2
WHERE Tab.num >=tab2.num
GROUP BY Tab.num
|
La sortie s'effectue dans une table temporaire.
|
Recopie des données
SELECT tabtmp.numnou AS num, Tab.texte
FROM tabtmp INNER JOIN tab ON tabtmp.num = Tab.num
|
|
Autre solution avec requêtes imbriquées
SELECT tabtmp.numnou AS num, Tab.texte
FROM
(
SELECT Count(tab.texte)*10 AS numnou, Tab.num
FROM Tab, Tab AS Tab2
WHERE Tab.num >=tab2.num
GROUP BY Tab.num
) tabtmp
INNER JOIN tab ON tabtmp.num = Tab.num
|
|
Il s'agit ici de récupérer des données d'un fichier spoule (grand livre), sachant que celui-ci est constitué de lignes d'entetes compte et de lignes de détail d'écritures.
La ligne d'entete se décompose ainsi :
- Position 10: la mention 'Compte',
- Position 19: le n° de compte (10 car.),
- Position 32: l'intitulé du compte (30 car.).
La ligne détail a la structure suivante :
- Position 07: la date en format jj/mm/aa; le / l'identifiera,
- Position 23: le n° de pièce (10 car.),
- Position 95: le montant sous forme xx.xxx.xxx,xx.
Le fichier spoule est préalablement copié (CPYSPLF) dans un fichier ayant 200 caractères de long, nommé SPLF.
Le processus se décompose ensuite en 4 phases :
- extraire les n° des lignes d'entete,
- extraire les n° des lignes de détail,
- rapprocher les n° des lignes d'entete et de détail,
- rechercher les données à partir des rapprochements entre les entetes et les détails.
Extraction des n° des lignes d'entete, dans un fichier SPLFE
SELECT RRN(splf) as rang from splf
WHERE substr(splf , 10, 6) = 'Compte'
|
|
Extraction des n° des lignes de détail, dans un fichier SPLFD
SELECT RRN(splf) as rang from splf
WHERE substr(splf , 9, 1) = '/'
|
|
Rapprochement des n° de lignes entete et détail, dans un fichier SPLFR
SELECT max(e.rang) as range, d.rang as rangd
FROM splfe e, splfd d
WHERE e.rang < d.rang
GROUP BY d.rang
|
|
Extraction des données, dans un fichier SPLFX
SELECT substr(a.splf, 19, 10) as compte,
substr(a.splf, 32, 30) as intitu,
substr(b.splf, 7, 8) as datepi,
substr(b.splf, 23, 10) as piece,
case when substr(b.splf, 106, 1)=' ' then 0 else
decimal(
substr(b.splf, 95, 2) !!
substr(b.splf, 98, 3) !!
substr(b.splf, 102, 6)
, 12, 2)
end as montan
FROM splfr c, splf a, splf b
WHERE rrn(a)=c.range and rrn(b)=c.rangd
|
|
| Recherche de maximum dans un groupe
| Préc. | |
On est parfois amené à rechercher la valeur associée à une valeur maximum dans un groupe. Par exemple, prendre pour un client la valeur d'une donnée statistique correspondant à la dernière valeur connue à une certaine date d'arrêté.
Exemple :
| Date (aaaamm) | Montant |
| 201201 | 1000 |
| 201202 | 1500 |
| 201203 | 1200 |
| 201204 | 1400 |
Par la fonction max, on ne peut obtenir simultanément la date voulue (ex: 201203) et la valeur correspondante (1200). Il faut d'abord trouver 201203 puis, en reprenant cette valeur, une sous-requête extrait la bonne valeur.
La solution est de composer une expression qui combine la période et le montant (dans cet ordre puisque la date est le critère important), et d'en rechercher le maximum.
Pour des valeurs numériques, en estimant le montant maximum possible, on pourrait calculer les expressions :
- date * 1 000 000 + montant
- date * 1 000 000
En calculant le max de chacune on obtient 201203001200 et 201203000000. En faisant la différence des 2, on obtient le montant; en divisant la seconde par 1 000 000, on obtient la date.
Avec des zones alphanumériques, on procède par concaténation et extraction de sous-chaînes.
|
