Analyser le contenu d'un champ Oracle

Version imprimable

18/10/2018, 17h21
marieo

Analyser le contenu d'un champ Oracle
Bonjour,
Je cherche à vérifier si un champ ne contient que des valeurs autorisées

J'ainsi une table de la forme suivante
Code:

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:

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
18/10/2018, 22h44
Ikebukuro

Créer une colonne virtuelle avec la fonction DECODE, ça marche pas?
19/10/2018, 08h38
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:

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
19/10/2018, 10h05
Ikebukuro

Attention,

Code:

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"
19/10/2018, 10h48
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.
19/10/2018, 10h55
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...
19/10/2018, 11h34
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.

Code:

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  
);

Sans REGEXP :

Code:

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:

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

22/10/2018, 13h25
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

Citation:

Envoyé par 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

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:

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','|')) ;