PC SOFT

FORUMS PROFESSIONNELS
WINDEV, WEBDEV et WINDEV Mobile

|
|
|
FR
Accueil → WINDEV 2024 → Requête complexe : UNION, NOT IN
Requête complexe : UNION, NOT IN
Débuté par r.guion, 19 sep. 2005 16:33 - 5 réponses
#1
Posté le 19 septembre 2005 - 16:33
Bonjour,

J'ai réalisé une requête complexe qui fonctionne très bien sous Accès et MySQL...Je l'ai importé sous WinDev.

L'exécution de cette requête s'effectue normalement mais les données à l'écran sont différentes après chaque éxécution. Des fois, j'ai le bon résultat.

De plus, si je remplis un champs Table avec cette requête la fenêtre plante systématiquement. (Erreur système innatendue)

Pourtant la syntaxe SQL est bonne car si deux SGBD l'affichent alors je vois pas pk elle serait mauvaise sous WinDev. De plus elle s'execute....

j'ai le même problème avec toutes mes requêtes complexes. En fait dès que j'utilise une requête complexe, c'est toujours le même problème...Cela fait planter WinDev et HyperFile...

Outre ces problèmes de requêtes, ne pensez vous pas que WinDev est vraiment truffé de bugs car depuis que nous l'utilisons, nous sommes constamment confronté à des problèmes
#2
Posté le 19 septembre 2005 - 16:54
Voici ma requête complexe :

SELECT IDRegion,
Pays,
SUM(COLA) As Equipement_Auto,
SUM(COLB) As Equipement_Soudure,
SUM(COLC) As Equipement_Garage,
SUM(COLD) As Retail_DIY,
SUM(COLE) As Teleshopping,
SUM(COLF) As Branded,
SUM(COLG) AS Outillage_Electrique,
SUM(COLH) AS Constructeur_Automobile,
SUM(COLI) AS Autre
FROM (SELECT Pays.IDRegion,
Pays.Pays,
COUNT(*) AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 1
GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM pays WHERE Pays.Pays NOT IN(
SELECT Pays.Pays,
COUNT(*) AS COLA FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 1
GROUP BY Pays.Pays) GROUP BY Pays.IDRegion, Pays.Pays)
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
COUNT(*) AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 2
GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM pays WHERE Pays.Pays NOT IN(
SELECT Pays.Pays,
COUNT(*) AS COLB FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 2
GROUP BY Pays.Pays) GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
COUNT(*) AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 3
GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM pays WHERE Pays.Pays NOT IN(
SELECT Pays.Pays,
COUNT(*) AS COLC FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 3
GROUP BY Pays.Pays) GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
COUNT(*) AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 4
GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM pays WHERE Pays.Pays NOT IN(
SELECT Pays.Pays,
COUNT(*) AS COLD FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 4
GROUP BY Pays.Pays) GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
COUNT(*) AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 5
GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM pays WHERE Pays.Pays NOT IN(
SELECT Pays.Pays,
COUNT(*) AS COLE FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 5
GROUP BY Pays.Pays) GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
COUNT(*) AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 6
GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM pays WHERE Pays.Pays NOT IN(
SELECT Pays.Pays,
COUNT(*) AS COLF FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 6
GROUP BY Pays.Pays) GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
COUNT(*) AS COLG,
0 AS COLH,
0 AS COLI
FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 7
GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM pays WHERE Pays.Pays NOT IN(
SELECT Pays.Pays,
COUNT(*) AS COLG FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 7
GROUP BY Pays.Pays) GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
COUNT(*) AS COLH,
0 AS COLI
FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 8
GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM pays WHERE Pays.Pays NOT IN(
SELECT Pays.Pays,
COUNT(*) AS COLH FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 8
GROUP BY Pays.Pays) GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
COUNT(*) AS COLI
FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 9
GROUP BY Pays.IDRegion, Pays.Pays
UNION (SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLA,
0 AS COLB,
0 AS COLC,
0 AS COLD,
0 AS COLE,
0 AS COLF,
0 AS COLG,
0 AS COLH,
0 AS COLI
FROM pays WHERE Pays.Pays NOT IN(
SELECT Pays.Pays,
COUNT(*) AS COLI FROM Personne_morale INNER JOIN Type_Personne_morale ON Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale,
Pays INNER JOIN Personne_morale ON Pays.IDPays = Personne_morale.IDPays
WHERE Type_Personne_morale.IDType = 9
GROUP BY Pays.Pays) GROUP BY Pays.IDRegion, Pays.Pays))))))))))))))))) GROUP BY IDRegion, Pays
#3
Posté le 20 septembre 2005 - 08:40
Cà c'est de la requête ! J'y vois juste un problème avec HF:
FROM (SELECT Pays.IDRegion
A ma connaissance ce n'est pas géré par HyperFile.

Frédéric.
#4
Posté le 20 septembre 2005 - 12:14
Si Si,

HyperFile éxecute très bien la requête...

Le problème c'est que lorsque je me sers de cette requête pour remplir un champs table alors il y a une erreur systeme inconnue...

Je pense que le champ table ne laisse pas le temps à la requête de s'éxecuter...Comment je peux demander au champs table d'attendre la fin de l'execution de la requête pour se remplir de celle-ci

Merci
#5
Posté le 20 septembre 2005 - 12:31
Tu peux nous donner le code qui te sert- à remplir la table ? Parce qu'il m'arrive de remplir des tables avec des requêtes SQL qui prennent parfois quelques minutes, et ce sans aucun problème. Dans mon cas j'utilise soit SQLExec+SQLTable, soit SQLExec et un parcours avec SQLAvance + TableAjoute.

Frédéric.
#6
Posté le 20 septembre 2005 - 14:54
Cette requete exécutée dans WDSQL donne quel résultat ?


"GUION Richard" <r.guion@gys.fr> a écrit dans le message de news:
432fcc5c$1@news.pcsoft.fr...

Si Si,

HyperFile éxecute très bien la requête...

Le problème c'est que lorsque je me sers de cette requête pour remplir un
champs table alors il y a une erreur systeme inconnue...

Je pense que le champ table ne laisse pas le temps à la requête de
s'éxecuter...Comment je peux demander au champs table d'attendre la fin de
l'execution de la requête pour se remplir de celle-ci

Merci
→ Revenir à WINDEV 2024