Discussion :

[marieo]

Bonjour,
Je cherche à vérifier si un champ ne contient que des valeurs autorisées

J'ainsi une table de la forme suivante

Code :

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

1
2
3
4
5
6
7
8
 
ID_CLE, NB_VALEURS, DONNEES_A_VERIFIER
1, 2, 18|28
2, 2, 7|34
3, 4, 18|28|38|48
4, 1, 8
5, 3, 18|27|48
...

J'aimerais savoir comment écrire une requête SQL qui extrait de ma table uniquement les enregistrements pour lesquels les valeurs du champ "DONNEES_A_VERIFIER" sont parmi celles autorisées : 18, 28, 38, 48 et qui ajoute un champ pour donner le résultat de la vérification (NOK si autre chose que 18, 28, 38 ou 48 à partir du moment où on a au moins 18 ou 28 ou 38 ou 48 ?

Je n'arrive pas à savoir comment faire.

En résultat, j'aimerais avoir ceci :

Code :

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

1
2
3
4
5
 
ID_CLE, NB_VALEURS, DONNEES_A_VERIFIER, VERIF
1, 2, 18|28, OK
3, 4, 18|28|38|48, OK
5, 3, 18|27|48, NOK

Merci beaucoup à tous pour vos idées.

MarieO

[Ikebukuro]

Créer une colonne virtuelle avec la fonction DECODE, ça marche pas?

[marieo]

Bonjour,

Ca doit certainement marcher, mais ma question est qu'est-ce que je mets dans le DECODE ?

Si je fais un DECODE(DONNEES_A_VERIFIER,18,...) jamais je n'aurais OK ou NOK car je dois regarder les valeurs qui ne sont pas des '|'.

Je sais que je vais faire une clause WHERE avec la condition

Code :

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

 where DONNEES_A_VERIFIER like '%18%' or DONNEES_A_VERIFIER like '%28%' or DONNEES_A_VERIFIER like '%38%' or DONNEES_A_VERIFIER like '%48%'

Par contre, je ne sais pas comment faire ce DECODE pour qu'il me dise
- OK si je n'ai que 18 et/ou 28 et/ou 38 et/ou 48 dans DONNEES_A_VERIFIER
- NOK à partir du moment où j'ai autre chose que 18, 28, 38 ou 48 dans DONNEES_A_VERIFIER.

Merci encore.
MarieO

[Ikebukuro]

Attention,

Code :

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

where DONNEES_A_VERIFIER like '%18%' or DONNEES_A_VERIFIER like '%28%' or DONNEES_A_VERIFIER like '%38%' or DONNEES_A_VERIFIER like '%48%'

va te retourner la ligne "5, 3, 18|27|48, NOK"

[marieo]

C'est bien ce que je veux.

En résultat, je veux avoir TOUTES les lignes qui ont au moins 18 ou 28 ou 38 ou 48 dans le champ "DONNEES_A_VERIFIER", et pas du tout les lignes qui ne contiennent pas ces valeurs dans DONNEES_A_VERIFIER.

Par contre, je veux en plus un champ "VERIF" qui me dira si l'enregistrement retourné est "OK (que 18 et/ou 28 et/ou 38 et/ou 48) ou "NOK" si au moins une valeur de DONNEES_A_VERIFIER n'est pas 18, 28, 38 ou 48.

[Ikebukuro]

"je veux avoir TOUTES les lignes qui ont au moins 18 ou 28 ou 38 ou 48 "
Je ne comprends alors pas pourquoi tu ne veux pas "5, 3, 18|27|48" car elle a 18 et 48...

[marieo]

Si, je la veux (cf. tableau résultat) mais flaguée "NOK" car la valeur "27" est une erreur de saisie qu'il faudra corriger...

Je cherche toutes les lignes avec des 18, 28, 38, 48 (même celles qui contiennent autre chose) afin de voir s'il y a des lignes à "corriger" (avec autre chose que 18, 28, 38, 48).

Mais je ne veux pas les lignes qui ne contiennent pas au moins 18 ou 28 ou 38 ou 48.

[JeitEmgie]

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
with data as (      select 1 as nsq, '18|28' as to_check from dual   
    union  
    select 2 as nsq, '7|34' as to_check from dual   
    union  
    select 3 as nsq, '8|10|28' from dual 
)  
, checker as (  
    select d.nsq, splitted.value from data d,  
    lateral (  
        SELECT REGEXP_SUBSTR(d.to_check, '[^|]+', 1, LEVEL) AS value FROM dual  
            CONNECT BY REGEXP_SUBSTR(d.to_check, '[^|]+', 1, LEVEL) IS NOT NULL  
    ) splitted 
)  
select distinct nsq from checker where value not in (
        SELECT REGEXP_SUBSTR('18|28|38|48', '[^|]+', 1, LEVEL) AS value FROM dual  
            CONNECT BY REGEXP_SUBSTR('18|28|38|48', '[^|]+', 1, LEVEL) IS NOT NULL  
);

[McM]

Sans REGEXP :

Code :

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

1
2
3
4
5
6
7
WITH t AS (SELECT 1 id_cle, 2 nb_valeurs, '18|28' donnees_a_verifier FROM dual
UNION SELECT 3 id_cle, 4 nb_valeurs, '18|28|38|48' donnees_a_verifier FROM dual
UNION SELECT 5 id_cle, 3 nb_valeurs, '18|27|48' donnees_a_verifier FROM dual
)
SELECT id_cle, nb_valeurs, donnees_a_verifier, 
	CASE WHEN REPLACE(REPLACE(REPLACE(REPLACE('|'|| REPLACE(donnees_a_verifier, '|', '||') ||'|', '|18|'), '|28|'), '|38|'), '|48|') IS NULL THEN 'Ok' ELSE 'KO' END verif
FROM t

Code :

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

1
2
3
4
ID_CLE	NB_VAL	DONNEES	VERIF
1	2	18|28	Ok
3	4	18|28|38|48	Ok
5	3	18|27|48	KO

[marieo]

Merci beaucoup à tous les 2 pour vos solutions.

Je vais me servir de la solution sans REGEX car nous n'avons pas LATERAL dans notre base Oracle puisque nous sommes en 11GR2.

MarieO

[JeitEmgie]

Merci beaucoup à tous les 2 pour vos solutions.

Je vais me servir de la solution sans REGEX car nous n'avons pas LATERAL dans notre base Oracle puisque nous sommes en 11GR2.

MarieO

Il y a moyen de remplacer lateral() par un table(cast(multiset( ... )...))
ce qui aurait au moins le mérite de garder un query paramétrisable...

ou alors avec une fonction :

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
 
create function string2numberlist( i_str IN VARCHAR2, i_sep IN CHAR ) RETURN 
    sys.odcinumberlist
IS
    w_pattern VARCHAR2(16 char) := '[^' || i_sep || ']+' ;
    w_result sys.odcinumberlist ;
BEGIN
    SELECT to_number(REGEXP_SUBSTR(i_str, w_pattern, 1, LEVEL)) 
        bulk collect into w_result 
        FROM dual  
            CONNECT BY REGEXP_SUBSTR(i_str, w_pattern, 1, LEVEL) IS NOT NULL ;
    return w_result ;
END string2numberlist ;
 
with data as (  
    select 1 as nsq, '18|28' as to_check from dual   
    union  
    select 2 as nsq, '7|34' as to_check from dual   
    union  
    select 3 as nsq, '8|10|28' from dual 
)  
, checker as (  
    select d.nsq, splitted.column_value as value from data d,  
        string2numberlist(d.to_check, '|') splitted
)  
select distinct nsq from checker where value not in (select * from string2numberlist('8|18|28|38|48','|')) ;