Clause IN avec une liste de valeur

Version imprimable

09/03/2018, 11h08
dede_bo

Clause IN avec une liste de valeur
Bonjour,

J'explique mon problème:
J'ai une table (Table1) avec un champ qui contient une liste d'ID ou l'objet est lié à différents noms (ID venant de Table2).
Cet ID est en fait n° de l'enregistrement du nom.

Table1
ID lie_a
1 | 5
2 | 8, 16
3 | 7

Table 2
ID nom
5 pomme
7 poire
8 raisin blanc
16 raisin noir

J'essaie d'extraire la liste des noms.

ID NOM
1 | pomme
2 | raisin blanc, raisin noir
3 | poire
Code:

1 2 3 4 5 6 SELECT table2.nom FROM table2 WHERE table2.ID IN(SELECT table1.lie_a FROM table1 )
Le problème est qu'une liste est une chaine de caractères et pas numérique. Donc, cela fonctionne pas.
Si je modifie la ligne suivante:
Code:

1 2 WHERE TO_CHAR(table2.ID) IN(SELECT table1.lie_a
Il n'y a pas d'erreur mais le résultat est vide.

J'ai cherché sur ce forum et Google.
Une discussion qui existe : https://www.developpez.net/forums/d1...on-d-variable/
L'auteur l'a clôturé sans mettre la solution.

Alors, si l'auteur se reconnait, auriez-vous la gentillesse de m'expliquer votre solution?
Autrement, quelqu'un aurais t' il une idée à proposer?

Merci
Bonne journée
André
09/03/2018, 11h58
Waldar

La modélisation de votre base est erronée et viole la première forme normale.
Oui, on peut jouer avec des solutions pour répondre au besoin, mais ce ne sera jamais très performant.

Je ne vous ai pas proposé la bonne solution en me relisant, mais du coup voici de quoi répondre à votre besoin.
11gR2 minimum.

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
42
43
44
45
46
47
48
49
with table1 (id, lie_a) as
(
select 1, '5'     from dual union all
select 2, '8, 16' from dual union all
select 3, '7'     from dual
)
  ,  table2 (id, nom) as
(
select  5, 'pomme'        from dual union all
select  7, 'poire'        from dual union all
select  8, 'raisin blanc' from dual union all
select 16, 'raisin noir'  from dual
)
  ,  table1_rec (id, lie_a, lie_reste, lvl, nb_recurs) as
(
select id
     , substr(lie_a, 1, instr(lie_a, ',') - 1)
     , substr(lie_a, instr(lie_a, ',') + 2)
     , 1
     , length(lie_a) - length(replace(lie_a, ',', ''))
  from table1
 where instr(lie_a, ',') > 0
 union all
select id
     , case lvl
         when nb_recurs
         then substr(lie_reste, 1)
         else substr(lie_reste, 1, instr(lie_reste, ',') - 1)
       end
     , substr(lie_reste, instr(lie_reste, ',') + 2)
     , lvl + 1
     , nb_recurs
  from table1_rec
 where lvl <= nb_recurs
)
  ,  table1_finale (id, lie_a, ord) as
(
select id, to_number(lie_a), lvl
  from table1_rec
 union all
select id, to_number(lie_a), 1
  from table1
 where instr(lie_a, ',') = 0
)
    select t1.id
         , listagg(t2.nom, ', ') within group (order by t1.ord asc) as nom
      from table1_finale t1
 left join table2        t2 on t2.id = t1.lie_a
  group by t1.id;

Je vous invite toujours à reprendre la modélisation.

09/03/2018, 13h43
escartefigue

Citation:

Envoyé par Waldar

Je vous invite toujours à reprendre la modélisation.

+1 : à modélisation merd####, solution complexe, fiabilité douteuse et performances désastreuses :aie:

Reaction et solution trouvée

Bonjour à tous les deux,
Merci pour vos réactions. Je crains effectivement pour les performances.
Par contre, de mon côté, un collègue viens de me donner la solution (avec de bonnes performances)

Code:

1
2
3
4
5
6
7
8
9
 
SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(t.lie_a, '[^,]+', 1, lines.column_value))) AS fruitID, sl.nom
  FROM Table1 t, Table2 sl,
           TABLE (CAST (MULTISET(SELECT LEVEL FROM dual
                                                       CONNECT BY INSTR(t.lie_a, ',', 1, LEVEL - 1) > 0
                                 ) AS sys.odciNumberList 
                        ) 
                 ) lines
 WHERE sl.ID = TO_NUMBER(TRIM(REGEXP_SUBSTR(t.lie_a, '[^,]+', 1, lines.column_value)))

Je dois lui demander qu'il m'explique cet SQL mais elle fonctionne très bien.

Voilà

Bonne après-midi
André

09/03/2018, 20h14
escartefigue

Citation:

Envoyé par dede_bo

Par contre, de mon côté, un collègue viens de me donner la solution (avec de bonnes performances)

Sur quelle volumétrie avez vous mesuré les perfs ? je doute que les perfs soient acceptables avec un volume significatif

Pour la simplicité du code, juste avec un INSTR et une Jointure Cartésienne 8-)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH table1 (ID, lie_a) AS
(
SELECT 1, '5'     FROM dual UNION ALL
SELECT 2, '16, 5,8,7' FROM dual UNION ALL
SELECT 3, '7'     FROM dual
)
  ,  table2 (ID, nom) AS
(
SELECT  5, 'pomme'        FROM dual UNION ALL
SELECT  7, 'poire'        FROM dual UNION ALL
SELECT  8, 'raisin blanc' FROM dual UNION ALL
SELECT 16, 'raisin noir'  FROM dual
)
SELECT n.ID, listagg(l.nom, ', ') WITHIN GROUP (ORDER BY INSTR(','||REPLACE(n.lie_a, ' ') ||',', ','|| l.ID ||',')) lib
FROM table1 n, table2 l
WHERE INSTR(','||REPLACE(n.lie_a, ' ') ||',', ','|| l.ID ||',') > 0 
GROUP BY n.ID
 
ID	LIB
1	pomme
2	raisin noir, pomme, raisin blanc, poire
3	poire