Filtrer sur le resultat d'une sous-requete

**MinsK** · 26/11/2009, 10h42

Salut a tous,

J'ai cree une requete assez grosse et complexe dont l'une des colonnes est le resultats d'une sous-requete.
Maintenant j'aimerais filtrer les resultats de ma requete en creant une condition sur le resultat de ma sous-requete.
Voici un example se rapprochant de ma requete actuelle:

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
SELECT
      --Sous-Requete1
      (SELECT T_CUST.fld_name
	FROM tbl_customer AS T_CUST
	WHERE T2.cal_customer_id = T_CUST.fld_id
      ) AS cust_name,
      --Latest Date
      (SELECT T_ORDER.fld_event_date AS date1
	FROM (SELECT fld_epc_orders_payments_id AS order_id, MAX(fld_id) AS ids
		FROM tbl_epc_orders_payments_history_list
		WHERE fld_status = 'Invoiced'
		GROUP BY order_id) AS t3,
	    tbl_epc_orders_payments_history_list AS T_ORDER
	WHERE t3.ids = T_ORDER.fld_id AND T_ORDER.fld_epc_orders_payments_id = T_STAGE.fld_id
	AND T_ORDER.fld_status = 'Invoiced'
       ) AS latest_date
FROM
      tbl_epc_orders_payments AS T1,
      tbl_epc_orders AS T2
WHERE
      T1.fld_epc_orders_id = T2.fld_id AND T1.fld_status ='Invoiced'
AND latest_date BETWEEN '2009-05-22' AND '2009-11-22'

Cette requete va retourner l'erreur suivante:

ERROR: column "latest_date" does not exist

Comment faire pour filtrer sur "latest_date"?

**CinePhil** · 26/11/2009, 11h40

En écrivant les jointures avec la syntaxe normalisée depuis 1992, ce serait plus clair !

La première sous-requête peut être remplacée par une jointure. Pour la seconde, c'est plus compliqué, d'autant plus qu'il semble manquer des choses :

Code :

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

WHERE t3.ids = T_ORDER.fld_id AND T_ORDER.fld_epc_orders_payments_id = T_STAGE.fld_id

Il manque la définition de T_STAGE !

Code :

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

1
2
3
(SELECT T_ORDER.fld_event_date AS date1
    FROM (SELECT fld_epc_orders_payments_id AS order_id, MAX(fld_id) AS ids
        FROM tbl_epc_orders_payments_history_list

Le premier SELECT demande une colonne qui n'est pas dans le second !

Voici un example se rapprochant de ma requete actuelle:

Avec la vraie requête complète, on pourrait peut-être aider plus efficacement !

Voici le début de la réécriture de la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT tc.fld_name AS cust_name,
  to.fld_event_date AS latest_date
FROM tbl_epc_orders_payments AS T1
INNER JOIN tbl_epc_orders AS T2 ON T1.fld_epc_orders_id = T2.fld_id
  INNER JOIN tbl_customer AS tc ON T2.cal_customer_id = tc.fld_id
-- manque jointure avec sous-requête 2
INNER JOIN tbl_epc_orders_payments_history_list AS to ON -- manque condition de jointure
 
 
WHERE T1.fld_status = 'Invoiced'
  AND to.fld_event_date BETWEEN '2009-05-22' AND '2009-11-22'

L'alias to remplace T_ORDER.
L'alias tc remplace T_CUST.

**MinsK** · 26/11/2009, 12h01

Desole je ne pensais pas qu'il fallait rentrer autant dans le code pour resoudre ce probleme en fait. C'est pourquoi j'ai bricole rapidement une petite requete pour essayer de montrer comment etait structure la vrai.

Bref, voici l'originale:

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
SELECT
      --Customer No
      (SELECT T_CUST.fld_company_no
	FROM tbl_companies AS T_CUST
	WHERE T_ORDERS.cal_customer_id = T_CUST.fld_id
      ) AS cust_no,
      --Project No
      T_ORDERS.cal_epc_no AS proj_no,
      --Invoice Detail
      T_STAGE.fld_stage_description AS description,
      --Invoice Value
      T_STAGE.cal_total_value AS invoice_value,
      --Group Month Year
      (SELECT to_char(T_STAGE.fld_stage_date, 'Month')||' '||to_char(T_STAGE.fld_stage_date, 'YYYY')) AS group_month,
      --Latest Date
      (SELECT T_ORDER.fld_event_date AS date1
	FROM (SELECT fld_epc_orders_payments_id AS order_id, MAX(fld_id) AS ids
		FROM tbl_epc_orders_payments_history_list
		WHERE fld_status = 'Invoiced'
		GROUP BY order_id) AS t1,
	    tbl_epc_orders_payments_history_list AS T_ORDER
	WHERE t1.ids = T_ORDER.fld_id AND T_ORDER.fld_epc_orders_payments_id = T_STAGE.fld_id
	AND T_ORDER.fld_status = 'Invoiced'
       ) AS latest_date,
      --Initial Date
      (SELECT T_ORDER.fld_due_date AS date1
	FROM (SELECT fld_epc_orders_payments_id AS order_id, MIN(fld_id) AS ids
		FROM tbl_epc_orders_payments_history_list
		WHERE fld_due_date IS NOT NULL
		GROUP BY order_id) AS t1,
	    tbl_epc_orders_payments_history_list AS T_ORDER
	WHERE t1.ids = T_ORDER.fld_id AND T_ORDER.fld_epc_orders_payments_id = T_STAGE.fld_id
	AND T_ORDER.fld_due_date IS NOT NULL
       ) AS initial_date,
      --Delta
       (SELECT DATE_PART('days',
	(SELECT T_ORDER.fld_event_date AS date1
	FROM (SELECT fld_epc_orders_payments_id AS order_id, MAX(fld_id) AS ids
		FROM tbl_epc_orders_payments_history_list
		WHERE fld_status = 'Invoiced'
		GROUP BY order_id) AS t1,
	    tbl_epc_orders_payments_history_list AS T_ORDER
	WHERE t1.ids = T_ORDER.fld_id AND T_ORDER.fld_epc_orders_payments_id = T_STAGE.fld_id
	AND T_ORDER.fld_status = 'Invoiced'
       )
	  -
	(SELECT T_ORDER.fld_due_date AS date1
	FROM (SELECT fld_epc_orders_payments_id AS order_id, MIN(fld_id) AS ids
		FROM tbl_epc_orders_payments_history_list
		WHERE fld_due_date IS NOT NULL
		GROUP BY order_id) AS t1,
	    tbl_epc_orders_payments_history_list AS T_ORDER
	WHERE t1.ids = T_ORDER.fld_id AND T_ORDER.fld_epc_orders_payments_id = T_STAGE.fld_id
	AND T_ORDER.fld_due_date IS NOT NULL
       ))
      ) AS delta,
      --Technical Authority
      (SELECT T_PERSO.fld_name
	FROM tbl_personnel AS T_PERSO, tbl_epc_personnel AS T_EPC
	WHERE T_ORDERS.fld_epc_id = T_EPC.fld_epc_id AND
      T_EPC.fld_personnel_id = T_PERSO.fld_id AND
      T_EPC.fld_role = 'Technical Authority'
      ) AS ta,
      --Notes
      T_STAGE.fld_notes AS notes,
      --Count
      (SELECT COUNT(T_ORDERS.cal_epc_no)
	FROM tbl_epc_orders_payments AS T_STAGE, tbl_epc_orders AS T_ORDERS
	WHERE T_STAGE.fld_epc_orders_id = T_ORDERS.fld_id AND T_STAGE.fld_status ='Invoiced'
      ) AS count_records,
      --Sum
      (SELECT SUM(T_STAGE.cal_total_value)
	FROM tbl_epc_orders_payments AS T_STAGE, tbl_epc_orders AS T_ORDERS
	WHERE T_STAGE.fld_epc_orders_id = T_ORDERS.fld_id AND T_STAGE.fld_status ='Invoiced'
      ) AS sum_invoices
FROM
      tbl_epc_orders_payments AS T_STAGE,
      tbl_epc_orders AS T_ORDERS
WHERE
      T_STAGE.fld_epc_orders_id = T_ORDERS.fld_id AND T_STAGE.fld_status ='Invoiced'
	AND latest_date BETWEEN '2009-02-26' AND '2009-11-22'
ORDER BY delta ASC, proj_no ASC, latest_date ASC, cust_no ASC ;

Si l'on supprime la ligne "AND latest_date BETWEEN '2009-02-26' AND '2009-11-22'", cette requete fonctionne parfaitement.
Voici la structure des 3 tables les plus importantes dans cette requete, places de la table "mere" (tbl_orders) aux "petit-enfants" (tbl_orders_payments_history_list)

**CinePhil** · 26/11/2009, 12h36

Je continuerai tout à l'heure mais voilà déjà un début de simplification :

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
SELECT co.fld_company_no AS cust_no, 
  to.cal_epc_no AS proj_no,
  ts.fld_stage_description AS description,
  ts.cal_total_value AS invoice_value,
  to_char(ts.fld_stage_date, 'Month')||' '||to_char(ts.fld_stage_date, 'YYYY')) AS group_month,
  thl.fld_event_date AS date1,
 
FROM tbl_companies AS co
INNER JOIN tbl_epc_orders AS to ON to.cal_customer_id = co.fld_id
  INNER JOIN tbl_epc_orders_payments AS ts ON ts.fld_epc_orders_id = to.fld_id
    INNER JOIN tbl_epc_orders_payments_history_list AS thl ON thl.fld_epc_orders_payments_id = ts.fld_id
 
 
WHERE ts.fld_status = 'Invoiced'
  AND latest_date BETWEEN '2009-02-26' AND '2009-11-22'
ORDER BY delta ASC, to.cal_epc_no ASC, latest_date ASC, co.fld_company_no ASC ;

De plus, si fld_status = 'Invoiced' ==> fld_due_date IS NOT NULL alors les deux sous-requêtes de recherche du MAX et du MIN fld_id de la table tbl_epc_orders_payments_history_list peuvent être réduites en une seule :

Code :

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

1
2
3
4
5
6
SELECT thl.fld_epc_orders_payments_id AS order_id, 
  MAX(thl.fld_id) AS max_ids, MIN(thl.fld_id) AS min_ids
FROM tbl_epc_orders_payments_history_list AS thl
WHERE thl.fld_status = 'Invoiced'
  AND thl.fld_due_date IS NOT NULL
GROUP BY thl.fld_epc_orders_payments_id

Voilà où j'en suis pour le moment. C'est une horreur cette requête !

**estofilo** · 26/11/2009, 13h15

Comment faire pour filtrer sur "latest_date"?

En le faisant à un étage de requête supérieure.

Voici une requête plus simple qui a le même problème:

Code :

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

1
2
=> select 1 as z where z=1;
ERROR:  column "z" does not exist

Il n'est pas possible d'appliquer une clause WHERE sur z parce que z ne va être calculé que quand la clause WHERE est vraie, et donc ça se mord la queue.

En revanche il est possible de faire

Code :

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

1
2
3
4
 
SELECT z FROM
 (select 1 as z) AS s
WHERE [condition de filtrage sur z]

**MinsK** · 26/11/2009, 13h59

Envoyé par CinePhil

De plus, si fld_status = 'Invoiced' ==> fld_due_date IS NOT NULL alors les deux sous-requêtes de recherche du MAX et du MIN fld_id de la table tbl_epc_orders_payments_history_list peuvent être réduites en une seule :

Code :

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

1
2
3
4
5
6
SELECT thl.fld_epc_orders_payments_id AS order_id, 
  MAX(thl.fld_id) AS max_ids, MIN(thl.fld_id) AS min_ids
FROM tbl_epc_orders_payments_history_list AS thl
WHERE thl.fld_status = 'Invoiced'
  AND thl.fld_due_date IS NOT NULL
GROUP BY thl.fld_epc_orders_payments_id

No ca ira pas je pense parce que dans la requete du MAX, je recherche la derniere ligne ayant pour status "Invoiced" pour ensuite en recuperer fld_event_date (je regarde pas fld_due_date).
Dans la requete du MIN, je recherche la premiere ligne (la plus vieille) ayant fld_due_date NOT NULL et je recupere cette date (je regarde pas le status).
Donc je vois pas trop comment on peut les regrouper.

Envoyé par CinePhil

Voilà où j'en suis pour le moment. C'est une horreur cette requête !

C'est clair qu'en regardant rien que le debut de ta requete ca parait bien plus clair! Il va falloir que je m'y mette aux JOIN!

Envoyé par estofilo

En le faisant à un étage de requête supérieure.

Voici une requête plus simple qui a le même problème:

Code :

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

1
2
=> select 1 as z where z=1;
ERROR:  column "z" does not exist

Il n'est pas possible d'appliquer une clause WHERE sur z parce que z ne va être calculé que quand la clause WHERE est vraie, et donc ça se mord la queue.

En revanche il est possible de faire

Code :

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

1
2
3
4
 
SELECT z FROM
 (select 1 as z) AS s
WHERE [condition de filtrage sur z]

Ah beh oui! Ca parait evident quand on la solution devant les yeux.
Je viens de tester, et ca marche tres bien avec ma requete

Merci!

CinePhil, si tu as presque termine ou le temps de termine la conversion de la requete, ca serait super. Je pourrais par la suite m'en servir de modele pour creer mes futures requetes!

En tout cas grand merci!

**CinePhil** · 26/11/2009, 14h08

No ca ira pas je pense parce que dans la requete du MAX, je recherche la derniere ligne ayant pour status "Invoiced" pour ensuite en recuperer fld_event_date (je regarde pas fld_due_date).
Dans la requete du MIN, je recherche la premiere ligne (la plus vieille) ayant fld_due_date NOT NULL et je recupere cette date (je regarde pas le status).
Donc je vois pas trop comment on peut les regrouper.

Comme j'ai vu que la condition 'invoiced' figurait dans le WHERE général et dans toutes les sous requêtes (sauf celles du MIN), je me suis dit que cette condition devait s'appliquer à toutes les lignes. Je suis donc resté sur cette hypothèse et je suis arrivé à cette requête :

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
SELECT c.fld_company_no AS cust_no,
  o.cal_epc_no AS proj_no,
  p.fld_stage_description AS description,
  p.cal_total_value AS invoice_value,
  EXTRACT(MONTH FROM p.fld_stage_date)||' '||EXTRACT(YEAR FROM p.fld_stage_date)) AS group_month,
  h1.fld_event_date AS latest_date,
  h2.fld_due_date AS initial_date
  DATEDIFF(h2.fld_due_date AS initial_date, h1.fld_event_date AS latest_date) AS delta,
  per.fld_name AS ta,
  p.fld_notes AS notes,
  COUNT(o.cal_epc_no) AS count_records,
  SUM(p.cal_total_value) AS sum_invoices
FROM tbl_companies AS c
INNER JOIN tbl_epc_orders AS o ON o.cal_customer_id = c.fld_id
  INNER JOIN tbl_epc_orders_payments AS p ON p.fld_epc_orders_id = o.fld_id
    INNER JOIN (
      SELECT fld_epc_orders_payments_id AS order_id, MAX(fld_id) AS max_ids, MIN(fld_id) AS min_ids
      FROM tbl_epc_orders_payments_history_list
      WHERE fld_status = 'Invoiced'
        AND fld_due_date IS NOT NULL
      GROUP BY order_id
    ) AS grp ON grp.fld_epc_orders_payments_id = p.fld_id
      INNER JOIN tbl_epc_orders_payments_history_list AS h1 ON h1.fld_epc_orders_payments_id = grp.max_ids
      INNER JOIN tbl_epc_orders_payments_history_list AS h2 ON h2.fld_epc_orders_payments_id = grp.min_ids
  INNER JOIN tbl_epc_personnel AS ep ON o.fld_epc_id = ep.fld_epc_id
    INNER JOIN tbl_personnel AS per ON ep.fld_personnel_id = per.fld_id
WHERE p.fld_status = 'Invoiced'
  AND h1.fld_event_date BETWEEN '2009-02-26' AND '2009-11-22'
  AND ep.fld_role = 'Technical Authority'
ORDER BY delta ASC, o.cal_epc_no ASC, h1.fld_event_date ASC, c.fld_company_no ASC ;

A tester.
Si elle fonctionne, je suppose qu'elle sera beaucoup plus rapide que l'autre car il n'y a plus qu'une seule sous-requête.

Si la condition NOT NULL et la condition Invoiced ne sont pas forcément compatibles, tu peux essayer :
- de retirer le NOT NULL du WHERE de la sous-requête et de l'ajouter en condition de jointure avec h2 ;
- de retirer le Invoiced du WHERE de la sous-requête et de l'ajouter en condition de jointure avec h1.

Au passage, j'ai normalisé l'extraction du mois et de l'année (EXTRACT) et j'ai simplifié le calcul du delta (DATEDIFF).

**MinsK** · 26/11/2009, 16h45

Merci pour la requete!
D'autant plus que je viens de m'apercevoir que l'utilisation d'un niveau supplementaire dans l'originale, ne marche pas car il me faut une colonne ayant le nombre de results ainsi qu'une colonne ayant le total des "invoice_value".

Le probleme c'est qu'avec ta requete c'est, je pense, ces meme COUNT et SUM qui posent probleme.
J'ai essaye de retoucher, comme j'ai pu, ta requete pour la debugger (DATEDIFF n'existe pas en PostgreSQL) et j'arrive a cette erreur:

ERROR: column "c.fld_company_no" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT c.fld_company_no AS cust_no,
^

Voici la requete actuelle:

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
SELECT c.fld_company_no AS cust_no,
  o.cal_epc_no AS proj_no,
  p.fld_stage_description AS description,
  p.cal_total_value AS invoice_value,
  EXTRACT(MONTH FROM p.fld_stage_date)||' '||EXTRACT(YEAR FROM p.fld_stage_date) AS group_month,
  h1.fld_event_date AS latest_date,
  h2.fld_due_date AS initial_date,
  (SELECT DATE_PART('days',(h2.fld_due_date - h1.fld_event_date))) AS delta,
  per.fld_name AS ta,
  p.fld_notes AS notes,
  COUNT(o.cal_epc_no) AS count_records,
  SUM(p.cal_total_value) AS sum_invoices
FROM tbl_companies AS c
INNER JOIN tbl_epc_orders AS o ON o.cal_customer_id = c.fld_id
  INNER JOIN tbl_epc_orders_payments AS p ON p.fld_epc_orders_id = o.fld_id
    INNER JOIN (
      SELECT fld_epc_orders_payments_id AS order_id, MAX(fld_id) AS max_ids, MIN(fld_id) AS min_ids
      FROM tbl_epc_orders_payments_history_list
      WHERE fld_status = 'Invoiced'
        AND fld_due_date IS NOT NULL
      GROUP BY order_id
    ) AS grp ON grp.order_id = p.fld_id
      INNER JOIN tbl_epc_orders_payments_history_list AS h1 ON h1.fld_epc_orders_payments_id = grp.max_ids
      INNER JOIN tbl_epc_orders_payments_history_list AS h2 ON h2.fld_epc_orders_payments_id = grp.min_ids
  INNER JOIN tbl_epc_personnel AS ep ON o.fld_epc_id = ep.fld_epc_id
    INNER JOIN tbl_personnel AS per ON ep.fld_personnel_id = per.fld_id
WHERE p.fld_status = 'Invoiced'
  AND h1.fld_event_date BETWEEN '2009-02-26' AND '2009-11-22'
  AND ep.fld_role = 'Technical Authority'
ORDER BY delta ASC, o.cal_epc_no ASC, h1.fld_event_date ASC, c.fld_company_no ASC ;

Filtrer sur le resultat d'une sous-requete

Requêtes PostgreSQL

Discussions similaires

Partager

Partager