insertion avec multiples lignes

**f066457** · 22/03/2006, 03h58

Bonjour,

Je travaille avec une base de donnée DB2/AS400 V5R3.
J'ai une table TEMP qui contient seulement les champs id (integer) auto incrémental, numero (char (12)) et lib (char 20).

Ex:
1 200603000101 toto
2 200603000102 toto
3 200603000103 toto
4 200603000104 toto
5 200603000201 marcel
6 200603000202 marcel
7 200603000203 marcel
8 200603000204 marcel

Comme vous pouvez le voir, il y a chaque fois une séquence du numéro et chaque séquence a son propre lib.

Aujourd'hui
Aujourd'hui, j'alimente cette table ainsi (en passant via la couche JDBC)

select (bigint(max(numero))/100 + 1) * 100 from temp
cela ramène 200603000300, appelons le NEXTNUM, mon prochain numero à créer (-1).

et j'exécute 4 INSERT :
insert into temp (numero, lib) values ('"+NEXTNUM+1+"' , 'justin);
insert into temp (numero, lib) values ('"+NEXTNUM+2+"' , 'justin');
insert into temp (numero, lib) values ('"+NEXTNUM+3+"' , 'justin');
insert into temp (numero, lib) values ('"+NEXTNUM+4+"' , 'justin');

Et j'obtiens ces nouvelles lignes
*9 200603000301 justin
10 200603000302 justin
11 200603000303 justin
12 200603000304 justin

Question
Est-il possible de faire ces insert en une seule requête SQL ?
Encore mieux, est-il possible d'y inclure le select max avec ?
Qui parmi vous sera mon sauveur ? ;-)

**SQLpro** · 22/03/2006, 13h28

Il suffit de disposer d'une table de nombre du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE T_NUM
(NUL   INT NOT NULL PRIMARY KEY)
 
INSERT INTO T_NUM VALUES (0)
INSERT INTO T_NUM VALUES (1)
INSERT INTO T_NUM VALUES (2)
...
INSERT INTO T_NUM VALUES (999)

Dès lors, une jointure croisée avec cette table permettra de résoudre tout vos problèmes en une seule jointure.

A +

**f066457** · 23/03/2006, 01h27

OK, j'ai créé cette table.

Pour la jointure, Je peux avoir un exemple ?

Je crois avoir compris le principe, mais la syntaxe; j'ai trop de lacunes en SQL

**SQLpro** · 23/03/2006, 12h57

Si tu veut plus d'aide :
Poste le DDL de tes tables avec un jeu d'essais comme décrit ICI :
http://www.developpez.net/forums/viewtopic.php?t=32668

A +

**f066457** · 24/03/2006, 02h57

Je pensais avair été assez clair. je refais ça en essayant de coller d'avantage au lien.

1) Je travaille avec une base de donnée DB2/AS400 V5R3. Je ne poste donc pas dans le forum DB2 car il y a malheureusement trop de differences entre le DB2 et le DB2 for iseries...

2) J'ai une table TEMP_T_BON qui contient seulement les champs id (integer) auto incrémental, numero (char 12) et lib (char 20).

voici le script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE TEST_T_BON ( 
	ID integer GENERATED ALWAYS AS IDENTITY ( 
	START WITH 1 INCREMENT BY 1 
	NO MINVALUE NO MAXVALUE 
	NO CYCLE NO ORDER 
	NO CACHE ) , 
	NUMERO CHAR(12) DEFAULT NULL , 
	LIB  CHAR(20) DEFAULT NULL ) ;

3) Voici un jeu de données

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
insert into test_t_bon (numero, lib) values ('200603000101', 'toto');
insert into test_t_bon (numero, lib) values ('200603000102', 'toto');
insert into test_t_bon (numero, lib) values ('200603000103', 'toto');
insert into test_t_bon (numero, lib) values ('200603000104', 'toto');
insert into test_t_bon (numero, lib) values ('200603000201', 'marcel');
insert into test_t_bon (numero, lib) values ('200603000202', 'marcel');
insert into test_t_bon (numero, lib) values ('200603000203', 'marcel');
insert into test_t_bon (numero, lib) values ('200603000204', 'marcel');
insert into test_t_bon (numero, lib) values ('200604000101', 'auguste');
insert into test_t_bon (numero, lib) values ('200604000102', 'auguste');
insert into test_t_bon (numero, lib) values ('200604000103', 'auguste');
insert into test_t_bon (numero, lib) values ('200604000104', 'auguste');

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Code Numero       Lib
---- ------------ ---
1    200603000101 toto 
2    200603000102 toto 
3    200603000103 toto 
4    200603000104 toto 
5    200603000201 marcel 
6    200603000202 marcel 
7    200603000203 marcel 
8    200603000204 marcel 
9    200604000101 auguste 
10   200604000102 auguste 
11   200604000103 auguste 
12   200604000104 auguste

4) Description
4-1)
Le champ numero est décomposé ainsi :
aaaammxxxxxyy
aaaa : année
mm : mois
xxxx : séquence pour l'année et le mois
yy : chiffre dans la séquence pour le mois et l'année

Le champ numero est unique (même si dans mon cas ce n'est pas déterminé dans le script de création de la table).

4-2)
Le champ lib est un libellé simple

5) Ce que je veux faire
Pour toute insertion dans la table, je dois chercher la dernière séquence pour une période donnée ex : 200603.
Dans notre cas, c'est le numero 200603000204, donc la sequence 0002
IMPORTANT: si je ne le trouve pas, alors sequence = 0001.
Puis je calcule la séquence suivante pour 200603 : 00003
J'insère en table les enregistrements : 200603000301, 200603000302, 200603000303, 200603000304.

6) Comment je le fais oujourd'hui
Insertion des éléments pour la période 200603 avec lib = 'luc' :

- Recherche de la sequence:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select 
case when max(numero) is null then '0001'
else substr(max(numero),7,4)
end as sequence
from test_t_bon 
where  numero like '200603%'

Ce qui me ramène

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SEQUENCE
--------
0002

Insertion dans la table manuellement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
insert into test_t_bon (numero, lib) values ('200603000301', 'luc');
insert into test_t_bon (numero, lib) values ('200603000302', 'luc');
insert into test_t_bon (numero, lib) values ('200603000303', 'luc');
insert into test_t_bon (numero, lib) values ('200603000304', 'luc');

5) Ce que je voudrais faire
Je voudrais réussir à faire cela en une seule requête (le select max et les 4 insert).
SQLPRO m'a conseillé de créer une table T_NUM (vois plus haut dans le topic) et de faire une jointure croisée, mais je ne sais pas comment faire cette jointure.

J'espère avoir donné assez d'informations ce coup ci.

Merci d'avance !!!

**yoyopi** · 24/03/2006, 13h07

salut moi aussi j'utilise db2 for iseries sur as400,

ce que tu peut faire c'est créer une procedure sql sous db2 avec en paramètre d'entré ton libellé et qui te ferais tes insert elles-même.

Ensuite il ne te reste plus qu'a appeler cette procedure dans ton code.

Voilà j'espère que ça réponds à ta question

**SQLpro** · 24/03/2006, 13h18

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
CREATE TABLE TEST_T_BON 
(
   ID     integer IDENTITY,
   NUMERO CHAR(12),
   LIB    CHAR(20)) ;
 
 
insert into test_t_bon (numero, lib) values ('200603000101', 'toto');
insert into test_t_bon (numero, lib) values ('200603000102', 'toto');
insert into test_t_bon (numero, lib) values ('200603000103', 'toto');
insert into test_t_bon (numero, lib) values ('200603000104', 'toto');
insert into test_t_bon (numero, lib) values ('200603000201', 'marcel');
insert into test_t_bon (numero, lib) values ('200603000202', 'marcel');
insert into test_t_bon (numero, lib) values ('200603000203', 'marcel');
insert into test_t_bon (numero, lib) values ('200603000204', 'marcel');
insert into test_t_bon (numero, lib) values ('200604000101', 'auguste');
insert into test_t_bon (numero, lib) values ('200604000102', 'auguste');
insert into test_t_bon (numero, lib) values ('200604000103', 'auguste');
insert into test_t_bon (numero, lib) values ('200604000104', 'auguste');
 
-- le but : Insérer 'luc' avec prefix 200603 et éclatement.
-- doit donner l'équivalent de :
--insert into test_t_bon (numero, lib) values ('200603000301', 'luc');
--insert into test_t_bon (numero, lib) values ('200603000302', 'luc');
--insert into test_t_bon (numero, lib) values ('200603000303', 'luc');
--insert into test_t_bon (numero, lib) values ('200603000304', 'luc');
 
 
-- 1) décomposition :
SELECT NUMERO, 
       SUBSTRING(NUMERO, 1, 6) AS PREFIX, 
       CAST(SUBSTRING(NUMERO, 7, 4) AS INT) AS SEQUENCE, 
       SUBSTRING(NUMERO, 11, 2) AS SUFIXE,
       LIB
FROM   TEST_T_BON
 
 
NUMERO       PREFIX SEQUENCE    SUFIXE LIB                  
------------ ------ ----------- ------ -------------------- 
200603000101 200603 1           01     toto                
200603000102 200603 1           02     toto                
200603000103 200603 1           03     toto                
200603000104 200603 1           04     toto                
200603000201 200603 2           01     marcel              
200603000202 200603 2           02     marcel              
200603000203 200603 2           03     marcel              
200603000204 200603 2           04     marcel              
200604000101 200604 1           01     auguste             
200604000102 200604 1           02     auguste             
200604000103 200604 1           03     auguste             
200604000104 200604 1           04     auguste    
 
 
-- approche de la solution
SELECT 'luc' as nom, '200603' as tag, 
       CAST(MAX(CAST(SUBSTRING(NUMERO, 7, 4) AS INT)) + 1 AS VARCHAR(4)) AS MAX_SEQUENCE, 
       SUBSTRING(NUMERO, 11, 2)  AS SUFIXE
FROM   TEST_T_BON
WHERE  SUBSTRING(NUMERO, 1, 6) = '200603'
GROUP  BY SUBSTRING(NUMERO, 11, 2)
 
nom  tag    MAX_SEQUENCE SUFIXE 
---- ------ ------------ ------ 
luc  200603 3            01
luc  200603 3            02
luc  200603 3            03
luc  200603 3            04
 
 
-- cosmétique pour MAX_SEQUENCE (ajout des zéro en tête) :
SELECT nom, tag + CASE LEN(MAX_SEQUENCE)
                     WHEN 1 THEN '000'
                     WHEN 2 THEN '00'
                     WHEN 3 THEN '0'
                  END +MAX_SEQUENCE + SUFIXE AS NEW_TAG
FROM   (SELECT 'luc' as nom, '200603' as tag, 
               CAST(MAX(CAST(SUBSTRING(NUMERO, 7, 4) AS INT)) + 1 AS VARCHAR(4)) AS MAX_SEQUENCE, 
               SUBSTRING(NUMERO, 11, 2)  AS SUFIXE
        FROM   TEST_T_BON
        WHERE  SUBSTRING(NUMERO, 1, 6) = '200603'
        GROUP  BY SUBSTRING(NUMERO, 11, 2)) AS T
 
nom  NEW_TAG         
---- --------------- 
luc  200603000301
luc  200603000302
luc  200603000303
luc  200603000304
 
-- insertion :
INSERT INTO TEST_T_BON (LIB, NUMERO)
SELECT nom, tag + CASE LEN(MAX_SEQUENCE)
                     WHEN 1 THEN '000'
                     WHEN 2 THEN '00'
                     WHEN 3 THEN '0'
                  END +MAX_SEQUENCE + SUFIXE
FROM   (SELECT 'luc' as nom, '200603' as tag, 
               CAST(MAX(CAST(SUBSTRING(NUMERO, 7, 4) AS INT)) + 1 AS VARCHAR(4)) AS MAX_SEQUENCE, 
               SUBSTRING(NUMERO, 11, 2)  AS SUFIXE
        FROM   TEST_T_BON
        WHERE  SUBSTRING(NUMERO, 1, 6) = '200603'
        GROUP  BY SUBSTRING(NUMERO, 11, 2)) AS T
 
-- preuve
SELECT * FROM TEST_T_BON 
 
ID          NUMERO       LIB                  
----------- ------------ -------------------- 
1           200603000101 toto                
2           200603000102 toto                
3           200603000103 toto                
4           200603000104 toto                
5           200603000201 marcel              
6           200603000202 marcel              
7           200603000203 marcel              
8           200603000204 marcel              
9           200604000101 auguste             
10          200604000102 auguste             
11          200604000103 auguste             
12          200604000104 auguste             
13          200603000301 luc                 
14          200603000302 luc                 
15          200603000303 luc                 
16          200603000304 luc

ps : LEN est la fonction qui retourne la longueur réelle d'une chaine (CHARACTER_LENGTH)

A +

**f066457** · 27/03/2006, 01h52

Merci SQLPRO.

C'est très clair et je viens d'apprendre une chose fantastique : je ne savais pas qu'il était possible de faire un select à l'intérieur d'un from !!!
Ca rajoute plein de possibilités !!!

Pous que ça fonction sur mon DB2 AS400, j'ai simplement dû remplacer les '+' avec || en LEN avec LENGTH.

Il y a quand même 2 ombres au tableau:
1) lorsque je veux insérer les numéros d'une séquence qui n'a pas encore de "tag".

Par exemple, dans notre jeu d'essai, si on appelle avec 200605:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT 'luc' as nom, '200603' as tag, 
       CAST(MAX(CAST(SUBSTRING(NUMERO, 7, 4) AS INT)) + 1 AS VARCHAR(4)) AS MAX_SEQUENCE, 
       SUBSTRING(NUMERO, 11, 2)  AS SUFIXE 
FROM   TEST_T_BON 
WHERE  SUBSTRING(NUMERO, 1, 6) = '200605' 
GROUP  BY SUBSTRING(NUMERO, 11, 2)

Ce la ne ramène rien:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
nom  tag    MAX_SEQUENCE SUFIXE 
---- ------ ------------ ------

L'insert ne marche donc pas.

2) lorsque certains numeros sont supprimés (le 200604000103 200604000104 par exemple), le select

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT 'luc' as nom, '200604' as tag, 
       CAST(MAX(CAST(SUBSTRING(NUMERO, 7, 4) AS INT)) + 1 AS VARCHAR(4)) AS MAX_SEQUENCE, 
       SUBSTRING(NUMERO, 11, 2)  AS SUFIXE 
FROM   TEST_T_BON 
WHERE  SUBSTRING(NUMERO, 1, 6) = '200604' 
GROUP  BY SUBSTRING(NUMERO, 11, 2)

ne ramène que 2 lignes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
nom  tag    MAX_SEQUENCE SUFIXE 
---- ------ ------------ ------ 
luc  200604 2            01 
luc  200604 2            02

J'ai essayé de greffer mon "when max(numero) is null then ..." mais en vain.
Je pense que la solution est proche ;-)

Savez-vous comment éclaircir ces 2 ombres restantes ?
Merci.

**SQLpro** · 27/03/2006, 16h20

1) faire une requête UNION avec séquence initiale

2) SQL ne peut deviner les lignes effacées... Il ne pourra que se baser sur ce qui existe. Si vous voulez gérer un compteur alors il faut une table de comptage... Voir l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/clefs/

permettez moi simplement de vous faire une petite remarque : votre modèle de données avec une données comprenant différentes partie à la sémantique différente viole toutes les règles de la modélisation et va conduire à une base inexploitable lorsque la volumétrie des données sera importante.
Vous devriez modifier votre modèle pour que votre colonne soit NUMERO soit éclatée en plusieurs colonne. C'est toujours une chose stupide et catastrophique que ce genre de codification employé du temps de COBOL !

A +

**f066457** · 28/03/2006, 00h52

Bonjour SQLPRO

Envoyé par SQLpro

1) faire une requête UNION avec séquence initiale

Malheureusement, les séquences (CREATE SEQUENCE) n'existent pas dans ma version de DB2 V5R2 et non pas V5R3 comme je l'ai cité par erreur plus haut. Je sais que cette fonctionnalité a été rajoutée dans la V5R3

Si l'in de vous connait son équivalent en V5R2, je suis preneur

Envoyé par SQLpro

2) SQL ne peut deviner les lignes effacées... Il ne pourra que se baser sur ce qui existe.

J'ai dû mal m'expliquer sur ce point. Ce n'est pas un problème si une ou plusieurs lignes sont effacées, que ce soit 1 ou 4 des lignes. C'est fonctionellement autorisé par les applications utilisant cette table.
La seule chose qui importe, c'est que si j'insére une nouvelle série d'enregistrements, il doit y avoir 4 lignes d'insérées en tenant compte de la dernière sequence existant dans la table, pour le tag donné. Si la séquence n'existe pas encore, alors on prend la valeur 0001

Si par exemple j'ai les éléments suivants dans ma table AVANT l'insert (certaines lignes de marcel et toto effacées):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
ID          NUMERO       LIB                  
----------- ------------ -------------------- 
1           200603000101 toto                
4           200603000104 toto                
5           200603000201 marcel              
6           200603000202 marcel              
9           200604000101 auguste              
10          200604000102 auguste              
11          200604000103 auguste              
12          200604000104 auguste

Si j'insère des éléments avec le tag 200603 et le nom luc, la table doit ressembler à cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ID          NUMERO       LIB                  
----------- ------------ -------------------- 
1           200603000101 toto                
4           200603000104 toto                
5           200603000201 marcel              
6           200603000202 marcel              
9           200604000101 auguste              
10          200604000102 auguste              
11          200604000103 auguste              
12          200604000104 auguste              
13          200603000301 luc                  
14          200603000302 luc                  
15          200603000303 luc                  
16          200603000304 luc

C'est à dire que la séquence de chaque numéro inséré s'est bien basée sur la séquence max pour le tag 200603 (à savoir 0002) et chaque numero inséré est le résultat de la concaténation de 200606 + 0003 + 01..04.

De même, si j'insère des éléments avec le tag 200605 et le nom arthur, la table doit ressembler à cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ID          NUMERO       LIB                  
----------- ------------ -------------------- 
1           200603000101 toto                
4           200603000104 toto                
5           200603000201 marcel              
6           200603000202 marcel              
9           200604000101 auguste              
10          200604000102 auguste              
11          200604000103 auguste              
12          200604000104 auguste              
13          200603000301 luc                  
14          200603000302 luc                  
15          200603000303 luc                  
16          200603000304 luc    
17          200605000101 arthur                  
18          200605000102 arthur                  
19          200605000103 arthur                  
20          200605000104 arthur

C'est à dire que la sequence max pour le tag 200605 n'existe pas, donc la sequence est 0001 et chaque numero inséré est le résultat de la concaténation de 200605 + 0001 + 01..04.

Envoyé par SQLpro

Voir l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/clefs/

Cet article est très interessant. Je vais essayer d'appliquer le principe de la procédure stockée...

Envoyé par SQLpro

permettez moi simplement de vous faire une petite remarque : votre modèle de données avec une données comprenant différentes partie à la sémantique différente viole toutes les règles de la modélisation et va conduire à une base inexploitable lorsque la volumétrie des données sera importante.
Vous devriez modifier votre modèle pour que votre colonne soit NUMERO soit éclatée en plusieurs colonne. C'est toujours une chose stupide et catastrophique que ce genre de codification employé du temps de COBOL !

A +

A qui le dites vous ...
Je développe malheureusement une application qui doit marcher en parallèle avec une vieille application existante qui elle ne doit pas être modifiée (pas de sources...) J'aurais bien voulu éclater cette satanée table... Et pour vous faire encore plus peur, ce n'est malheureusement pas la seule...

La seule solution que j'ai trouvée aujourd'hui pour la rendre plus rapide, c'est de créer un index sur la colonne numéro.

Merci beaucoup.
@ bientôt

insertion avec multiples lignes

Langage SQL

Vue hybride

Discussions similaires

Partager

Partager