Discussion :

[Douzout]

Bonjour,

Mes utilisateurs disposent de formulaires, chacun ayant leur ID, leur permettant de soumettre de 1 à 10 codes.
Ils peuvent revenir sur un formulaire existant et le mettre à jour (ajout, suppression de codes) : une nouvelle version est alors créée pour ce même ID.
Tout cela est stocké dans une TABLE1 : ID | Version | Codes
Il existe une autre table permettant de connaitre la dernière version de chaque formulaire, TABLE2 : ID | Version

Ex (1/2) :

TABLE1

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
ID | Version | Codes
001 | 1 | 123
001 | 2 | 123
001 | 2 | 456
001 | 3 | 123
001 | 3 | 222
001 | 3 | 789
001 | 4 | 135
001 | 4 | 222
001 | 4 | 246
002 | 1 | 111
002 | 1 | 222
002 | 2 | 222
002 | 2 | 789
003 | 1 | 222
003 | 1 | 444
003 | 1 | 789

TABLE2

ID | Version
001 | 4
002 | 2
003 | 1

Je cherche à mettre en place un "formulaire de recherche" dans lequel les utilisateurs peuvent chercher de 1 à 10 codes, et remonter les ID des formulaires dont la dernière version contient au moins tous les codes demandés.


Ex (2/2) :
L'utilisateur cherche les formulaires avec les codes 222 et 789
001 ne doit pas remonter (car sa dernière version, la 4, n'en contient qu'un des 2)
002 doit remonter (car sa dernière version, la 2, contient ces 2 codes)
003 doit remonter (car sa dernière version, la 1, contient ces 2 codes, plus un 3e, ce qui "ne dérange pas")

Je ne parviens pas à créer cette requête avec mes quelques connaissances; j'ai la possibilité de passer la liste des codes demandés en paramètre de cette requête.
Comment récupérer l'information "dernière version" des formulaires ? avec un "MAX" dans TABLE1 ou avec une jointure sur TABLE2 ?
Dans l'idéal, je souhaiterais une unique requête, mais la création d'une fonction, vue, index, etc. peut être envisagé si les performances le nécessitent.

Je vous remercie d'avance pour votre aide et vos conseils au néophyte que je suis.
Bonne journée à tous !
Guillaume

[al1_24]

Peux-tu nous montrer les requêtes que tu as déjà testées ?

[Douzout]

Merci pour cette réponse si rapide !
Du coup, j'avais créé cette discussion en anticipant demain, où je retourne chez le client souhaitant mettre en place cela, chez qui je ne suis pas aujourd'hui. Je ne pourrai vous montrer ma dernière requête testée que demain, donc, désolé

[mnitu]

Solutions spécifique Oracle à la division relationnelle

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
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> With Table1 As (
  2    Select cast('001' As varchar2(5)) As Id, 1 As version, cast('123' As varchar2(10)) As codes from dual union all
  3    Select '001' As Id, 2 As version, '123' As codes from dual union all
  4    Select '001' As Id, 2 As version, '456' As codes from dual union all
  5    Select '001' As Id, 3 As version, '123' As codes from dual union all
  6    Select '001' As Id, 3 As version, '222' As codes from dual union all
  7    Select '001' As Id, 3 As version, '789' As codes from dual union all
  8    Select '001' As Id, 4 As version, '135' As codes from dual union all
  9    Select '001' As Id, 4 As version, '222' As codes from dual union all
 10    Select '001' As Id, 4 As version, '246' As codes from dual union all
 11    Select '002' As Id, 1 As version, '111' As codes from dual union all
 12    Select '002' As Id, 1 As version, '222' As codes from dual union all
 13    Select '002' As Id, 2 As version, '222' As codes from dual union all
 14    Select '002' As Id, 2 As version, '789' As codes from dual union all
 15    Select '003' As Id, 1 As version, '222' As codes from dual union all
 16    Select '003' As Id, 1 As version, '444' As codes from dual union all
 17    Select '003' As Id, 1 As version, '789' As codes from dual
 18  ),
 19  TABLE2 As (
 20    Select cast('001' As varchar2(5)) As Id, 4 As version from dual union all
 21    Select '002' As Id, 2 As version from dual union all
 22    Select '003' As Id, 1 As version from dual
 23  ), vals As (
 24    Select cast('222' As varchar2(10)) As codes from dual union all
 25    Select '789' As codes from dual
 26  )
 27  Select t1.id
 28    from TABLE1 t1
 29         Join
 30         TABLE2 t2
 31      On t1.id = t2.id
 32         And t1.version = t2.version
 33    Group BY t1.id
 34    HAVING (SELECT Cast(Collect(codes) AS varchar2_tt) FROM vals)
 35            Submultiset of Cast(collect(codes) AS varchar2_tt)
 36  /
 
ID
-----
002
003
 
SQL>

[Douzout]

Bonjour,

Merci beaucoup pour cette aide !

J'ai tenté de comprendre un peu comment était faite cette requête :

- la partie FROM TABLE1 t1 JOIN TABLE2 t2 ON t1.id = t2.id AND t1.version = t2.version je comprends, même si j'ai plutôt tendance à écrire FROM TABLE1 t1, TABLE2 t2 where t1.id = t2.id AND t1.version = t2.version. Je ne sais pas si l'une de ces 2 écritures est préférable.

- j'ai compris que toute la première partie WITH TABLE1...TABLE2...vals permettait de déclarer et peupler les tables avant de lancer la requête.

- les cast(...as...) permettent de récupérer les valeurs dans un type particulier.

- je découvre complètement les "collect" et "submultiset"

J'ai tenté de "concaténer" la requête avec la déclaration "with vals" :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
select t1.dataid 
	from TABLE1 t1 
		JOIN
		TABLE2 t2
	ON t1.dataid = t2.dataid
		AND t1.versionnum = t2.versionnum
group by t1.dataid 
having (
	select Cast(collect(codes) AS varchar2_tt) from 
		(select cast('222' as varchar2(10)) as codes from dual union all 
		select cast('789' as varchar2(10)) as codes from dual))
	submultiset of cast(collect(codes) as varchar2_tt)

J'ai eu l'erreur "Erreur lors du traitement de la requête. [ORA-00936: missing expression"
En tentant de bidouiller un peu (enlever les cast), j'ai également eu des erreur "ORA-00902: invalid datatype" et "ORA-00932: inconsistent datatypes: expected UDT got - - select "

Est-il possible de combiner la déclaration de la table temporaire "vals" et la requête sur cette table, comme je souhaite le faire ?

Bonne journée à tous, encore merci beaucoup !

[mapk0]

Déjà je pense qu'il faut déclarer le type varchar2_tt avec une instruction du genre:

Code :

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

CREATE OR REPLACE TYPE varchar2_tt AS TABLE OF VARCHAR2(4000);

[Douzout]

Bonjour,

Désolé pour le long délai de réponse, je suis de retour de congé paternité, survenu un peu plus tôt que prévu !
Bref, je m'apprête à me replonger dans ma problématique sql, et toute nouvelle piste est la bienvenue

Excellente année 2014 à tous !