Créer Vue multicritère

**bbo1991** · 02/01/2007, 15h48

Bonjour à tous,
J'ai deux tables: une table "client" et une table "critereSuspect".
Dans chaque table critereSuspect il y a différents critères qui qualifient un client jugé suspect: on a par exemple un numéro de téléphone douteux donc tout client qui l'aura sera jugé suspect, un critère peut se composer de divers champs: un client de type "Particulier" qui fait un achat entre 15 000 et 10 000 euros par exemple est suspect, dans ce cas on observe le type et le montant.

Chaque ligne de "critereSuspect" se compose donc des champs suivant : "typeClient", "typeachat","montantAchatMaximum","montantAchatMinimum", "tel".

Ce que je voudrai faire maintenant c'est créer une vue qui m'affiche tous les clients que je juge suspects : je dois donc tester chaque client avec chaque ligne de ma table "critereSuspect" et là je bloque

Comment faire pour obtenir les critères de chaque ligne et les tester une par une ?
Autre blocage pour certaines lignes de la table "citereSuspect" on ne spécifie par exemple que le numéro de téléphone dans ce cas là comment dire à Oracle de ne pas tester les autres champs????
Quelqu'un a une solution à proposer?

**nuke_y** · 02/01/2007, 16h28

En conceptuel :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT T1.* from Client T1 WHERE EXISTS
(
    select 1 FROM critereSuspect T2 WHERE
   (T1.typeClient=T2.typeClient AND T1.Montant>T2.MontantMax)
   OR
   (T1.tel=T2.tel)
   OR
   ...
)

Ca va te donner la liste des clients qui ont au moins un critère suspect.

**bbo1991** · 02/01/2007, 17h47

Mouais ça j'y avais déjà pensé mais voyez-vous la liste des critères suspects est dynamique, donc ce qui se trouve ( ) après les clauses WHERE peuvent varier selon les lignes de la table "critereSuspect".

L'administrateur de l'appli peu rajouter de nouveaux numéros de tel douteux, ou encore d'autres critères ou en supprimer sa guise.

La liste des clients suspects variant selon les valeurs des critères suspects....

Donc il nous faut un moyen pour dynamiser le contenu des clauses WHERE ....

**nuke_y** · 02/01/2007, 19h40

Tu peux nous donner un exemple sur un jeu de données ?

**bbo1991** · 03/01/2007, 08h41

Ok,
Soit par exemple la table "critereSuspect".
Cette table possède les champs suivants:
"typeClient", "typeachat","montantAchatMaximum","montantAchatMinimum" et "tel".

Dans un premier temps cette table compte 2 enregistrements:
-dans le premier seul le champ "tel" est renseigné, les autres champs sont vides, avec "tel"=33658794
-dans le second on a "typeClient"=particulier, "montantAchatMaximum"=15000,"montantAchatMinimum"=10000 les autres champs sont vides.

Mais voilà, dans un second temps on a décidé d'ajouter de nouveaux enregistrements à la table "critereSuspect", en plus des deux critères précédents on a ajouté deux nouvelles lignes:
- "typeachat"=Au comptant, "montantAchatMinimum"=50000, les autres champs seront vides
- "typeachat"=Crédit, "tel"=3366889547, les autres champs à vides

Et il va falloir tester chaque client avec tous ces critère pour obtenir la liste des clients suspects, bref il va nous falloir une vue dynamique....

**remi4444** · 03/01/2007, 10h29

Bonjour, bonne année et tout ça...

Je comprends pas en quoi la proposition de nuke_y ne va pas, une vue par définition est dynamique...

Il faut que tu poses clairement tous tes cas possibles.
D'après ce que j'ai cru comprendre tu voudrais qu'un "null" signifie qu'il ne faut pas faire de test. Tu peux donc faire des critères du style:

Code :

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

1
2
3
4
5
6
7
8
9
SELECT T1.* FROM Client T1 WHERE EXISTS
(
    SELECT 1 FROM critereSuspect T2 WHERE
         (T1.typeClient=T2.typeClient OR  T2.typeClient  is null) 
   AND (T1.typeAchat=T2.typeAchat OR  T2.typeAchat  is null) 
   AND (T1.Montant>T2.MontantMax OR T2.MontantMax is null )
   AND (T1.Montant <T2.MontantMin  OR T2.MontantMin is null )
   AND (T1.tel=T2.tel OR T2.tel is null)
)

Il faut bien comprendre qu'une vue n'est jamais qu'une requête stockée donc lorsque tu vas rajouter ou enlever des lignes dans ta table de critères le résultat de la vue suivra.

**bbo1991** · 03/01/2007, 14h01

Pour faire correctement la vue il faudrait qu'elle ressembe à ceci.

En suivant mon précédent scénar, elle devrait ressember dans un premier temps à ceci:

Code :

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

1
2
3
4
5
6
 
SELECT T1.* FROM Client T1 WHERE EXISTS 
(  SELECT 1 FROM critereSuspect T2 WHERE
(T1.tel=T2.tel) OR
(T1.typeClient=T2.typeClient AND T1.Montant <T2.MontantMin AND T1.Montant>T2.MontantMax) 
)

Puis dans un second temps, ça change en qlq chose comme ceci:

Code :

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

1
2
3
4
5
6
7
8
 
SELECT T1.* FROM Client T1 WHERE EXISTS 
(  SELECT 1 FROM critereSuspect T2 WHERE
(T1.tel=T2.tel) OR
(T1.typeClient=T2.typeClient AND T1.Montant <T2.MontantMin AND T1.Montant>T2.MontantMax) OR
(T1.typeachat=T2.typeAchat  AND T1.Montant>T2.MontantMax) OR
(T1.typeachat=T2.typeAchat  AND T1.tel=T2.tel)
)

Si je comprends bien remi4444 tu proposes de généraliser tous les cas possibles dans les clauses WHERE, là j'ai fait quelques essais mais ça marche pas...
Sinon j'ai pas bien compris la conséquence des bouts de code:

Code :

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

1
2
 
OR  T2.typeClient  IS NULL

Pour les champs de la table "critereSuspect" qui sont laissés nulls j'aimerai juste ne pas les considérer, or dans d'autres cas il se pourrait que tous les champs de cette table soient reneignés sur une seule ligne auquel cas il va falloir tester la valeur de tous ces champs.

**remi4444** · 03/01/2007, 14h11

Le "OR is null" c'est précisément pour faire ce que tu demandes mais en fait je crois que le problème est pris à l'envers. Car finalement, j'ai l'impression que tu parles de critère éliminatoire, ce n'est donc pas par des appartenances qu'il faut raisonner (EXISTS) mais par des exclusions (NOT EXISTS) ...

Donc dans ce cas effectivement, le "OR is null" n'a plus lieu d'etre...

EDIT: non décidément, je viens de relire ton premier post, c'est bien les clients suspects que tu veux, c'est à dire ceux qui ont au moins un des critères de ta 2ieme table de vérifié. Je ne comprends pas pourquoi ma requête ne te conviens pas, il faudrait que tu donne un mini jeu de données pour etre sur qu'on parle de la même chose.

**bbo1991** · 03/01/2007, 16h42

Bon d'accord j'ai persisté sur le code et ça a l'air de marcher:

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.*
  FROM client t1
 WHERE EXISTS (
          SELECT 1
            FROM criteresuspect t2
           WHERE
 (
(t1.typeClient = t2.typeClient OR t2.typeClient IS NULL)
AND (t1.typeAchat = t2.typeAchat OR t2.typeAchat IS NULL)
AND (t1.tel = t2.tel OR t2.tel IS NULL)
AND (t1.prix > t2.prixsup OR t1.prix < t2.prixinf) 
))

Comme je l'ai dit ça a l'air de marcher, l'air car il y a un petit truc ki cloche:
dans ma table "client" il arrive que le champ "typeachat" ne soit pas renseigné et laissé à NULL, dans ce cas là même si le client satisfait à l'un des critères suspects il est ignoré...
Une solution?
Sinon merci déjà, il ya quand même des résultats

**remi4444** · 03/01/2007, 16h58

Il faut savoir que NULL = nimporte_quoi renvoie toujours faux, et meme NULL = NULL!

C'est un peu logique car si tu as NULL dans ta table client, ça veux dire que le type d'achat du client est indeterminé donc par défaut, dans le doute oracle s'abstient et ne fera la jointure avec aucun des critères, seuls seront pris en compte les lignes de critère ou le type d'achat est lui même null (grace au "OR t2.typeAchat is null") si tu veux que le traitement par défaut soit plus restrictif, c'est à dire que dés qu'un client a un typeAchat à null alors tous les crières doivent etre testé, alors rajoute dans la meme parenthèse "OR t1.typeAchat is null".

**bbo1991** · 04/01/2007, 12h52

Mouais cool, l'ajout de "or t1.typeAchat is null" a bien résolu l'affaire merci bcp.

Seule ombre au tableau: le numéro de téléphone.
J'ai effectué jusqu'ici mes tests sur des critères suspects du type : client appartenant à un type et ayant effectué des achats jugés suspects (compris entre 2 prix extrêmes).
Jusque là tout va bien, ma vue me retourne bien les clients suspects que je cherchai.

Là j'ai rajouté un client ayant un numéro de téléphone douteux ( ex: 3365482931).

Je rajoute dans ma table "critereSuspect" un enregistrement dans lequel je ne spécifie que le champ "tel" en y mettant la valeur "3365482931", les autres champs sont vides.

Et là ma vue ne trouve pas le client qui a pourtant ce numéro là!!!
Mon code :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT t1.*
  FROM client t1
 WHERE EXISTS (
          SELECT 1
            FROM criteresuspect t2
           WHERE
 (
(t1.typeClient = t2.typeClient OR t1.typeClient IS NULL OR t2.typeClient IS NULL)
AND (t1.typeAchat = t2.typeAchat OR t1.typeAchat IS NULL OR t2.typeAchat IS NULL)
AND (t1.tel = t2.tel OR t1.tel IS NULL OR t2.tel IS NULL)
AND (t1.tel not like t2.notTel OR t1.tel IS NULL OR t2.tel IS NULL)
AND ((t1.prix > t2.prixsup OR t1.prix < t2.prixinf) OR t1.prix IS NULL OR t2.prixsup IS NULL OR t2.prixinf IS NULL)
))

Vous remarquerez l'ajout d'un champ "notTel" dans la table critere suspect, il est utillisé pour demander à la vue d'ignorer un numéro de téléphone donné (dire par exemple qu'il s'agit d'un numéro lié à un client exceptionnel).

Vu que ça marche sur les autres critères je ne comprends pas pourquoi il ne trouve pas mon client muni du tel que j'ai renseigné comme critère suspect!!!!