|
| Problème requête avec champs table |
| Iniciado por GUION Richard, 20,sep. 2005 12:20 - 3 respuestas |
| |
| | | |
|
| |
Publicado el 20,septiembre 2005 - 12:20 |
Bonjour,
J'ai une requête complexe en code SQL qui s'éxecute correctement en mode requête. Je peux même faire une rétro analyse de cette requête...
Cependant lorsque je me sers de cette requête pour remplir un champs table, j'ai une erreur système innatendue...
J'ai retravailler ma requete pour alléger le code mais avec des syntaxes différentes c'est la même chose
Je commence à croire que la table ne laisse pas assez de temps à la requête pour s'éxecuter...
Voici le code de ma requête mais je doute que vous allez saisir :
SELECT IDRegion,
Pays,
SUM(COLONNEA) AS SCOLONNEA,
SUM(COLONNEB) AS SCOLONNEB,
SUM(COLONNEC) AS SCOLONNEC,
SUM(COLONNED) AS SCOLONNED,
SUM(COLONNEE) AS SCOLONNEE,
SUM(COLONNEF) AS SCOLONNEF,
SUM(COLONNEG) AS SCOLONNEG,
SUM(COLONNEH) AS SCOLONNEH,
SUM(COLONNEI) AS SCOLONNEI
FROM (
SELECT Pays.IDRegion,
Pays.Pays,
COUNT(*) AS COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
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, COLONNEB, COLONNEC, COLONNED, COLONNEE, COLONNEF, COLONNEG, COLONNEH, COLONNEI
UNION (
SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
FROM Pays
WHERE Pays.Pays NOT IN (
SELECT Pays.Pays
FROM Personne_morale, Type_Personne_morale, Pays
WHERE Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale
AND Pays.IDPays = Personne_morale.IDPays
AND Type_Personne_morale.IDType = 1))
UNION (
SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLONNEA,
COUNT(*) AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
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 COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
FROM Pays
WHERE Pays.Pays NOT IN (
SELECT Pays.Pays
FROM Personne_morale, Type_Personne_morale, Pays
WHERE Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale
AND Pays.IDPays = Personne_morale.IDPays
AND Type_Personne_morale.IDType = 2))
UNION (
SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLONNEA,
0 AS COLONNEB,
COUNT(*) AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
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 COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
FROM Pays
WHERE Pays.Pays NOT IN (
SELECT Pays.Pays
FROM Personne_morale, Type_Personne_morale, Pays
WHERE Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale
AND Pays.IDPays = Personne_morale.IDPays
AND Type_Personne_morale.IDType = 3))
UNION (
SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
COUNT(*) AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
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 COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
FROM Pays
WHERE Pays.Pays NOT IN (
SELECT Pays.Pays
FROM Personne_morale, Type_Personne_morale, Pays
WHERE Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale
AND Pays.IDPays = Personne_morale.IDPays
AND Type_Personne_morale.IDType = 4))
UNION (
SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
COUNT(*) AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
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 COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
FROM Pays
WHERE Pays.Pays NOT IN (
SELECT Pays.Pays
FROM Personne_morale, Type_Personne_morale, Pays
WHERE Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale
AND Pays.IDPays = Personne_morale.IDPays
AND Type_Personne_morale.IDType = 5))
UNION (
SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
COUNT(*) AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
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 COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
FROM Pays
WHERE Pays.Pays NOT IN (
SELECT Pays.Pays
FROM Personne_morale, Type_Personne_morale, Pays
WHERE Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale
AND Pays.IDPays = Personne_morale.IDPays
AND Type_Personne_morale.IDType = 6))
UNION (
SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
COUNT(*) AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
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 COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
FROM Pays
WHERE Pays.Pays NOT IN (
SELECT Pays.Pays
FROM Personne_morale, Type_Personne_morale, Pays
WHERE Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale
AND Pays.IDPays = Personne_morale.IDPays
AND Type_Personne_morale.IDType = 7))
UNION (
SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
COUNT(*) AS COLONNEH,
0 AS COLONNEI
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 COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
FROM Pays
WHERE Pays.Pays NOT IN (
SELECT Pays.Pays
FROM Personne_morale, Type_Personne_morale, Pays
WHERE Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale
AND Pays.IDPays = Personne_morale.IDPays
AND Type_Personne_morale.IDType =  )
UNION (
SELECT Pays.IDRegion,
Pays.Pays,
0 AS COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
COUNT(*) AS COLONNEI
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 COLONNEA,
0 AS COLONNEB,
0 AS COLONNEC,
0 AS COLONNED,
0 AS COLONNEE,
0 AS COLONNEF,
0 AS COLONNEG,
0 AS COLONNEH,
0 AS COLONNEI
FROM Pays
WHERE Pays.Pays NOT IN (
SELECT Pays.Pays
FROM Personne_morale, Type_Personne_morale, Pays
WHERE Personne_morale.IDPersonne_morale = Type_Personne_morale.IDPersonne_morale
AND Pays.IDPays = Personne_morale.IDPays
AND Type_Personne_morale.IDType = 9
))))))))))) GROUP BY IDRegion, Pays |
| |
| |
| | | |
|
| | |
| |
Publicado el 20,septiembre 2005 - 15:28 |
Essayez de passer par des req intermédiaire WD plutôt que de faire une méga requête imbriquée en SQL. Cette req, bien que concevable en SQL, doit pouvoir être réduite en passant éventuellement par des tables de travail intermédiaires ou des tableaux ...
Cordialement |
| |
| |
| | | |
|
| | |
| |
Publicado el 20,septiembre 2005 - 15:30 |
Rebonjour,
Deuxième solution, si la requête fonctionne, c'est de l'executer et de la parcourir pour remplir en prog la table avec tableAjoute
J'espère que j'ai pu vous aider
Eric DETREZ |
| |
| |
| | | |
|
| | |
| |
Publicado el 21,septiembre 2005 - 08:40 |
bonjour,
Tout d'abord, merci pour votre aide...Cependant, lorsque j'essaye de découper ma requête avec des sous requêtes, WinDev me dit qu'il n'arrive pas à trouver les sous requêtes....
Ensuite si j'essaye d'excuter la requête avec ExecuteRequeteSQL puis de remplir la table par programmation alors seul 17 lignes blanches sont ajoutées...
En clair, par tous les moyens, WinDev est incapable d'éxecuter cette requête hors du testeur de requête...
Le problème c'est que je ne peux pas renoncer à cette requete car c'est une part importante de mon projet.... |
| |
| |
| | | |
|
| | | | |
| | |
|