[Oracle 7.3.4/SQL] Problème de sélection de première ligne de résultat(s) avec ROWNUM

**David.Schris** · 16/08/2006, 10h36

Bonjour,

Soient les deux tables suivantes :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
+------------+
|   table1   |
+------------+
| t1_champ1  |    +----------------+
| t1_champ2  |    | table_versions |
| t1_champ3  |    +----------------+
| t1_version-=----=---tv_version   |
+------------+    |   tv_date      |
                  +----------------+

Si je lance la requête suivante...

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
	t1.t1_champ3,
	tv.tv_date,
	tv.tv_version
FROM
	table1 t1,
	table_versions tv
WHERE
	t1.t1_champ1='XXXXX'
	AND
	t1.t1_champ2=1
	AND
	t1.t1_version=tv.tv_version
	AND
	tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
ORDER BY
	tv.tv_version DESC;

...tout se passe pour le mieux et j'obtiens :

Code :

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

1
2
3
4
5
6
7
 
+-----------+----------+------------+
| t1_champ3 |  tv_date | tv_version |
+-----------+----------+------------+
|     16    | 01/09/05 |      2     |
|     48    | 31/03/05 |      1     |
+-----------+----------+------------+

Ne voulant que la première ligne de ce qui est retourné, je me suis dit : "Facile, il suffit d'utiliser ROWNUM comme expliqué dans la FAQ".
Donc j'ai essayé la requête suivante :

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
 
SELECT
	*
FROM
	(
		SELECT
			t1.t1_champ3,
			tv.tv_date,
			tv.tv_version
		FROM
			table1 t1,
			table_versions tv
		WHERE
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
			AND
			t1.t1_version=tv.tv_version
			AND
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
		ORDER BY
			tv.tv_version DESC
	)
WHERE
	ROWNUM=1;

Problème, j'obtiens le message d'erreur suivant :

Code :

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

1
2
3
4
5
 
    ORDER BY v.version DESC
    *
ERREUR à la ligne 20 :
ORA-00907: Parenthèse de droite absente

Or, il ne manque pas de parenthèse.

Qu'ai-je donc mal fait ?

Merci d'avance pour votre aide

Cordialement,
DS.

PS : j'ai essayé sans le "ORDER BY" et, là, j'obtiens "commande inconnue ")" - reste de la ligne ignoré." et "commande inconnue au début de "WHERE rown..." - le reste de la ligne est ignoré."...

**McM** · 16/08/2006, 11h18

Pour une base 8 ça marcherait bien, pour une 7.3.4 je sais pas.

Essaye un select FROM select tout simple pour voir si ça passe

**nuke_y** · 16/08/2006, 11h58

Essaye ça :

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
SELECT * FROM
( SELECT
			t1.t1_champ3,
			tv.tv_date,
			tv.tv_version
		FROM
			table1 t1,
			table_versions tv
		WHERE
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
			AND
			t1.t1_version=tv.tv_version
			AND
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
		ORDER BY
			tv.tv_version DESC)
WHERE ROWNUM=1;

**David.Schris** · 17/08/2006, 12h00

Bonjour,

Tout d'abord, merci pour vos réponses.

Revenons dans le vif du sujet...

Envoyé par nuke_y

[...]

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 * FROM
( SELECT
			t1.t1_champ3,
			tv.tv_date,
			tv.tv_version
		FROM
			table1 t1,
			table_versions tv
		WHERE
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
			AND
			t1.t1_version=tv.tv_version
			AND
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
		ORDER BY
			tv.tv_version DESC)
WHERE ROWNUM=1;

Désolé, mais je ne vois pas de différence (mis à part les positions des sauts de ligne) entre ce que tu me proposes et la requête ne fonctionnant pas citée dans mon premier message.
Par acquis de conscience, j'ai réessayé (avec trois clients différents) et, la requête étant la même, rien n'y fait.

Envoyé par McM

Essaye un select FROM select tout simple pour voir si ça passe

Ca passe.
J'ai même fait plus qu'un "select FROM select tout simple" et je vous en donne, ci-après, une liste (non exhaustive).
Dans les exemples qui suivent, "fonctionner" veut dire "aucune erreur signalée par Oracle" mais ne veut pas dire "fait ce que je veux" et "ne pas fonctionner" veut dire "produit la même erreur que celle mentionnée dans mon premier message".

Fonctionne :

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
 
SELECT
	*
FROM
	(
		SELECT
			t1.t1_champ3,
			tv.tv_date,
			tv.tv_version
		FROM
			table1 t1,
			table_versions tv
		WHERE
			t1.t1_version=tv.tv_version
			AND
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
			AND
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
	);

Ne fonctionne 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
 
SELECT
	*
FROM
	(
		SELECT
			t1.t1_champ3,
			tv.tv_date,
			tv.tv_version
		FROM
			table1 t1,
			table_versions tv
		ORDER BY
			tv.tv_version DESC
	);

Fonctionne :

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
 
SELECT
	*
FROM
	(
		SELECT
			t1.t1_champ3,
			tv.tv_date,
			tv.tv_version
		FROM
			table1 t1,
			table_versions tv
		WHERE
			t1.t1_version=tv.tv_version
			AND
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
			AND
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
	)
ORDER BY
	version DESC;

Ne fonctionne 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
21
22
23
24
25
26
27
28
29
30
 
SELECT
	*
FROM
	(
		SELECT
			*
		FROM
			(
				SELECT
					t1.t1_champ3,
					tv.tv_date,
					tv.tv_version
				FROM
					table1 t1,
					table_versions tv
				WHERE
					t1.t1_version=tv.tv_version
					AND
					t1.t1_champ1='XXXXX'
					AND
					t1.t1_champ2=1
					AND
					tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
			)
		ORDER BY
			version DESC
	)
WHERE
	ROWNUM=1;

Fonctionne :

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
 
SELECT
	*
FROM
	(
		SELECT
			t1.t1_champ3,
			tv.tv_date,
			tv.tv_version
		FROM
			table1 t1,
			table_versions tv
		WHERE
			t1.t1_version=tv.tv_version
			AND
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
			AND
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
	)
WHERE
	ROWNUM=1;

Fonctionne :

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
 
SELECT
	*
FROM
	(
		SELECT
			*
		FROM
			(
				SELECT
					t1.t1_champ3,
					tv.tv_date,
					tv.tv_version
				FROM
					table1 t1,
					table_versions tv
				WHERE
					t1.t1_version=tv.tv_version
					AND
					t1.t1_champ1='XXXXX'
					AND
					t1.t1_champ2=1
					AND
					tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
			)
	);

Ne fonctionne 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
21
22
23
24
25
26
27
28
 
SELECT
	*
FROM
	(
		SELECT
			*
		FROM
			(
				SELECT
					t1.t1_champ3,
					tv.tv_date,
					tv.tv_version
				FROM
					table1 t1,
					table_versions tv
				WHERE
					t1.t1_version=tv.tv_version
					AND
					t1.t1_champ1='XXXXX'
					AND
					t1.t1_champ2=1
					AND
					tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
			)
		ORDER BY
			version DESC
	);

Ne fonctionne 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
21
22
23
24
25
26
27
28
 
SELECT
	*
FROM
	(
		SELECT
			*
		FROM
			(
				SELECT
					t1.t1_champ3,
					tv.tv_date,
					tv.tv_version
				FROM
					table1 t1,
					table_versions tv
				WHERE
					t1.t1_version=tv.tv_version
					AND
					t1.t1_champ1='XXXXX'
					AND
					t1.t1_champ2=1
					AND
					tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
				ORDER BY
					version DESC
			)
	);

Fonctionne :

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
	x.*,
	tv.tv_version
FROM
	(
		SELECT
			*
		FROM
			table1 t1
		WHERE
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
	) x,
	table_versions tv
WHERE
	x.version=tv.tv_version;

Fonctionne :

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
	x.*,
	tv.tv_version
FROM
	(
		SELECT
			*
		FROM
			table1 t1
		WHERE
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
	) x,
	table_versions tv
WHERE
	x.version=tv.tv_version
ORDER BY
	tv.tv_version DESC;

Ne fonctionne 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
21
22
23
24
25
 
SELECT
	*
FROM
	(
		SELECT
			x.*,
			tv.tv_version
		FROM
			(
				SELECT
					*
				FROM
					table1 t1
				WHERE
					t1.t1_champ1='XXXXX'
					AND
					t1.t1_champ2=1
			) x,
			table_versions tv
		WHERE
			x.version=tv.tv_version
		ORDER BY
			tv.tv_version DESC
	);

Fonctionne :

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
 
SELECT
	x.*
FROM
	(
		SELECT
			*
		FROM
			table1 t1
		WHERE
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
	) x,
	table_versions tv
WHERE
	x.version=tv.tv_version
	AND
	tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
ORDER BY
	x.version DESC;

Ne fonctionne 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
21
22
23
24
25
26
 
SELECT
	*
FROM
	(
		SELECT
			x.*
		FROM
			(
				SELECT
					*
				FROM
					table1 t1
				WHERE
					t1.t1_champ1='XXXXX'
					AND
					t1.t1_champ2=1
			) x,
			table_versions tv
		WHERE
			x.version=tv.tv_version
			AND
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
		ORDER BY
			x.version DESC
	);

J'ai trouvé une façon d'obtenir le résultat que je voulais sans utiliser ROWNUM, mais cette solution ne me satisfait pas :

cela ne m'explique pas pourquoi mes essais (utilisant ROWNUM) ne fonctionnent pas ;
cela ne me dit pas si une solution utilisant ROWNUM existe;
une partie des tests doivent être faits dans la requête ET dans la sous-requête (et je trouve cela "moche").

Cette requête, n'utilisant pas ROWNUM, est la suivante :

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
 
SELECT
	t1.*
FROM
	table1 t1
WHERE
	t1.t1_version=(
		SELECT
			Max(tv.tv_version)
		FROM
			table_versions tv,
			table1 t1
		WHERE
			t1.t1_version=tv.tv_version
			AND
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
			AND
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
	)
	AND
	t1.t1_champ1='XXXXX'
	AND
	t1.t1_champ2=1;

Je considère, jusqu'à preuve qu'il n'y a pas de meilleure solution, que c'est un bricolage temporaire et suis donc toujours à l'écoute d'éventuelles propositions.
Si la preuve est faite qu'il n'y a pas de solution avec ROWNUM pour Oracle 7.3.4, il pourrait être bon de glisser un mot à ce sujet dans la FAQ, voire de proposer une méthode "générique" (<--utopie) permettant de construire une requête équivalente à celle(s) qui y est/sont proposée(s).
Enfin, ce n'est pas ROWNUM qui pose problème puisque, comme on peut le voir dans les exemples donnnés plus haut, les requêtes ne fonctionnant pas ne l'utilisent pas forcément : apparemment, c'est la présence d'un "ORDER BY" dans une sous-requête qui n'est pas appréciée par Oracle.

Cordialement,
DS.

**plaineR** · 17/08/2006, 12h13

D'après tes exemples ce que je vois c'est dès que tu mets order by dans ton sous-select cela ne fonctionne plus.

Ton sous-select étant une vue virtuelle, pourquoi ne pas créer une vue réelle :

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
create view vue1 as 
SELECT
	t1.t1_champ3,
	tv.tv_date,
	tv.tv_version
FROM
	table1 t1,
	table_versions tv
WHERE
	t1.t1_champ1='XXXXX'
	AND
	t1.t1_champ2=1
	AND
	t1.t1_version=tv.tv_version
	AND
	tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
ORDER BY
	tv.tv_version DESC;

et exécuter la requête suivante :

Code :

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

select * from ma vue where rownum = 1;

**nuke_y** · 17/08/2006, 13h57

Je t'ai demandé de tester avec une autre mise en forme parce qu'il m'est déjà arrivé d'avoir ce genre d'erreur pour une histoire de mise en forme. En mettant tout sur une seule ligne ça marchait.

Par contre je n'étais pas en 7.

**Oraman** · 17/08/2006, 14h17

Bonjour,
c'est quoi exactement ton numero de version tu peux le savoir avec la commande sous sqlplus

Code :

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

show release

dans ton message de depart tu obtient le message suivant :

Code :

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

1
2
3
4
 ORDER BY v.version DESC
    *
ERREUR à la ligne 20 :
ORA-00907: Parenthèse de droite absente

alors que dans ta requête tu as :

Code :

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

1
2
ORDER BY
			tv.tv_version DESC

c'est pas un pb de mise en forme ça !!
d'ailleurj'ai fais le test avec ta même requête de depart ça marche parfaitement sans aucun message d'erreur

**nuke_y** · 17/08/2006, 14h25

Imaginons que ce soit le TO_DATE qui pose un problème dans cette version. Que donne ceci :

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
SELECT
	*
FROM
	(
		SELECT
			t1.t1_champ3,
			tv.tv_date,
			tv.tv_version
		FROM
			table1 t1,
			table_versions tv
		WHERE
			t1.t1_champ1='XXXXX'
			AND
			t1.t1_champ2=1
			AND
			t1.t1_version=tv.tv_version
			AND
			tv.tv_date<'01/06/2006'
		ORDER BY
			tv.tv_version DESC
	)
WHERE
	ROWNUM=1;

**bouyao** · 17/08/2006, 16h57

Cest normal que vous avez l'erreur car :
Avant 8.1 on ne peut pas utiliser ORDER BY dans une vue.

**nuke_y** · 17/08/2006, 17h25

Certes mais :

PS : j'ai essayé sans le "ORDER BY" et, là, j'obtiens "commande inconnue ")" - reste de la ligne ignoré." et "commande inconnue au début de "WHERE rown..." - le reste de la ligne est ignoré."...

Un 2e problème dans ce cas ?

**links** · 17/08/2006, 19h15

juste une suggestion, au cas où ça pourait aider, pourquoi ne pas utiliser :

select max(tv.tv_date)
....

**laurentschneider** · 18/08/2006, 09h33

Envoyé par links

juste une suggestion, au cas où ça pourait aider, pourquoi ne pas utiliser :

select max(tv.tv_date)
....

c'est vrai, dans Oracle7, il est impossible d'employer order by dans une sous requête ou dans une vue, il faut donc faire

Code :

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

1
2
 
select * from t where date in (select max(date) from t) and rownum=1;

le and rownum=1 est facultatif, c'est pour éviter d'obtenir plus d'une ligne

**remi4444** · 18/08/2006, 10h20

Le rownum, ce n'est pas du sql puisque ça donne potentiellement un résultat aléatoire (dépendant du hazard de l'implémentation des données). C'est juste un raccourci pratique pour les dba et développeurs "impurs" que nous sommes

.

Attention il y a plein de piege à l'utiliser (par ex un rownum dans une sous-requête porte-t-il sur les resultat de la sous requête ou sur le curseur général ?)

La requête avec le max est plus pure, pour la rendre moins "moche" il ne faut pas répetter 2 fois les critères mais faire une vrai auto-jointure avec 2 alias différents. Je verrais bien un truc du genre:

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
 
SELECT
	t1.*
FROM
	table1 t1
WHERE
	t1.t1_version=(
		SELECT
			Max(tv.tv_version)
		FROM
			table_versions tv,
			table1 t1_bis
		WHERE
			t1_bis.t1_champ1=t1.t1_champ1
			AND
			t1_bis.t1_champ2=t1.t1_champ2
			AND
			t1_bis.t1_version=tv.tv_version
			AND
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
	)
	AND
	t1.t1_champ1='XXXXX'
	AND
	t1.t1_champ2=1;

Question plan d'exécution, il n'y a pas de soucis si les champs de la jointure sont bien indexés, oracle sais trés bien gérer ce genre de requête.

Je ne connais pas le fonctionnel mais il me semble qu'il y a encore plus simple, s'il s'agit de juste d'avoir une ligne représentant la "derniere version" contenue dans une autre table:

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
	t1.*
FROM
	table1 t1
WHERE
	t1.t1_version=(
		SELECT
			Max(tv.tv_version)
		FROM
			table_versions tv
		WHERE
			tv.tv_date<To_Date('01/06/2006','DD/MM/YYYY')
	)
	AND
	t1.t1_champ1='XXXXX'
	AND
	t1.t1_champ2=1;

**laurentschneider** · 18/08/2006, 11h34

Envoyé par remi4444

Le rownum, ce n'est pas du sql puisque ça donne potentiellement un résultat aléatoire (dépendant du hazard de l'implémentation des données)

non non

Envoyé par SQL Reference ROWNUM

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:

Code :

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

1
2
3
4
 
SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;

**orafrance** · 18/08/2006, 11h42

Envoyé par remi4444

par ex un rownum dans une sous-requête porte-t-il sur les resultat de la sous requête ou sur le curseur général ?

sur la sous-requête... ça ne fait aucun doute

**remi4444** · 18/08/2006, 11h43

non non

si si, en partie du moins...

imagine par exemple que tu ais dans ta table 50 fois le meme employee_id à 0 avec des données diverses dans les autres colonnes.

Selon la base de donnée, l'ordre d'insertion etc... les 11 lignes ramenées ne seront pas les mêmes...

**orafrance** · 18/08/2006, 11h50

en utilisant une sous-requête tel qu'il le montre il n'y a aucun problème, idem si tu as un index sur la colonne sur laquel tu veux ordonner le résultat

**remi4444** · 18/08/2006, 11h50

Envoyé par Fred_D

sur la sous-requête... ça ne fait aucun doute

eh bien moi j'en suis pas si sur...

En tout cas je me suis déja fait méga piégé avec ce genre de choses, ça m'a calmé pour un moment. Maintenant je n'utilise les ROWNUM qu'au dernier niveau pour des problématiques de pagination, je les évite au maximum pour la selection...

**remi4444** · 18/08/2006, 11h57

Envoyé par Fred_D

en utilisant une sous-requête tel qu'il le montre il n'y a aucun problème, idem si tu as un index sur la colonne sur laquel tu veux ordonner le résultat

un index oui, mais un index unique sinon il y aura une part d'aléatoire

De toutes façons je trouve que pour l'exemple qui nous interresse, et si je l'ai bien saisi, utiliser le max s'avère plus simple et surtout plus juste d'un point de vue fonctionnel. Le rownum c'est du technique, c'est pas tout à fait la même chose.

ok je tiens un discours de puriste, j'avoue que c'est souvent bien pratique le rownum...