fonction Selection par defaut

Version imprimable

06/02/2011, 10h24
pepitoluna

fonction Selection par defaut

Bonjour,
je vous explique mon problème.
je suis en train de construire une table paramètre (Param) qui est constituée de deux groupe de champs, le premier servant à l'identification(ident1,ident2,ident3,ident4,ident5) , le second rassemblant des informations(infos1,infos2,etc) dont je vais avoir besoin
J'aimerais construire une fonction qui en fonction des valeurs d'un champs d'ident que je récupère par une requête me donne les bons champ infos correspondant dans la table param. Jusque la tout va bien
Le problème est que je risque d'avoir des couples (ident1,ident2,ident3,ident4,ident5) qui n'existent pas dans la table PARAM (certains champs idents ne sont pas renseignées et nulles).
exemple de couples (ident1='a',ident='b',ident3='c',ident4=0,ident5=0)
Pour ce cas précis il faudrait quand même que cette fonction me ramène des champs en trouvant un couple d'idents(ident1,ident2,etc) qui lui est le plus "proche".
ps : le choix lorsque le couple d'idents n'est pas présent dans la table doit se faire dans l'ordre suivant de comparaison de la valeur ident1 puis ident2,puis idens3,tec...
Si j'utilise ce mode sélection, le couple d'ident récupéré a beaucoup de chances de trouver plusieurs couples d'ident correspondants.
Dans ce cas, le choix se ferait sur celui qui a de plus de champs idents renseignés(cad non nulles) dans la table param.
Auriez vous une idée sur la manière de construire cette fonction sans que ce soit un gros bordel.
Merci
je ne sais pas si j'ai été assez clair mais n'hésitez pas à me le dire si cela n'est pas le cas

Si j'ai bien compris, votre table a cette tête :

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
 
SQL> select * from titi;
 
I I I         I4         I5 P1     P2
- - - ---------- ---------- ------ ------
a b c          0          1 param1 param2
a a c          0          0 param4 param3
a b c          1          0 param4 param3
a b a          1          1 param4 param3
b b b          0          1 param4 param3
b c a          0          1 param4 param3
b b c          0          0 param4 param3
b a c          1          0 param4 param3
b a a          1          1 param4 param3
b c b          0          1 param4 param3
c b a          0          0 param4 param3
c b c          1          0 param4 param3
c a c          1          1 param4 param3
 
desc titi
 
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 I1                                                 CHAR(1)
 I2                                                 CHAR(1)
 I3                                                 CHAR(1)
 I4                                                 NUMBER
 I5                                                 NUMBER
 P1                                                 CHAR(6)
 P2                                                 CHAR(6)

Je procède de la manière suivante :

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
 
 create or replace type tltiti as object ( i1 char(1), i2 char(1), i3 char(1), i4 number, i5 number, p1 char(6), p2 char(6) );
 /
 
 
 create or replace type t_titi as table of tltiti;
 /
 
create or replace function f_titi ( p1 in varchar2, p2 in varchar2, p3 in varchar2, p4 in number, p5 in number) return t_titi is
	ret t_titi;
begin
	select cast( multiset (select * from ( select * from titi where I1=p1 AND I2=p2 AND I3=p3 AND I4=p4 AND I5=p5 union all 
	                            select * from titi where I1=p1 AND I2=p2 AND I3=p3 AND I4=p4 union all
								select * from titi where I1=p1 AND I2=p2 AND I3=p3 union all
								select * from titi where I1=p1 AND I2=p2 union all
								select * from titi where I1=p1 union all
								select * from titi )
				where rownum=1) as t_titi ) into ret from dual;
	return ret;
end;
/
 
 
SQL> select * from table( f_titi('a','b','c',0,0));
 
I I I         I4         I5 P1     P2
- - - ---------- ---------- ------ ------
a b c          0          1 param1 param2
 
SQL> select * from table( f_titi('a','b','c',0,1));
 
I I I         I4         I5 P1     P2
- - - ---------- ---------- ------ ------
a b c          0          1 param1 param2

Est-ce le type de résultat que vous souhaitez ?

Bonsoir,
merci pour la réponse qui ressemble à ce que j'attendais mais par contre entre-temps les besoins ont changé :
La table paramètre(titi) peut désormais contenir des champs dont les valeurs sont nulles (à titre d'exemple: a,b, ,0 ,param2,param3 )
Autre point , la seule règle existante est que si le couple d'ident ne correspond pas à l'identique, on cherche les couples d'ident (lignes) pour lesquels les champs idents ne correspondant pas sont égales à la valeur nulle.
Parmi ces couples, on choisirait au final celui qui a le plus de champs correspondant non nulles.
Code:

1 2 3 4 5 6 - - ---- -------- --------- ------ ------ a b c 0 1 param1 param2 a b c NULL 0 param4 param3 a b c 1 0 param4 param3 a b c NULL NULL param4 param5 a b NULL NULL NULL param4 param5
la fonction devrait me ramener si
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT * FROM TABLE( f_titi('a','b','c',0,0)); I I I I4 I5 P1 P2 - - - ---------- ---------- ------ ------ a b c null 0 param4 param3 SQL> SELECT * FROM TABLE( f_titi('a','b','c',1,1)); I I I I4 I5 P1 P2 - - - ---------- ---------- ------ ------ a b c null nullparam4param5 SELECT * FROM TABLE( f_titi('a','b','c',1,0)); I I I I4 I5 P1 P2 - - - ---------- ---------- ------ ------ a b c 1 0 param4 param3
Voila je sais pas si j'ai été assez clair mais j'imagine que la requête principale de la fonction aurait cette forme mais je ne vois pas trop comment faire pour que la requête me renvoie la ligne pour laquelle le nombre de champs idents renseignés(not null) est le plus élevé.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SELECT cast( multiset ( SELECT * FROM titi WHERE (I1 = p1 or I1 is null) AND (I2 = p2 or I2 is null) AND (I3 = p3 or I3 is null) AND (I4 = p4 or I4 is null) AND (I5 = p5 or I5 is null) ) AS t_titi ) INTO ret FROM dual; RETURN ret; end;
Merci encore

Je l'aurais plutôt réécrite comme ça.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create or replace function f_titi ( v_p1 in varchar2, v_p2 in varchar2, v_p3 in varchar2, v_p4 in number, v_p5 in number) return t_titi is
   ret t_titi;
begin
   for c in (select * from (select I1, I2, I3, I4, I5, P1, P2
                        from ( select 1 ord, T.* from titi T where nvl(I1,v_p1)=v_p1 AND nvl(I2,v_p2)=v_p2 AND nvl(I3,v_p3)=v_p3 AND nvl(I4,v_p4)=v_p4 AND nvl(I5,v_p5)=v_p5 union all
                               select 2, T.* from titi T where nvl(I1,v_p1)=v_p1 AND nvl(I2,v_p2)=v_p2 AND nvl(I3,v_p3)=v_p3 AND nvl(I4,v_p4)=v_p4 union all
                                                           select 3, T.* from titi T where nvl(I1,v_p1)=v_p1 AND nvl(I2,v_p2)=v_p2 AND nvl(I3,v_p3)=v_p3 union all
                                                           select 4, T.* from titi T where nvl(I1,v_p1)=v_p1 AND nvl(I2,v_p2)=v_p2 union all
                                                           select 5, T.* from titi T where nvl(I1,v_p1)=v_p1 union all
                                                           select 6, T.* from titi T
                                                           ) order by ord
                           ) where rownum=1)
   loop
		select cast( multiset( select c.I1, c.I2, c.I3, c.I4, c.i5, c.P1, c.P2 from dual) as t_titi ) into ret from dual;
	end loop;
   return ret;
end;
/

27/02/2011, 12h01
pepitoluna

merci pour les reponses,
voila comment je l'ai ecrite au final

Citation:

SELECT * into res from(

select P1, P2, P3,
CASE I1 is not null then 1
CASE I2 is not null then 2
,

(decode(I1,null,0,1 )+decode(I2,null,0,1 )+decode(I3,null,0,1 ).... +decode(I5,null,0,1 )) as cpt

FROM titi
WHERE (I1 = p1 OR I1 IS NULL)
AND (I2 = p2 OR I2 IS NULL)
AND (I3 = p3 OR I3 IS NULL)
AND (I4 = p4 OR I4 IS NULL)
AND (I5 = p5 OR I5 IS NULL)
27/02/2011, 12h05
pepitoluna

merci pour les reponses,
voila comment je l'ai ecrite au final

Citation:

SELECT * into res from(

select P1, P2, P3,
CASE
When I1 is not null then 1
When I2 is not null then 2
When I3 is not null then 3
When I4 is not null then 4
When I5 is not null then 5
else null end

(decode(I1,null,0,1 )+decode(I2,null,0,1 )+decode(I3,null,0,1 ).... +decode(I5,null,0,1 )) as cpt

FROM titi
WHERE (I1 = p1 OR I1 IS NULL)
AND (I2 = p2 OR I2 IS NULL)
AND (I3 = p3 OR I3 IS NULL)
AND (I4 = p4 OR I4 IS NULL)
AND (I5 = p5 OR I5 IS NULL)
27/02/2011, 12h10
pepitoluna

merci pour les réponses,
voila comment je l'ai écrite au final

Citation:

SELECT * into res from
( select
P1, P2, P3,
CASE
When I1 is not null then 1
When I2 is not null then 2
When I3 is not null then 3
When I4 is not null then 4
When I5 is not null then 5
else null end
as ordre,

(decode(I1,null,0,1 )+decode(I2,null,0,1 )+decode(I3,null,0,1 ).... +decode(I5,null,0,1 )) as cpt

FROM titi
WHERE (I1 = p1 OR I1 IS NULL)
AND (I2 = p2 OR I2 IS NULL)
AND (I3 = p3 OR I3 IS NULL)
AND (I4 = p4 OR I4 IS NULL)
AND (I5 = p5 OR I5 IS NULL)
order by ordre DESC, cpt ASC)
where rownum=1;

ps : je n'utilise pas nvl ds le where parce que les champs I1,I2 peuvent êtres nulles