Ordonner un tableau à 2 dimensions

**gilal** · 09/11/2008, 18h15

Bonjour a Tous
Voila,je me prends la tete pour un truc que j'ai deja fait il y a une paire d'année.Je fais donc appel a vous.

J'ai un table TOTO avec 2 colonnes champ1 et champ2 du type varchar
rempli de maniere aleatoire mais qui ont toutes et toujours un champ de depart et un champ de fin

En prenant le cas ou elle est remplie de 7 lignes
(ici champ de depart=ligne 4 donnee A) et champ de fin (ici ligne 7 donnee H)

champ1 | champ2
------------------
E | D
E | F
D | C
F | G
A | B
B | C
G | H

Je voudrais creer une table TATA qui me donne le resultat suivant
Champ1 A-B
Champ2 B-C
Champ3 C-D
Champ4 D-E
Champ5 E-F
Champ6 F-G
Champ7 G-H

A) Ca, c'est le cas ou j'ai une continuité de A a H
de sorte que si je fait un select * from TATA, je puisse voir la continuite des champs de la table TOTO
A-B;B-C;C-D;D-E;E-F;F-G;G-H

B) Ca se complique quand j'ai ligne de la table TOTO qui ne m'assure aucune liaison. Je dois quand meme creer ma table
Supposons que dans la table TOTO, je n'ai pas la ligne 3 (D,C).
Je voudrais quand meme pouvoir avoir la table TATA
de sorte que si je fait un select * from TATA, je puisse voir la continuite des champs de la table TOTO et le trou
A-B;B-C;"ANOMALIE";D-E;E-F;F-G;G-H

Si vous pouvez-m'aider a creer ce code, je pourrais alors l'adapter
à des tables remplies de X lignes et voir celles qui ont des anomalies.

Merci si vous avez le temps, c'est plutot enervant de tout gérer
Je ne peux le faire qu'en PLSQL avec curseurs et exeption mais je m'y perds.

Gilal

**salim11** · 10/11/2008, 15h47

Salut,

La caluse MODEL nous permet de définir des cubes.

Solution avec 10g( purement sql)

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
 
SELECT 'CHAMP'||POSITION LIBELLE ,CHAMP1||'-'||CHAMP2 CHAMP,CHAINE ,1 GRP
FROM (WITH TT AS
     (SELECT   CASE
                  WHEN CHAMP1 > CHAMP2
                     THEN CHAMP2
                  ELSE CHAMP1
               END CHAMP1,
               CASE
                  WHEN CHAMP2 > CHAMP1
                     THEN CHAMP2
                  ELSE CHAMP1
               END CHAMP2
          FROM TOTO
      ORDER BY CHAMP1, CHAMP2)
    SELECT CHAMP1, CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT
      FROM TT
    UNION
    SELECT *
      FROM (SELECT CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT, '-1'
              FROM TT)
     WHERE NEXT != CHAMP2 )
MODEL
RETURN UPDATED ROWS 
DIMENSION BY ( ROWNUM POSITION )
MEASURES ( CAST( CHAMP1||CHAMP2 AS VARCHAR2(4000) ) AS CHAINE , CHAMP1,CHAMP2,NEXT, 0 FLAG_ANOMALIE )IGNORE NAV
RULES
UPSERT
(   
 CHAINE[POSITION>=1] =CASE WHEN NEXT[CV()]!='-1' OR  NEXT[CV()] IS NULL THEN
                                CHAMP1[CV()]||'-'||CHAMP2[CV()] 
                           WHEN NEXT[CV()]='-1' THEN  
                                'Anomalie' END
)

Démo : cas avec anomalie
======================================================

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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
 
SQL> select * from toto;
 
CHAMP1                         CHAMP2
------------------------------ ------------------------------
E                              F
D                              C
F                              G
A                              B
B                              C
G                              H
 
6 ligne(s) sélectionnée(s).
 
SQL> edit
écrit file afiedt.buf
 
  1  SELECT   CASE
  2                    WHEN CHAMP1 > CHAMP2
  3                       THEN CHAMP2
  4                    ELSE CHAMP1
  5                 END CHAMP1,
  6                 CASE
  7                    WHEN CHAMP2 > CHAMP1
  8                       THEN CHAMP2
  9                    ELSE CHAMP1
 10                 END CHAMP2
 11            FROM TOTO
 12*       ORDER BY CHAMP1, CHAMP2
SQL> /
 
CHAMP1                         CHAMP2
------------------------------ ------------------------------
A                              B
B                              C
C                              D
E                              F
F                              G
G                              H
 
6 ligne(s) sélectionnée(s).
 
SQL> WITH TT AS
  2       (SELECT   CASE
  3                    WHEN CHAMP1 > CHAMP2
  4                       THEN CHAMP2
  5                    ELSE CHAMP1
  6                 END CHAMP1,
  7                 CASE
  8                    WHEN CHAMP2 > CHAMP1
  9                       THEN CHAMP2
 10                    ELSE CHAMP1
 11                 END CHAMP2
 12            FROM TOTO
 13        ORDER BY CHAMP1, CHAMP2)
 14      SELECT CHAMP1, CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT
 15        FROM TT
 16      UNION
 17      SELECT *
 18        FROM (SELECT CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT, '-1'
 19                FROM TT)
 20       WHERE NEXT != CHAMP2;
 
CHAMP1                         CHAMP2                         NEXT
------------------------------ ------------------------------ ------------------------------
A                              B                              B
B                              C                              C
C                              D                              E
D                              E                              -1
E                              F                              F
F                              G                              G
G                              H
 
7 ligne(s) sélectionnée(s).
 
SQL> SELECT 'CHAMP'||POSITION LIBELLE ,CHAMP1||'-'||CHAMP2 CHAMP,CHAINE ,1 GRP
  2  FROM (WITH TT AS
  3       (SELECT   CASE
  4                    WHEN CHAMP1 > CHAMP2
  5                       THEN CHAMP2
  6                    ELSE CHAMP1
  7                 END CHAMP1,
  8                 CASE
  9                    WHEN CHAMP2 > CHAMP1
 10                       THEN CHAMP2
 11                    ELSE CHAMP1
 12                 END CHAMP2
 13            FROM TOTO
 14        ORDER BY CHAMP1, CHAMP2)
 15      SELECT CHAMP1, CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT
 16        FROM TT
 17      UNION 
 18      SELECT *
 19        FROM (SELECT CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT, '-1'
 20                FROM TT)
 21       WHERE NEXT != CHAMP2 )
 22  MODEL
 23  RETURN UPDATED ROWS 
 24  DIMENSION BY ( ROWNUM POSITION )
 25  MEASURES ( CAST( CHAMP1||CHAMP2 AS VARCHAR2(4000) ) AS CHAINE , CHAMP1,CHAMP2,NEXT, 0 FLAG_ANOM
ALIE )IGNORE NAV
 26  RULES
 27  UPSERT
 28  (   
 29   CHAINE[POSITION>=1] =CASE WHEN NEXT[CV()]!='-1' OR  NEXT[CV()] IS NULL THEN
 30                                  CHAMP1[CV()]||'-'||CHAMP2[CV()] 
 31                             WHEN NEXT[CV()]='-1' THEN  
 32                                  'Anomalie' END
 33  )
 34  
SQL> /
 
LIBELLE                                            CHAMP                CHAINE                                  
-------------------------------------------------- -------------------- ----------------------------
CHAMP1                                             A-B                  A-B                                     
CHAMP2                                             B-C                  B-C                                     
CHAMP3                                             C-D                  C-D                                     
CHAMP4                                             D-E                  Anomalie                                
CHAMP5                                             E-F                  E-F                                     
CHAMP6                                             F-G                  F-G                                     
CHAMP7                                             G-H                  G-H                                     
 
7 ligne(s) sélectionnée(s).
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=12 Bytes=61
          2)
 
   1    0   SQL MODEL (ORDERED)
   2    1     COUNT
   3    2       FILTER
   4    3         VIEW (Cost=8 Card=12 Bytes=612)
   5    4           TEMP TABLE TRANSFORMATION
   6    5             LOAD AS SELECT
   7    6               SORT (ORDER BY) (Cost=4 Card=6 Bytes=204)
   8    7                 TABLE ACCESS (FULL) OF 'TOTO' (TABLE) (Cost=
          3 Card=6 Bytes=204)
 
   9    5             SORT (UNIQUE) (Cost=8 Card=12 Bytes=432)
  10    9               UNION-ALL
  11   10                 WINDOW (SORT) (Cost=4 Card=6 Bytes=204)
  12   11                   VIEW (Cost=2 Card=6 Bytes=204)
  13   12                     TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D66
          96_FC4B9C76' (TABLE (TEMP)) (Cost=2 Card=6 Bytes=204)
 
  14   10                 VIEW (Cost=3 Card=6 Bytes=228)
  15   14                   WINDOW (SORT) (Cost=3 Card=6 Bytes=204)
  16   15                     VIEW (Cost=2 Card=6 Bytes=204)
  17   16                       TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D
          6696_FC4B9C76' (TABLE (TEMP)) (Cost=2 Card=6 Bytes=204)
 
 
 
 
SQL>

=====================================================

Démo : cas sans anomalie

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
 
SQL> select * from toto;
 
CHAMP1                         CHAMP2
------------------------------ ------------------------------
E                              F
D                              C
F                              G
A                              B
B                              C
G                              H
E                              D
 
7 ligne(s) sélectionnée(s).
 
SQL> SELECT 'CHAMP'||POSITION LIBELLE ,CHAMP1||'-'||CHAMP2 CHAMP,CHAINE ,1 GRP
  2  FROM (WITH TT AS
  3       (SELECT   CASE
  4                    WHEN CHAMP1 > CHAMP2
  5                       THEN CHAMP2
  6                    ELSE CHAMP1
  7                 END CHAMP1,
  8                 CASE
  9                    WHEN CHAMP2 > CHAMP1
 10                       THEN CHAMP2
 11                    ELSE CHAMP1
 12                 END CHAMP2
 13            FROM TOTO
 14        ORDER BY CHAMP1, CHAMP2)
 15      SELECT CHAMP1, CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT
 16        FROM TT
 17      UNION
 18      SELECT *
 19        FROM (SELECT CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT, '-1'
 20                FROM TT)
 21       WHERE NEXT != CHAMP2 )
 22  MODEL
 23  RETURN UPDATED ROWS 
 24  DIMENSION BY ( ROWNUM POSITION )
 25  MEASURES ( CAST( CHAMP1||CHAMP2 AS VARCHAR2(4000) ) AS CHAINE , CHAMP1,CHAMP2,NEXT, 0 FLAG_ANOM
ALIE )IGNORE NAV
 26  RULES
 27  UPSERT
 28  (   
 29   CHAINE[POSITION>=1] =CASE WHEN NEXT[CV()]!='-1' OR  NEXT[CV()] IS NULL THEN
 30                                  CHAMP1[CV()]||'-'||CHAMP2[CV()] 
 31                             WHEN NEXT[CV()]='-1' THEN  
 32                                  'Anomalie' END
 33  )
 34  ;
 
LIBELLE                                            CHAMP                CHAINE                                  
-------------------------------------------------- -------------------- ----------------------------
CHAMP1                                             A-B                  A-B                                     
CHAMP2                                             B-C                  B-C                                     
CHAMP3                                             C-D                  C-D                                     
CHAMP4                                             D-E                  D-E                                     
CHAMP5                                             E-F                  E-F                                     
CHAMP6                                             F-G                  F-G                                     
CHAMP7                                             G-H                  G-H                                     
 
7 ligne(s) sélectionnée(s).

**salim11** · 10/11/2008, 16h17

Salut,

Afficher le résultat sur une ligne

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
SELECT 'Resultat ----> '||substr(string,2) string
FROM (SELECT 'CHAMP'||POSITION LIBELLE ,CHAMP1||'-'||CHAMP2 CHAMP,CHAINE ,1 GRP
FROM (WITH TT AS
     (SELECT   CASE
                  WHEN CHAMP1 > CHAMP2
                     THEN CHAMP2
                  ELSE CHAMP1
               END CHAMP1,
               CASE
                  WHEN CHAMP2 > CHAMP1
                     THEN CHAMP2
                  ELSE CHAMP1
               END CHAMP2
          FROM TOTO
      ORDER BY CHAMP1, CHAMP2)
    SELECT CHAMP1, CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT
      FROM TT
    UNION 
    SELECT *
      FROM (SELECT CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT, '-1'
              FROM TT)
     WHERE NEXT != CHAMP2 )
MODEL
RETURN UPDATED ROWS 
DIMENSION BY ( ROWNUM POSITION )
MEASURES ( CAST( CHAMP1||CHAMP2 AS VARCHAR2(4000) ) AS CHAINE , CHAMP1,CHAMP2,NEXT, 0 FLAG_ANOMALIE )IGNORE NAV
RULES
UPSERT
(   
 CHAINE[POSITION>=1] =CASE WHEN NEXT[CV()]!='-1' OR  NEXT[CV()] IS NULL THEN
                                CHAMP1[CV()]||'-'||CHAMP2[CV()] 
                           WHEN NEXT[CV()]='-1' THEN  
                                'Anomalie' END
)
)
MODEL
RETURN UPDATED ROWS 
partition by ( grp)
DIMENSION BY ( row_number() over(partition by grp order by champ)POSITION )
MEASURES ( libelle,champ, CAST( chaine AS VARCHAR2(3000) ) AS string )IGNORE NAV
RULES
UPSERT
ITERATE( 1000)
UNTIL ( PRESENTV(string[ITERATION_NUMBER+2],1,0) = 0)
( string[0] =string[0] || ',' || string[ ITERATION_NUMBER+1] )
ORDER BY 1

=====================================================
Démo:

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
 
SQL> SELECT 'Resultat ----> '||substr(string,2) string
  2  FROM (SELECT 'CHAMP'||POSITION LIBELLE ,CHAMP1||'-'||CHAMP2 CHAMP,CHAINE ,1 GRP
  3  FROM (WITH TT AS
  4       (SELECT   CASE
  5                    WHEN CHAMP1 > CHAMP2
  6                       THEN CHAMP2
  7                    ELSE CHAMP1
  8                 END CHAMP1,
  9                 CASE
 10                    WHEN CHAMP2 > CHAMP1
 11                       THEN CHAMP2
 12                    ELSE CHAMP1
 13                 END CHAMP2
 14            FROM TOTO
 15        ORDER BY CHAMP1, CHAMP2)
 16      SELECT CHAMP1, CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT
 17        FROM TT
 18      UNION 
 19      SELECT *
 20        FROM (SELECT CHAMP2, LEAD (CHAMP1, 1) OVER (ORDER BY CHAMP1 ASC) NEXT, '-1'
 21                FROM TT)
 22       WHERE NEXT != CHAMP2 )
 23  MODEL
 24  RETURN UPDATED ROWS 
 25  DIMENSION BY ( ROWNUM POSITION )
 26  MEASURES ( CAST( CHAMP1||CHAMP2 AS VARCHAR2(4000) ) AS CHAINE , CHAMP1,CHAMP2,NEXT, 0 FLAG_ANOM
ALIE )IGNORE NAV
 27  RULES
 28  UPSERT
 29  (   
 30   CHAINE[POSITION>=1] =CASE WHEN NEXT[CV()]!='-1' OR  NEXT[CV()] IS NULL THEN
 31                                  CHAMP1[CV()]||'-'||CHAMP2[CV()] 
 32                             WHEN NEXT[CV()]='-1' THEN  
 33                                  'Anomalie' END
 34  )
 35  )
 36  MODEL
 37  RETURN UPDATED ROWS 
 38  partition by ( grp)
 39  DIMENSION BY ( row_number() over(partition by grp order by champ)POSITION )
 40  MEASURES ( libelle,champ, CAST( chaine AS VARCHAR2(3000) ) AS string )IGNORE NAV
 41  RULES
 42  UPSERT
 43  ITERATE( 1000)
 44  UNTIL ( PRESENTV(string[ITERATION_NUMBER+2],1,0) = 0)
 45  ( string[0] =string[0] || ',' || string[ ITERATION_NUMBER+1] )
 46  ORDER BY 1
 47  /
 
STRING
----------------------------------------------------------------------------------------------------
Resultat ----> A-B,B-C,C-D,Anomalie,E-F,F-G,G-H
 
SQL>

**salim11** · 10/11/2008, 17h06

salut,

Une autre solution sans le model(9i)

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
 
SQL> select * from toto;
 
CHAMP1                         CHAMP2
------------------------------ ------------------------------
E                              F
D                              C
F                              G
A                              B
B                              C
G                              H
 
6 ligne(s) sélectionnée(s).
 
SQL> SELECT 'CHAMP' || ROWNUM champ,
  2         CASE
  3            WHEN NEXT = '-1'
  4               THEN 'Anomalie'
  5            ELSE champ1 || '-' || champ2
  6         END res
  7    FROM (WITH tt AS
  8               (SELECT champ1, champ2,
  9                       LEAD (champ1, 1) OVER (ORDER BY champ1 ASC) NEXT
 10                  FROM (SELECT   CASE
 11                                    WHEN champ1 > champ2
 12                                       THEN champ2
 13                                    ELSE champ1
 14                                 END champ1,
 15                                 CASE
 16                                    WHEN champ2 > champ1
 17                                       THEN champ2
 18                                    ELSE champ1
 19                                 END champ2
 20                            FROM toto
 21                        ORDER BY champ1, champ2))
 22          SELECT   champ1, champ2, NEXT
 23              FROM tt
 24          UNION ALL
 25          SELECT   champ2, NEXT, '-1'
 26              FROM tt
 27             WHERE NEXT != champ2
 28          ORDER BY champ1, champ2);
 
CHAMP                RES
-------------------- -------------------------------------------------------------------------
CHAMP1               A-B
CHAMP2               B-C
CHAMP3               C-D
CHAMP4               Anomalie
CHAMP5               E-F
CHAMP6               F-G
CHAMP7               G-H
 
7 ligne(s) sélectionnée(s).
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=12 Bytes=61
          2)
 
   1    0   COUNT
   2    1     VIEW (Cost=5 Card=12 Bytes=612)
   3    2       TEMP TABLE TRANSFORMATION
   4    3         LOAD AS SELECT
   5    4           WINDOW (SORT) (Cost=5 Card=6 Bytes=204)
   6    5             VIEW (Cost=4 Card=6 Bytes=204)
   7    6               SORT (ORDER BY) (Cost=4 Card=6 Bytes=204)
   8    7                 TABLE ACCESS (FULL) OF 'TOTO' (TABLE) (Cost=
          3 Card=6 Bytes=204)
 
   9    3         SORT (ORDER BY) (Cost=4 Card=12 Bytes=510)
  10    9           UNION-ALL
  11   10             VIEW (Cost=2 Card=6 Bytes=306)
  12   11               TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D66BF_FC4
          B9C76' (TABLE (TEMP)) (Cost=2 Card=6 Bytes=204)
 
  13   10             VIEW (Cost=2 Card=6 Bytes=204)
  14   13               TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D66BF_FC4
          B9C76' (TABLE (TEMP)) (Cost=2 Card=6 Bytes=204)
 
 
 
 
SQL>

Affichage des résultats du sondage: plsql sur ordonnancement d'un tableau

Ordonner un tableau à 2 dimensions

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager