Oracle : Case dans Where qui retourne une liste

Bonjour,

Je suis dans une procédure stockée.
Suivant la valeur de ma variable V_PARAM, la liste des identifiants en paramètre va être variable (cf exemple ci-dessous).

Une liste, ça ne marche pas :

Code:

---

1 2 3 4

select t.champ1, t.champ2 from MATABLE t where t.champ3 in (select case when V_PARAM=3 then (120, 2) else (5) end from dual)

---

Une valeur simple, ça marche (mais c'est pas ce que je veux)

Code:

---

1 2 3 4

select t.champ1, t.champ2 from MATABLE t where t.champ3 in (select case when V_PARAM=3 then 120 else 5 end from dual)

---

Si c'est pas possible, je suis aussi preneur d'une solution qui me permette carrément de faire des sélect différents de ce type... à l'intérieur d'une boucle for :

Code:

---

1 2 3 4 5 6 7 8 9

for c_table in ( --cas1 : select t.champ1, t.champ2 from MATABLE t where t.champ3 in (120, 2) --cas2 ? )loop --bla, bla end loop;

---

Merci pour votre aide.

Effectivement tu ne peux pas utiliser CASE avec de multiples valeurs, ou du moins je ne vois pas comment à part remplacer IN par plusieurs OR.

Citation:

---

Si c'est pas possible, je suis aussi preneur d'une solution qui me permette carrément de faire des sélect différents de ce type

---

Tu peux utiliser la syntaxe OPEN FOR et générer une requête dynamiquement :

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

SQL> create table t as 2 select 1 as col from dual union all 3 select 2 from dual union all 4 select 3 from dual 5 /   Table created.   SQL> CREATE OR REPLACE procedure p (p_param IN number, c OUT sys_refcursor) 2 as 3 l_query varchar2(400); 4 begin 5 l_query := 'select * from t where 1 = 1 '; 6 if p_param = 3 then 7 l_query := l_query || ' and col in (1,2) '; 8 else 9 l_query := l_query || ' and col = 3 '; 10 end if; 11 open c for l_query; 12 end; 13 /   Procedure created.   SQL> show err No errors. SQL> var rc refcursor SQL> execute p (3,:rc)   PL/SQL procedure successfully completed.   SQL> print rc   COL ---------- 1 2   SQL> execute p (1,:rc)   PL/SQL procedure successfully completed.   SQL> print rc   COL ---------- 3

---

Mais bon c'est la version rapide sans bind variable, si la nécessité finale est d'exécuter la requête une fois dans un job nocturne c'est valable mais en environnement OLTP, il faut utiliser les binds variables et utiliser une technique plus élaborée comme Varying in lists
Dans ton cas ça pourrait donner :

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 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

SQL> create or replace context my_ctx using my_ctx_procedure 2 /   Context created.   SQL> create or replace procedure my_ctx_procedure ( p_str in varchar2 ) 2 as 3 begin 4 dbms_session.set_context ( 'my_ctx', 'txt', p_str ); 5 end; 6 /   Procedure created.   SQL> create or replace view IN_LIST 2 as 3 select trim( substr (txt, 4 instr (txt, ',', 1, level ) + 1, 5 instr (txt, ',', 1, level+1) 6 - instr (txt, ',', 1, level) -1 ) ) as token 7 from (select ','||sys_context('my_ctx','txt')||',' txt 8 from dual) 9 connect by level <= length(sys_context('my_ctx','txt')) 10 -length(replace(sys_context('my_ctx','txt'),',',''))+1 11 /   View created.   SQL> CREATE OR REPLACE procedure p (p_param IN number, c OUT sys_refcursor) 2 as 3 begin 4 if p_param = 3 then 5 my_ctx_procedure( '1,2' ); 6 elsif p_param = 1 then 7 my_ctx_procedure( '3' ); 8 else 9 my_ctx_procedure( '1,2,3' ); 10 end if; 11 open c for select * from t where col in (select * from IN_LIST); 12 end; 13 /   Procedure created.   SQL> show err No errors. SQL> var rc refcursor SQL> execute p (3,:rc)   PL/SQL procedure successfully completed.   SQL> print rc   COL ---------- 1 2   SQL> execute p (1,:rc)   PL/SQL procedure successfully completed.   SQL> print rc   COL ---------- 3   SQL> execute p (8,:rc)   PL/SQL procedure successfully completed.   SQL> print rc   COL ---------- 1 2 3

---

Merci pour ta réponse.

1°/Ca confirme qu'on peut donc pas faire avec les CASE :?

2°/J'avais oublié, mais ça je connais les OPEN FOR, donc ça semble faisable :ccool:

3°/Je suis désolé mais là je ne comprend absolument rien 8O Je ne reconnais plus du tout l'exemple simple que j'ai donné avec.
-job nocturne : c'est un terme technique? Quel rapport, en fait non ces traitements vont tourner en journée
-j'ai quand-même vaguement essayé de suivre le truc, mais la procédure my_ctx_procedure a des erreurs de compilation à cause du CREATE OR REPLACE context my_ctx USING my_ctx_procedure où j'ai insufficient privileges
-mais où se trouve ma requête dans tout ça?

Tu peux peut être aussi te faire une CTE de paramétrage ou -1 correspondrait au ELSE du CASE, quelque chose comme :

Code:

---

1 2 3 4 5 6 7 8

with param ( select -1 as c_param, 5 as val from dual union all select 3 , 120 from dual union all select 3 , 2 from dual ) select t.champ1, t.champ2 from MATABLE t where t.champ3 in (select val from param where c_param = v_param)

---

Citation:

---

Envoyé par stof

-job nocturne : c'est un terme technique? Quel rapport, en fait non ces traitements vont tourner en journée

---

Je me suis mal exprimé, je voulais dire que sans bind variable la requête sera reparsée à chaque fois que la liste des valeurs soumises sera différentes.
Donc si cette requête est fortement utilisée la base parsera inutilement de nombreuses fois la requête.
Si cette requête est assez peu exécutée, c'est moins problématique.

Citation:

---

Envoyé par stof

CREATE OR REPLACE context my_ctx USING my_ctx_procedure où j'ai insufficient privileges

---

CREATE CONTEXT

Citation:

---

To create a context namespace, you must have CREATE ANY CONTEXT system privilege.

---

Citation:

---

Envoyé par stof

-mais où se trouve ma requête dans tout ça?

---

Dans la procédure P :

Code:

---

open c FOR SELECT * FROM t WHERE col IN (SELECT * FROM IN_LIST);

---

Code:

---

1 2 3 4

select * from table t where t.table like decode(&v_param,3,%2%,--si &v_param = 3 alors comprendre 120 et 2 5)--sinon 5

---

bon courage

Ah, mais attendez, il y a mieux!! 8-)

Code:

---

1 2 3 4

select t.champ1, t.champ2 from MATABLE t where t.champ3 in (select * from table(SPLITLST(V_MY_LIST))))

---

avec :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

create or replace FUNCTION SPLITLST ( p_list IN VARCHAR2, p_del IN VARCHAR2 Default ',' ) Return SPLIT_TABLE_STR pipelined Is l_idx pls_integer; l_list varchar2(32767) := p_list; l_value varchar2(32767); Begin loop l_idx := instr(l_list,p_del); if l_idx > 0 then pipe row(substr(l_list,1,l_idx-1)); l_list := substr(l_list,l_idx+length(p_del)); else pipe row(l_list); exit; end if; end loop; Return; End splitlst;

---

C'est pas mal ça non? J'ai juste à définir ma liste V_MY_LIST en fonction des différents cas '1, 20, 150' dans un cas, '5' dans l'autre, etc.

Oui ça fonctionne aussi, c'est la version "If you are in Oracle 8i" du lien "Varying in lists" proposé ci-dessus.

A peu de choses près oui, c'est vrai :oops:

Bon, par contre c'est bien beau de passer '1, 20, 150' en dur, mais c'est autre chose d'aller chercher une liste d'integer dans une table suivant certains critères à la place...

Citation:

---

Envoyé par stof

mais c'est autre chose d'aller chercher une liste d'integer dans une table suivant certains critères à la place...

---

Qu'est ce que tu veux dire par là ?
Si ce sont des id stockés dans une table, il y a forcément moyen de faire plus simple et plus performent que concaténer puis déconcaténer. Donne nous peut être un exemple réaliste de ce que tu veux faire.

Sinon pour concaténer en liste des valeurs d'une colonne, c'est par là :
String Aggregation Techniques

OK, bon, pour résumer :
-si c'est 2 requêtes différentes suivant les valeurs de "V_PARAM", je fais un OPEN FOR
-si c'est une condition sur une liste dont les valeurs vont dépendre de "V_PARAM", je peux certes faire selon certaines solutions proposées ici mais plus logiquement, il faut surtout faire une table de correspondance COR_champ3 qui ressemble à

Code:

---

1 2 3 4 5

COR_champ3 V_PARAM champ3 3 120 3 2 1 5

---

Code:

---

1 2 3 4

select t.champ1, t.champ2 from MATABLE t where t.champ3 in (select champ3 from COR_champ3 where V_PARAM = 3)

---

:oops::oops: