Discussion :

[Panthot]

Bonjour,

Je souhaiterai connaitre de quelle façon je pourrai imiter la fonction RECHERCHEV d'Excel en langage SQL sous QGIS.
Dans mon cas, j'ai besoin de sélectionner un câble (entité de ma table attributaire) ayant le même point d'origine (1e champ) que mon point d’extrémité (2e champ).


Merci d'avance,
Bonne journée.

[escartefigue]

Bonjour,

A priori une simple jointure suffit, pouvez vous communiquer un exemple de jeu d'essai en entrée (description et contenu des tables) et de résultat attendu

[Panthot]

Merci pour la réponse.

J'ai essayé les jointures et c'est un bon début. Je ne pensais pas faire une jointure dans une même table, pour moi je devais passer par le principe de boucle que le SQL peut proposer. Mais je suis obligé de faire une copie de la table pour faire ma jointure, y a t'il un moyen moins lourd pour faire de simples jointures dans la même table?

Surtout que une jointure me permet de déterminer un segment de cable supplémentaire. Or je veux connaitre TOUS les câbles de ma section.
Je m'explique : à l'aide d'une jointure, je peux déterminer une noeux supplémentaire, avec deux jointures (et donc 2 copies de table) je peux déterminer 2 noeux supplémentaire. Et ainsi de suite.
Existe t'il une façon me permettant de condensé toutes ses informations pour arriver à mon objectif final : COMBIEN AI-JE DE SECTION DIFFÉRENTES?

[escartefigue]

Il y a un souci dans l'expression de besoin issue de votre document excel, et que je copie/colle ici :

TANT QUE EXTREMITE EST EGAL A "l'ORIGINE d'une autre ligne" ET QUE "ces deux lignes ont la même CAPACITE", ALORS RESULTAT SOUHAITE EST EGAL A "RESULTAT SOUHAITE de cette autre ligne", SINON "RESULTAT SOUHAITE de cette autre ligne" +1

La 1ère partie ne pose aucun souci, la deuxième, à partir du "SINON" doit être reformulée, car comment faire "+1" sur résulat souhaité si on a pas trouvé la ligne
En d'autre termes il faut décomposer
- cas où on trouve une ligne avec extrémité trouvée = origine de la ligne en cours et capacité trouvée = capacité en cours
- cas où on trouve une ligne avec extrémité trouvée = origine de la ligne en cours et capacité trouvée <> capacité en cours
- cas où on ne trouve pas de ligne avec extrémité trouvée = origine de la ligne en cours

[Panthot]

La 1ère partie ne pose aucun souci, la deuxième, à partir du "SINON" doit être reformulée, car comment faire "+1" sur résulat souhaité si on a pas trouvé la ligne
En d'autre termes il faut décomposer
- cas où on trouve une ligne avec extrémité trouvée = origine de la ligne en cours et capacité trouvée = capacité en cours
- cas où on trouve une ligne avec extrémité trouvée = origine de la ligne en cours et capacité trouvée <> capacité en cours
- cas où on ne trouve pas de ligne avec extrémité trouvée = origine de la ligne en cours

Bien vue. Ce qu'il faudrait faire du coup c'est :
- Premier cas : = "RESULTAT SOUHAITE de cette autre ligne"
- Deuxième cas : = "RESULTAT SOUHAITE de cette autre ligne" +1
- Troisième cas : = "RESULTAT SOUHAITE de la ligne antérieur?" +1

J'ai remarqué une sorte de "cascade" dans les résultats affichés par les jointures quand les résultats souhaités sont égaux. Il devrait y avoir un moyen pour afficher un résultat identique quand les dernières valeurs affichées avant 'NULL' sont les même. Je sais pas si je suis clair ...
Un 2e EXCEL pour illustrer tout ça en pj.

Un problème persiste, si la chaine de câble est supérieur à 3, je devrai effectuer dans jointure supplémentaire sans savoir au préalable combien je dois en faire (en copiant a chaque fois une nouvelle table...).

[escartefigue]

OK pour les 2 premiers cas
Par contre, pour le 3ème, qui dit ligne "antérieure" dit critère de classement, or quel est il ?

Attention aussi, pour les 2 premiers cas, que faut il faire si l'on trouve plusieurs lignes correspondant au critère ?
Et notamment si on a deux lignes dont l'une a la même capacité et pas l'autre, ou bien n lignes toutes de capacité différentes de la ligne en cours...

[Panthot]

Il est classé par rapport au nom du câble, avec la même racine que le point d'EXTREMIE (qui peut être supérieur ou inférieur au nom du point d'ORIGINE...)
Du coup il est vrai que la colonne du résultat souhaité ne sera pas forcement trié, et l'incrémentation risque de rencontrer des problèmes ...

[escartefigue]

Est-ce que "résultat souhaité" est une colonne existante dans la table ?
Si non, ça veut dire que le résultat doit être calculé en fonction du résultat de la ligne précédente, ce qui implique une requête récursive

[Panthot]

C'est une colonne que je comptai rajouter à l'aide du calculateur de champs de QGIS. Donc non, elle n'existe pas encore ..

J'ai regardé ici ce qu'est qu'une requête récursive. Pour sûr c'est intéressant, mais ce n'est malheureusement pas de mon niveau ...

[escartefigue]

La question est surtout de savoir si cette colonne aura déjà un contenu au moment d'exécuter la requête, si oui, pas besoin de faire une requête récursive

Voici par exemple avec ce DDL de création de table (fait sur DB2, je n'ai pas QGIS, mais le principe ne change pas)

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
      (T0ID   SMALLINT NOT NULL                    
              GENERATED BY DEFAULT AS IDENTITY     
             (START WITH    1                      
              INCREMENT BY  1                      
              CACHE 20)                            
      ,T0NOM  CHAR(020)                            
              NOT NULL                             
      ,T0CAP  DECIMAL(3,0)                         
              NOT NULL                             
      ,T0PRF  CHAR(020)                            
              NOT NULL                             
      ,T0SUF  CHAR(020)                            
              NOT NULL                             
      ,T0RES  DECIMAL(3,0)                         
              NOT NULL                             
      ,CONSTRAINT T0PK                             
                  PRIMARY KEY(T0ID)                
      ,CONSTRAINT T0AK                             
                  UNIQUE(T0NOM)                    
      )

Et avec ce contenu

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
T0ID         T0NOM         T0CAP        T0PRF                T0SUF         T0RES
----- -------------------- ----- -------------------- -------------------- -----
    1 CDI-67-024-BVA-2110    36  PBO-67-024-BVA-2105  PBO-67-024-BVA-2110     1 
    2 CDI-67-024-BVA-2115    36  PBO-67-024-BVA-2110  PBO-67-024-BVA-2115     1 
    3 CDI-67-024-BVA-3005    96  PBO-67-024-BVA-3010  PBO-67-024-BVA-3005     2 
    4 CDI-67-024-BVA-3010    96  PBO-67-024-BVA-3035  PBO-67-024-BVA-3010     2 
    5 CDI-67-024-BVA-3015    12  PBO-67-024-BVA-3005  PBO-67-024-BVA-3015     3 
    6 CDI-67-024-BVA-3030    12  PBO-67-024-BVA-3035  PBO-67-024-BVA-3030     4 
    7 CDI-67-024-BVA-3035    96  SRO-67-024-BVA       PBO-67-024-BVA-3035     5 
    8 CDI-67-024-BVA-3040    24  PBO-67-024-BVA-3035  PBO-67-024-BVA-3040     6 
    9 CDI-67-024-BVA-3055    24  PBO-67-024-BVA-3040  PBO-67-024-BVA-3055     6 
   10 CDI-67-024-BVA-3065    24  PBO-67-024-BVA-3055  PBO-67-024-BVA-3065     6

Une requête qui correspond au besoin :

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
  SELECT T0.T0NOM                                                
       , T0.T0PRF                                                
       , T0.T0SUF                                                
       , CASE                                                    
             WHEN T1.T0NOM IS NOT NULL                           
                  THEN T1.T0RES                                  
             WHEN T2.T0NOM IS NOT NULL                           
                  THEN T2.T0RES+1                                
             ELSE                                                
                  SUM(T0.T0RES)                                  
                  OVER(ORDER BY T0.T0NOM, T0.T0ID                
                       ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) 
         END AS RESULTAT                                         
  FROM TT01 AS T0                                                
  LEFT OUTER JOIN TT01 AS T1                                     
    ON T1.T0SUF =T0.T0PRF                                        
   AND T1.T0CAP =T0.T0CAP                                        
  LEFT OUTER JOIN TT01 AS T2                                     
    ON T2.T0SUF =T0.T0PRF                                        
   AND T2.T0CAP<>T0.T0CAP

[Panthot]

Merci pour ta réponse escartefigue !

Mais QGIS me dit que je rencontre un problème de syntaxe : "near "OVER": syntax error". En espérant qu'il en trouvera pas d'autre vu qu'il me les affiche l'une après l'autre.
Petites questions : "TT01", "T1" et "T2" sont des tables fictives? Car je n'arrive pas bien à me les représenter...
Et dans la ligne 11 : 'RESULTAT' est censé représenter quoi exactement?

Ma table 'CABLE_OPTIQUE_ROSACE_APD_BVA':

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
id	           code	        capacite	origine	                 extremite	        nombre_sect
1	CDI-67-024-BVA-2080	36	PBO-67-024-BVA-2025	PBO-67-024-BVA-2080	None
2	CDI-67-024-BVA-2090	36	PBO-67-024-BVA-2080	PBO-67-024-BVA-2090	None
3	CDI-67-024-BVA-2025	36	PBO-67-024-BVA-2020	PBO-67-024-BVA-2025	None
4	CDI-67-024-BVA-2020	36	PEC-67-024-BVA-2005	PBO-67-024-BVA-2020	None
5	CDI-67-024-BVA-2115	36	PBO-67-024-BVA-2110	PBO-67-024-BVA-2115	None
6	CDI-67-024-BVA-2110	36	PBO-67-024-BVA-2105	PBO-67-024-BVA-2110	None
7	CDI-67-024-BVA-3065	24	PBO-67-024-BVA-3055	PBO-67-024-BVA-3065	None
8	CDI-67-024-BVA-3055	24	PBO-67-024-BVA-3040	PBO-67-024-BVA-3055	None
9	CDI-67-024-BVA-3040	24	PBO-67-024-BVA-3035	PBO-67-024-BVA-3040	None
10	CDI-67-024-BVA-3010	96	PBO-67-024-BVA-3035	PBO-67-024-BVA-3010	None
11	CDI-67-024-BVA-3005	96	PBO-67-024-BVA-3010	PBO-67-024-BVA-3005	None
12	CDI-67-024-BVA-3015	12	PBO-67-024-BVA-3005	PBO-67-024-BVA-3015	None
13	CDI-67-024-BVA-2095	96	PEC-67-024-BVA-2005	PBO-67-024-BVA-2095	None
14	CDI-67-024-BVA-2015	12	PEC-67-024-BVA-2005	PBO-67-024-BVA-2015	None
15	CDI-67-024-BVA-2005	96	SRO-67-024-BVA	        PEC-67-024-BVA-2005	None
16	CDI-67-024-BVA-1010	12	SRO-67-024-BVA	        PBO-67-024-BVA-1010	None
17	CDI-67-024-BVA-4085	288	PBO-67-024-BVA-4075	PBO-67-024-BVA-4085	None
18	CDI-67-024-BVA-4050	24	PBO-67-024-BVA-4125	PBO-67-024-BVA-4050	None
19	CDI-67-024-BVA-4125	24	PBO-67-024-BVA-4180	PBO-67-024-BVA-4125	None
20	CDI-67-024-BVA-4185	36	PBO-67-024-BVA-4180	PBO-67-024-BVA-4185	None
21	CDI-67-024-BVA-4180	36	PBO-67-024-BVA-4170	PBO-67-024-BVA-4180	None
22	CDI-67-024-BVA-4045	288	PBO-67-024-BVA-4085	PBO-67-024-BVA-4045	None
23	CDI-67-024-BVA-4115	12	PBO-67-024-BVA-4045	PBO-67-024-BVA-4115	None
24	CDI-67-024-BVA-4005	72	PBO-67-024-BVA-4100	PBO-67-024-BVA-4005	None
25	CDI-67-024-BVA-4100	72	PBO-67-024-BVA-4120	PBO-67-024-BVA-4100	None
26	CDI-67-024-BVA-4170	72	PBO-67-024-BVA-4005	PBO-67-024-BVA-4170	None
27	CDI-67-024-BVA-4105	12	PBO-67-024-BVA-4080	PBO-67-024-BVA-4105	None
28	CDI-67-024-BVA-4070	36	PBO-67-024-BVA-4080	PBO-67-024-BVA-4070	None
29	CDI-67-024-BVA-4010	12	PBO-67-024-BVA-4095	PBO-67-024-BVA-4010	None
30	CDI-67-024-BVA-4025	288	PBO-67-024-BVA-4030	PBO-67-024-BVA-4025	None
31	CDI-67-024-BVA-4080	36	PBO-67-024-BVA-4025	PBO-67-024-BVA-4080	None
32	CDI-67-024-BVA-4030	288	PBO-67-024-BVA-4040	PBO-67-024-BVA-4030	None
33	CDI-67-024-BVA-4040	288	PBO-67-024-BVA-4015	PBO-67-024-BVA-4040	None
34	CDI-67-024-BVA-4015	288	PBO-67-024-BVA-4095	PBO-67-024-BVA-4015	None
35	CDI-67-024-BVA-4120	288	PBO-67-024-BVA-4045	PBO-67-024-BVA-4120	None
36	CDI-67-024-BVA-4095	288	PBO-67-024-BVA-4120	PBO-67-024-BVA-4095	None
37	CDI-67-024-BVA-4155	24	PBO-67-024-BVA-4005	PBO-67-024-BVA-4155	None
38	CDI-67-024-BVA-5005	12	SRO-67-024-BVA	        PBO-67-024-BVA-5005	None
39	CDI-67-024-BVA-4145	24	PBO-67-024-BVA-4150	PBO-67-024-BVA-4145	None
40	CDI-67-024-BVA-4130	72	PBO-67-024-BVA-4020	PBO-67-024-BVA-4130	None
41	CDI-67-024-BVA-4035	12	PBO-67-024-BVA-4130	PBO-67-024-BVA-4035	None
42	CDI-67-024-BVA-4060	72	PBO-67-024-BVA-4130	PBO-67-024-BVA-4060	None
43	CDI-67-024-BVA-4065	72	PEC-67-024-BVA-4060	PBO-67-024-BVA-4065	None
44	CDI-67-024-BVA-4055	24	PBO-67-024-BVA-4065	PEC-67-024-BVA-4055	None
45	CDI-67-024-BVA-4110	24	PBO-67-024-BVA-4055	PBO-67-024-BVA-4110	None
46	CDI-67-024-BVA-3035	96	SRO-67-024-BVA	        PBO-67-024-BVA-3035	None
47	CDI-67-024-BVA-3030	12	PBO-67-024-BVA-3035	PBO-67-024-BVA-3030	None
48	CDI-67-024-BVA-4150	24	PBO-67-024-BVA-4155	PBO-67-024-BVA-4150	None
49	CDI-67-024-BVA-2105	36	PBO-67-024-BVA-2095	PBO-67-024-BVA-2105	None
50	CDI-67-024-BVA-4135	12	PBO-67-024-BVA-4065	PEC-67-024-BVA-4135	None
51	CDI-67-024-BVA-4020	72	PBO-67-024-BVA-4120	PBO-67-024-BVA-4020	None
52	CDI-67-024-BVA-4075	288	SRO-67-024-BVA	        PBO-67-024-BVA-4075	None

Si il faut, je peux ajouter une valeur par défaut à la colonne "nombre_sect", =1 par exemple mais je suis pas sûr que ça puisse aider...



Mon code :

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
SELECT CABLE_OPTIQUE_ROSACE_APD_BVA.code , CABLE_OPTIQUE_ROSACE_APD_BVA.origine , CABLE_OPTIQUE_ROSACE_APD_BVA.extremite ,                                              
	CASE                                                    
			WHEN T1.code IS NOT NULL                           
				THEN T1.nombre_sect                                 
			WHEN T2.code IS NOT NULL                           
				THEN T2.nombre_sect+1                                
			ELSE                                                
					SUM(CABLE_OPTIQUE_ROSACE_APD_BVA.nombre_sect)                             
					OVER(ORDER BY CABLE_OPTIQUE_ROSACE_APD_BVA.code, CABLE_OPTIQUE_ROSACE_APD_BVA.id            
							ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
         END AS RESULTAT                                         
  FROM TT01 AS CABLE_OPTIQUE_ROSACE_APD_BVA                                                
  LEFT OUTER JOIN TT01 AS T1                                     
    ON T1.extremite =CABLE_OPTIQUE_ROSACE_APD_BVA.origine                                        
   AND T1.capacite =CABLE_OPTIQUE_ROSACE_APD_BVA.capacite                                        
  LEFT OUTER JOIN TT01 AS T2                                     
    ON T2.extremite =CABLE_OPTIQUE_ROSACE_APD_BVA.origine                                        
   AND T2.capacite<>CABLE_OPTIQUE_ROSACE_APD_BVA.capacite

[escartefigue]

Merci pour ta réponse escartefigue !
Mais QGIS me dit que je rencontre un problème de syntaxe : "near "OVER": syntax error". En espérant qu'il en trouvera pas d'autre vu qu'il me les affiche l'une après l'autre.

Tous les SGBD n'ont malheureusement pas implémenté les dernières évolutions de la norme SQL, peut être QGIS (que je ne connais pas) accepte-t-il les fonctions LAG et LEAD équivalentes

Petites questions : "TT01", "T1" et "T2" sont des tables fictives? Car je n'arrive pas bien à me les représenter...

Comme la requête utilise plusieurs fois la même table, il faut utiliser des ALIAS afin de distinguer ces différentes sources, c'est ce que j'ai fait
Vous trouverez des explications détaillées sur les alias de tables ici : http://sqlpro.developpez.com/cours/sqlaz/jointures/

Et dans la ligne 11 : 'RESULTAT' est censé représenter quoi exactement?

Resultat est un ALIAS de COLONNE. Ici, comme la requête restitue une colonne calculée (calculée dans le case, 3 valeurs possibles), j'utilise un alias pour pouvoir lui donner un petit nom, c'est plus pratique mais pas indispensable.

Si il faut, je peux ajouter une valeur par défaut à la colonne "nombre_sect", =1 par exemple mais je suis pas sûr que ça puisse aider...

Non, ça ne servira pas, et du coup, la seule solution est une requête récursive. Est-ce que QGIS connait les requêtes récursives ? à vérifier dans votre doc.

[Panthot]

Merci pour toutes tes réponses. Je vais essayer de me débrouiller avec ces billes.
Pour les requêtes récursives, je vais voir se que je peux faire. N'utilisant pas du SQL approprement parlé, mais plutôt du SQLite, je redirige ma demande dans SGBD.