Discussion :

[pjcejbpojo]

Salut TLMONDE
J'ai une colle pour vous amateurs de sql.
J'aimerais savoir si il est possible de compléter une requête sql qui renvoie n enregistrements en m enregistrements avec m>n.
Les (m-n) enregistrements qui complètent la requête doivent être nuls.

[Pomalaix]

Salut TLMONDE
J'ai une colle pour vous amateurs de sql.
J'aimerais savoir si il est possible de compléter une requête sql qui renvoie n enregistrements en m enregistrements avec m>n.
Les (m-n) enregistrements qui complètent la requête doivent être nuls.

Oui, pourquoi pas, il suffit d'utiliser un UNION ALL avec des NULL.

Imaginons que vous voulez systématiquement 5 lignes nulles :

Code :

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

1
2
3
4
5
6
7
select last_name, first_name
from hr.employees
where last_name like 'C%'
union all
select null, null 
from hr.employees
where rownum < 6;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Cabrio    Anthony
Cambrault    Gerald
Cambrault    Nanette
Chen    John
Chung    Kelly
Colmenares    Karen
(null)    (null)
(null)    (null)
(null)    (null)
(null)    (null)
(null)    (null)

[Médiat]

Je ferais plutôt :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT *
FROM (SELECT Last_Name, First_Name
      FROM Employees
    UNION ALL
      SELECT NULL, NULL
      FROM dual 
      CONNECT BY rownum <= 10
    ORDER BY 1, 2)
WHERE rownum <= 10

Le ORDER BY 1, 2 est là pour garantir que les lignes "pleines" seront prises en premier (mais dans le cas d'un UNION ALL c'est le cas, même sans le ORDER BY)

[laurentschneider]

Je ferais plutôt :

Code :

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

1
2
3
      SELECT NULL, NULL
      FROM dual 
      CONNECT BY rownum <= 10

moi je ne ferais jamais ça ! outre le fait que le nombre de lignes retournées n'est pas constant

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
 
SQL> select version from v$instance;
VERSION
-----------------
10.1.0.3.0
SQL> SELECT NULL, NULL FROM dual CONNECT BY rownum <= 10;
N N
- -
 
 
 
 
 
 
11 rows selected.

il est aussi illégal d'employer CONNECT BY sans clause PRIOR.

Pour revenir au problème de l'utilisateur, d'où vient "m" ? Si c'est une variable, alors tu peux programmer une fonction PL/SQL qui retourne une table. Si m est le nombre de lignes dans une autre requête, alors un outer join fera sûrement l'affaire

[Médiat]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SQL> select version from v$instance;
VERSION
-----------------
9.2.0.1.0
SQL> SELECT NULL, NULL FROM dual CONNECT BY rownum <= 10;
N N
- -
 
 
 
 
 
10 ligne(s) sélectionnée(s).

il est aussi illégal d'employer CONNECT BY sans clause PRIOR

Et qu'est-ce que je risque si tu me dénonces ? J'avoue que je ne comprends pas bien cet argument...

[laurentschneider]

pas grand chose

mais ce n'est pas propre ... et ce n'est pas censé fonctionner.

PRIOR
In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator

une hiérarchie connect by ne devrait pas avoir de boucles, et si boucle il y a, la requête devrait retourner ORA-01436: CONNECT BY loop in user data

le fait que la requête retourne 10 lignes dans 9iR2, 11 lignes dans 10gR1 et 10 lignes dans 10gR2 atteste de sa non-fiabilité. Cependant, depuis que Mikito Harakiri a trouvé cette astuce et publié sur le site de tom kyte, des milliers d'utilisateurs en font usage.

Bonne semaine
Laurent

[Jaouad]

Mikito Harakiri

tu aurait pu te méfier Médiat

[SheikYerbouti]

tu aurait pu te méfier Médiat

c'était pas plutôt Imatumi Itashietsu ?

[SheikYerbouti]

Pour revenir au sujet, il me semble qu'on utilise souvent ALL_OBJECTS pour récupérer un nombre de lignes certain.

[Jaouad]

c'était pas plutôt Imatumi Itashietsu ?

[plaineR]

Cela me rappelle un débat que j'avais eu avec Laurent :
http://www.developpez.net/forums/sho...hlight=connect

[laurentschneider]

Cela me rappelle un débat que j'avais eu avec Laurent :

J'ai aussi une cette discussion avec Tom Kyte quand il est venu à Zurich. Un de mes amis mathématicien était aussi là. D'après lui, CONNECT BY permet de définir un graphe acyclique, alors que CONNECT BY sans PRIOR, c'est un graphe complet.

[Médiat]

Un de mes amis mathématicien était aussi là. D'après lui, CONNECT BY permet de définir un graphe acyclique, alors que CONNECT BY sans PRIOR, c'est un graphe complet.

J'avoue ne pas voir le rapport (point de vue de mathématicien, bien sur, et non discussion sur l'intérêt des requêtes ci-dessous) :

Code :

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

select * from dual connect by prior dummy = dummy;

crée une boucle malgré le PRIOR

Code :

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

select * from dual connect by 1!=1;

ne crée pas de boucle malgré l'absence de PRIOR (même remarque pour CONNECT BY ROWNUM < 10).

[laurentschneider]

Code :

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

select * from dual connect by prior dummy = dummy;

crée une boucle malgré le PRIOR

oui, ce code est donc illégal

Code :

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

select * from dual connect by 1!=1;

ce cas est vraiment limite, c'est une hiérarchie sans parent ni enfant, en combinant les 2,

Code :

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

select * from dual connect by prior dummy=dummy and 1!=1;

on a un bout de code valide, qui contient une clause prior, et qui ne boucle pas. Bien sûr la clause "CONNECT BY" est superflue ;-)

Par contre, et stpl dis-moi si je me trompe, connect by rownum<5 ou connect by level<5 crée une boucle.

ça serait sympa d'entendre ton avis sur 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
 
SQL> select sys_connect_by_path(deptno,'/') from dept connect by rownum<5;
SYS_CONNECT_BY_PATH(DEPTNO,'/')
-----------------------------------
/10
/10/10
/10/10/10
/10/10/10/10
/20
/30
/40

Tu vois la boucle?

A+

[Médiat]

Je crois que tu m'as mal compris, je ne prétends pas que "connect by rownum < 5" ne crée jamais de boucle, je dis simplement que la présence de prior n'est ni une condition suffisante (cf. l'exemple avec dummy) ni une condition nécessaire (cf. l'exemple avec 1!=1) pour avoir un graphe acyclique.

[laurentschneider]

ok, tu peux utiliser CONNECT BY <condition-toujours-fausse> pour ne rien faire du tout.

si tu fais une boucle dans une hiérarchie, tu te meus en terrain miné

[Médiat]

Bon je recommence doucement :

1) je ne dis pas que les boucles c'est bien
2) je ne dis pas que la requête connect by 1 != 1 est intéressante (j'aurais pu la remplacer par start with deptno = 10 connect by deptno = 20 and rownum < 3, et je n'aurais pas eu de boucle non plus !)
3) j'ai réagi à une affirmation théorique qui fait un lien entre la présence/absence de PRIOR dans une requête et le fait que le graphe soit acyclique ou non : ce qui est FAUX comme les deux requêtes que j'ai exhibées le démontrent, même si ces requêtes sont inintéressantes au possible.

[laurentschneider]

ouais, ça reste dans le domaine de l'inutilisable