Where d'une sous-requête [Résolu]

[Zandes]

Bonjour,

Puisqu'un exemple vaut 1000 mots...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
	TAB_PRINC.STATUS_DATE, 
	(SELECT 
		TAB_A.DAT_EFFEC
	FROM
		TAB_A
	WHERE
		(TAB_A.NO_ID = B.NO_ID) AND
		(TAB_A.TRANS_NO = 
			(SELECT 
				Max(TAB_B.TRANS_NO)
			FROM 
				TAB_B
			WHERE 
				(TAB_B.NO_ID = TAB_PRINC.NO_ID) 				)
		)
	)  AS DAT_DER_COTIS
FROM 
	TAB_PRINC
WHERE 
	(DAT_DER_COTIS >= {d '2010-01-01'}) AND 
	(DAT_DER_COTIS <= {d '2011-01-01'})

Je sais que normalement on ne peut pas prendre l'alias d'une colonne pour l'utiliser dans la requête. D'où l'erreur de la requête. Est-ce que ça signifie que je dois recopier la sous-requête 2 fois dans le where principal, si je veux mettre la condition dans le where de la requête principale ?

À la limite, je pourrais mettre les conditions de DAT_DER_COTIS dans la sous-requête directement, mais puisque le code est généré de façon dynamique c'est plus facile d'ajouter une condition à la fin de la requête...

Des idées ?

Merci

[CinePhil]

On dirait que tu ne connais pas les jointures !
Et ta requête est tellement tarabiscotée que je n'arrive pas à en comprendre le sens.

Quel est le but de la requête, exprimé en français ?
Quelle est la structure des tables impliquées ?
Quel est ton SGBD ?
Un petit exemple de données et le résultat attendu aide aussi à t'aider.
Bref, applique les règles de ce forum.

[Zandes]

Ouin... je me disais aussi que ça ne serait pas clair.

Voilà le même exemple (un peu plus bas) avec moins de "tarabiscotage" (c'est un mot ça ?). Je suis peut-être un peu trop paranoïaque. Les dates "en clair" sont des paramètres.

Le but est de récupérer la date de dernière cotisation (ie. date de la dernière transaction) et de l'inclure avec le dossier du transporteur.

Pour ce qui est du SGBD, j'utilise DB2, mais c'est un problème SQL alors que j'utilise lui ou un autre ça ne change pas grand chose.

Pour l'exemple de résultat, ce sera un peu difficile à te le donner...

Code :

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
 
SELECT
	PERC_MANU.NOM_PERC_TRANSP,
	DOSS_CAR.JUR_CODE,   
	DOSS_CAR.TXPYR_ID,   
	DOSS_CAR.SOLD_DROIT,   
	DOSS_CAR.SOLD_PENAL,   
	DOSS_CAR.SOLD_INTE,
	TAXPAYER.TXPYR_STATUS, 
	TAXPAYER.STATUS_DATE, 
	TAXPAYER.COD_RAIS_ANNU,
	(SELECT 
		TRANS_A.DAT_EFFEC AS DAT_DER_COTIS
	FROM
		TRANS TRANS_A
	WHERE
		(TRANS_A.TXPYR_ID = DOSS_CAR.TXPYR_ID) AND
		(TRANS_A.TRANS_NO = 
			(SELECT 
				Max(TRANS_B.TRANS_NO)
			FROM 
				TRANS TRANS_B
			WHERE 
				(TRANS_B.TXPYR_ID = DOSS_CAR.TXPYR_ID) AND
				(
					(TRANS_B.TYP_TRAN='CATOF') OR
					(TRANS_B.TYP_TRAN='NITOF')
				)
			)
		)
	)
FROM 
	PERC_MANU PERC_MANU,     
	DOSS_CAR DOSS_CAR, 
	TAXPAYER TAXPAYER
WHERE 
	(TAXPAYER.TXPYR_JUR = DOSS_CAR.JUR_CODE) AND  
	(TAXPAYER.TXPYR_ID = DOSS_CAR.TXPYR_ID) AND
	(PERC_MANU.JUR_CODE = DOSS_CAR.JUR_CODE) AND  
	(PERC_MANU.TXPYR_ID = DOSS_CAR.TXPYR_ID) AND	(DAT_DER_COTIS >= {d '2010-01-01'}) AND 
	(DAT_DER_COTIS <= {d '2011-01-01'})

Merci de ton aide.

[CinePhil]

Ce n'est pas encore très clair tout ça ! Et effectivement, tu ne connais pas la syntaxe des jointures en vigueur depuis 1992 ; il serait temps de s'y mettre !

Envoyé par Zandes

Le but est de récupérer la date de dernière cotisation (ie. date de la dernière transaction)

D'après ce que je comprends, elle se trouve quelque part dans TRANS.DAT_EFFEC ?

et de l'inclure avec le dossier du transporteur.

Qui se trouve quelque part dans DOSS_CAR ?

Comme tu n'as toujours pas donné la structure des tables concernées, ça reste un jeu de devinettes !

Est-ce que la requête suivante te donnerait la date de la dernière cotisation par dossier de transporteur ?

Code :

1
2
3
4
SELECT d.TXPYR_ID, MAX(t.DAT_EFFEC) AS DAT_DER_COTIS
FROM TRANS t
INNER JOIN DOSS_CAR d ON d.TXPYR_ID = t.TXPYR_ID
GROUP BY d.TXPYR_ID

[Zandes]

Un égal fait une jointure interne implicite, non ?

mais bon, je ne veux pas commencer ce débat ici.

Dans la requête que tu m'as donné, tu me donnes la date la plus récente de la table TRANS (ou transaction). Ce que je veux, c'est la date de la transaction la plus récente (la date de la dernière transaction de type CATOF ou NITOF. D'où la sous-requête et la sous sous-requête.

Ensuite, je veux inclure les données du transporteur (DOSS_CAR et TAXPAYER).
Mais bon, je crois que je devrai constituer la requête au complet pour chaque situation dans le programme. Car, je ne crois pas que je peux le faire à la fin de la requête simplement.

Soit,

Code :

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
SELECT
	PERC_MANU.NOM_PERC_TRANSP,
	DOSS_CAR.JUR_CODE,   
	DOSS_CAR.TXPYR_ID,   
	DOSS_CAR.SOLD_DROIT,   
	DOSS_CAR.SOLD_PENAL,   
	DOSS_CAR.SOLD_INTE,
	TAXPAYER.TXPYR_STATUS, 
	TAXPAYER.STATUS_DATE, 
	TAXPAYER.COD_RAIS_ANNU,
	(SELECT 
		TRANS_A.DAT_EFFEC AS DAT_DER_COTIS
	FROM
		TRANS TRANS_A
	WHERE
		(TRANS_A.TXPYR_ID = DOSS_CAR.TXPYR_ID) AND
		(TRANS_A.TRANS_NO = 
			(SELECT 
				Max(TRANS_B.TRANS_NO)
			FROM 
				TRANS TRANS_B
			WHERE 
				(TRANS_B.TXPYR_ID = DOSS_CAR.TXPYR_ID) AND
				(
					(TRANS_B.TYP_TRAN='CATOF') OR
					(TRANS_B.TYP_TRAN='NITOF')
				)
			)
		) AND
		(TRANS_A.DAT_EFFEC >= {d '2010-01-01'}) AND 
		(TRANS_A.DAT_EFFEC <= {d '2011-01-01'})
	)
FROM 
	PERC_MANU PERC_MANU,     
	DOSS_CAR DOSS_CAR, 
	TAXPAYER TAXPAYER
WHERE 
	(TAXPAYER.TXPYR_JUR = DOSS_CAR.JUR_CODE) AND  
	(TAXPAYER.TXPYR_ID = DOSS_CAR.TXPYR_ID) AND
	(PERC_MANU.JUR_CODE = DOSS_CAR.JUR_CODE) AND  
	(PERC_MANU.TXPYR_ID = DOSS_CAR.TXPYR_ID)

[StringBuilder]

Ben au lieu de poster 25 fois la même requête, poste la description de ton modèle des données (que sont ces tables dont nous ignorons à la fois leurs champs, leurs relations et leur rôle ?) ainsi qu'un jeu de données de test, afin qu'on comprenne ce que tu veux faire.

Ta requête est absolument imbittable, et ça doit faire 10 ans au minimum que j'ai pas vu de select dirtectement à la place d'un champ : cette écriture est systématiquement remplaçable par une jointure ou une sous-requête au niveau du from, bien plus lisible.

[punkoff]

salut,

DB2 supporte les fonctions de fenêtrage si votre version n'est pas trop vieille du coup ..

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
WITH tmp_last_trans AS (
SELECT TXPYR_ID, DAT_EFFEC, 
row_number() over(partition BY TXPYR_ID ORDER BY TRANS_NO DESC) AS rnk
FROM TRANS
WHERE (TYP_TRAN='CATOF' OR TYP_TRAN='NITOF')
AND DAT_EFFEC = '2010-01-01')
 
SELECT 
        PERC_MANU.NOM_PERC_TRANSP,
	DOSS_CAR.JUR_CODE,   
	DOSS_CAR.TXPYR_ID,   
	DOSS_CAR.SOLD_DROIT,   
	DOSS_CAR.SOLD_PENAL,   
	DOSS_CAR.SOLD_INTE,
	TAXPAYER.TXPYR_STATUS, 
	TAXPAYER.STATUS_DATE, 
	TAXPAYER.COD_RAIS_ANNU,
        tmp.DAT_EFFEC
FROM PERC_MANU PERC_MANU
INNER JOIN DOSS_CAR DOSS_CAR ON PERC_MANU.JUR_CODE = DOSS_CAR.JUR_CODE AND  PERC_MANU.TXPYR_ID = DOSS_CAR.TXPYR_ID
INNER JOIN TAXPAYER TAXPAYER ON TAXPAYER.TXPYR_JUR = DOSS_CAR.JUR_CODE AND  TAXPAYER.TXPYR_ID = DOSS_CAR.TXPYR_ID 
LEFT OUTER JOIN tmp_last_trans tmp ON tmp.TXPYR_ID = DOSS_CAR.TXPYR_ID AND tmp.rnk = 1

edit : mais bon votre clause de restriction sur la date est tellement bizarre que je ne vois pas l’intérêt de tout ce process ....

[Zandes]

À bien y penser peut-être que je peux prendre ma sous-requête et la remplacer par une jointure dans le FROM, mais celle avec le "max" je ne crois pas, sans passer par le fenêtrage.

En esssayant avec la condition dans la sous-requête, ça ne fait pas ce que je veux non plus (ne me donne pas la date, mais me donne l'enregistrement). Un "outer join" devrait être plus approprié.

Pour ce qui est du fenêtrage, je n'ai jamais fait ça, mais en grande partie ça ressemble à ce que je veux faire. Je suis pas sûr si Powerbuilder le supporte par contre... La version de DB2 que j'utilise est 9.5.

Je vais voir si je peux transformer la requête en plusieurs morceaux (le principe du fenêtrage dans le fond). Je vais devoir y repenser.

Pour ce qui est de la structure, en gros:

Clés primaires de TAXPAYER:
- TAXPAYER.TXPYR_JUR
- TAXPAYER.TXPYR_ID

Clés primaires de DOSS_CAR:
- DOSS_CAR.TXPYR_ID
- DOSS_CAR.JUR_CODE

Clés primaires de PERC_MANU:
- PERC_MANU.JUR_CODE
- PERC_MANU.TXPYR_ID

Clés primaires de TRANS:
- TRANS.JUR_CODE
- TRANS.TXPYR_ID
- TRANS.TRANS_NO

DOSS_CAR est lié avec TAXPAYER (1,1)
DOSS_CAR est lié avec TRANS(1,N)
PERC_MANU est flottant

[Zandes]

Bon j'ai essayé une autre approche axée sur les jointures. Et c'est bien un "inner join" que je veux. Je ne veux pas les enregistrements qui n'ont pas de date de dernière cotisation (TRANS.DAT_EFFEC).

Je ne sais pas trop ce qui ne fonctionne pas (peut-on faire une sous-requête dans une jointure ?) --> voir 1ère citation

EDIT RÉSOLU:

J'ai réussi à faire ce que je veux en déplacant la sous-requête de la jointure dans le where. Finalement, je m'étais pas mal compliqué la vie pour pas grand chose... --> voir 2e citation


Code en erreur:

Code :

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
SELECT
	PERC_MANU.NOM_PERC_TRANSP,
	DOSS_CAR.JUR_CODE,   
	DOSS_CAR.TXPYR_ID,   
	DOSS_CAR.SOLD_DROIT,   
	DOSS_CAR.SOLD_PENAL,   
	DOSS_CAR.SOLD_INTE,
	TAXPAYER.TXPYR_STATUS, 
	TAXPAYER.STATUS_DATE, 
	TAXPAYER.COD_RAIS_ANNU
FROM 
	A.DOSS_CAR DOSS_CAR 
		INNER JOIN A.TAXPAYER TAXPAYER ON 
			DOSS_CAR.JUR_CODE = TAXPAYER.TXPYR_JUR AND
			DOSS_CAR.TXPYR_ID = TAXPAYER.TXPYR_ID
		INNER JOIN A.PERC_MANU PERC_MANU ON
			DOSS_CAR.JUR_CODE = PERC_MANU.JUR_CODE AND
			DOSS_CAR.TXPYR_ID = PERC_MANU.TXPYR_ID
		INNER JOIN A.TRANS TRANS ON
			DOSS_CAR.JUR_CODE = TRANS.JUR_CODE AND
			DOSS_CAR.TXPYR_ID = TRANS.TXPYR_ID AND
			TRANS.TRANS_NO = (SELECT 
						Max(TRANS_B.TRANS_NO)
					FROM 
						A.TRANS TRANS_B
					WHERE 
						(TRANS_B.TXPYR_ID = DOSS_CAR.TXPYR_ID) AND
						(
							(TRANS_B.TYP_TRAN='CATOF') OR
							(TRANS_B.TYP_TRAN='NITOF')
						)
					)
WHERE												
	(TRANS.DAT_EFFEC >= {d '2010-01-01'}) AND 
	(TRANS.DAT_EFFEC <= {d '2011-01-01'})

Code Résolu:

Code :

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
SELECT
	PERC_MANU.NOM_PERC_TRANSP,
	DOSS_CAR.JUR_CODE,   
	DOSS_CAR.TXPYR_ID,   
	DOSS_CAR.SOLD_DROIT,   
	DOSS_CAR.SOLD_PENAL,   
	DOSS_CAR.SOLD_INTE,
	TAXPAYER.TXPYR_STATUS, 
	TAXPAYER.STATUS_DATE, 
	TAXPAYER.COD_RAIS_ANNU,
	TRANS.DAT_EFFEC
FROM 
	A.DOSS_CAR DOSS_CAR 
		INNER JOIN A.TAXPAYER TAXPAYER ON 
			DOSS_CAR.JUR_CODE = TAXPAYER.TXPYR_JUR AND
			DOSS_CAR.TXPYR_ID = TAXPAYER.TXPYR_ID
		INNER JOIN A.PERC_MANU PERC_MANU ON
			DOSS_CAR.JUR_CODE = PERC_MANU.JUR_CODE AND
			DOSS_CAR.TXPYR_ID = PERC_MANU.TXPYR_ID
		INNER JOIN A.TRANS TRANS ON
			DOSS_CAR.JUR_CODE = TRANS.JUR_CODE AND
			DOSS_CAR.TXPYR_ID = TRANS.TXPYR_ID			
WHERE
	TRANS.TRANS_NO = (SELECT 
				Max(TRANS_B.TRANS_NO)
			FROM
				A.TRANS TRANS_B
					INNER JOIN A.TAXPAYER TAXPAYER ON 
						TRANS_B.JUR_CODE = DOSS_CAR.JUR_CODE AND
						TRANS_B.TXPYR_ID = DOSS_CAR.TXPYR_ID
 
			WHERE 
				(TRANS_B.TYP_TRAN='CATOF') OR
				(TRANS_B.TYP_TRAN='NITOF')
			) AND												
	(TRANS.DAT_EFFEC >= {d '2000-01-01'}) AND 
	(TRANS.DAT_EFFEC <= {d '2001-01-01'})

Merci !