Discussion :

[laure07]

Bonjour,

dans une BDD Oracle j'ai la table suivante :

Code :

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

1
2
3
4
5
6
7
 
CREATE TableA
(
 
  REFERENCE          INTEGER,
  VALEURRef          VARCHAR2(255 BYTE) 
)

La Requête qui doit afficher le max de la colonne VALEURRef par Reference est la suivante :

Code :

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

1
2
3
4
 
SELECT  max(VALEURRef)     
FROM TableA
Group by REFERENCE

La valeurRef la plus élevée est "199 647,00" mais le résultat de la requête affiche "88 440,00". c'est surement dû au champs valeurRef qui est de type Varchar2.
comment afficher la vrai valeur la plus élevée cad "199 647,00"?

PS : je n'ai le droit de changer le type de la colonne valeurRef dans la BDD.

[skuatamad]

Si VALEURREF ne contient que des valeurs numériques, faites un TO_NUMBER(VALEURREF).

[laure07]

j'ai essayé ceci :

Code :

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

1
2
3
4
SELECT  max(to_number(VALEURRef))     
FROM TableA
Group by REFERENCE

Résultat : message d'erreur : Ora-01722 : Nombre bib valide

[Scriuiw]

C'est donc que la colonne ne possède pas que des valeurs numériques.

Une solution bis mais sans pouvoir t'assurer du résultat ente les valeurs numériques et alphanumériques

Code :

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

1
2
3
4
 
SELECT  max(LPAD(VALEURRef,18,'0'))     
FROM TableA
Group by REFERENCE

Et si tu veux t'assurer de n'avoir que des résultats numériques, alors soit tu passes par une expression régulière pour n'avoir que les champs entièrement numérique ou alors par une fonction PL/SQL

[laure07]

je vous remercie pour votre réponse.

j'ai essayé le LPAD. Résultat : il m'a affiché la valeur supérieure.

sinon effectivement je ne veux avoir que les valeurs numérique. quelle est cette expression régulière svp?

Merci d'avance

[Scriuiw]

Je pense que l'expression va dépendre du fait d'avoir ou non des décimales dans un premier temps, après je ne suis pas expert des expressions régulières non plus...

Sinon pour vous aider, voici une fonction qui pourrait faire l'affaire pour ne sélectionner que les valeurs numériques :

Code :

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

1
2
3
4
5
 
SELECT  REFERENCE, max(VALEURRef)     
FROM TableA
WHERE LENGTH(TRIM(TRANSLATE(VALEURRef, ' +-.,0123456789', ' '))) IS NULL
Group by REFERENCE

[laure07]

Merci bcp pour vos réponses.

[laure07]

Bonjour

le Max fonctionne bien.

Code :

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

SELECT  Max(LPAD(VALEURRef,18))

Maintenant je souhaite plutot sommer, donc j'ai remplacer Max par Sum

Code :

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

SELECT  Sum(LPAD(VALEURRef,18))

ca me donne le message derreur :Ora 01722 Nombre non valide

je ne vois pas pourquoi le Sum ne marche pas?

Merci d'avance

[Scriuiw]

Le SUM ne fonctionne pas car la colonne ne contient pas que des numériques.
Par exemple, il se pourrait que tu aies ce type de valeur :

Code :

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

1
2
3
4
0.1.1
..1
1..4
etc..

Dans ce cas, passer par une fonction pour contrôler que la chaîne est belle et bien numérique :

Code :

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

1
2
3
4
5
6
7
8
CREATE OR REPLACE function IS_NUMBER(str in varchar2) return varchar2 IS
dummy number;
begin
dummy := TO_NUMBER(str);
return ('TRUE');
Exception WHEN OTHERS then
return ('FALSE');
end;

Puis l'appeler dans la requête

Code :

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

1
2
3
4
5
 
SELECT  REFERENCE, SUM(VALEURRef)     
FROM TableA
WHERE IS_NUMBER(VALEURRef) = 'TRUE'
Group by REFERENCE

[Waldar]

Sans dire que la réponse de Scriuiw n'est pas bonne, je vous invite à utiliser cette fonction-là :

Code :

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

1
2
3
4
5
6
7
8
9
10
create or replace function is_number (p$_str in varchar2)
return number deterministic
is
begin
    return (to_number(p$_str));
 
    exception when VALUE_ERROR then
        return null;
end;
/

Vous l'appelez par la suite de cette façon, l'écriture est un peu moins limpide mais elle permet d'utiliser le cache de fonction, qui donne de meilleures performances :

Code :

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

1
2
3
4
  select Reference
       , sum((select is_number(ValeurRef) from dual)) as ValeurRef
    from TableA
group by Reference;

[laure07]

Merci bcp pour vous réponses.

en fait j'ai essayé un truc plus simple et ça a l'air de marché aussi.

à la base j'ai les valeurs de ma colonne "ValeurRef" sont les suivantes :

Code :

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

1
2
3
4
5
6
 
- 527 040,00
- l'article A de la catégorie A soldé
- 0
- l'article B de la catégorie B soldé
- 858 000,00

j'ai essayé cette requête :

Code :

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

1
2
3
4
 
Select IdRef, ValeurRef  
From MaTable 
Where not regexp_like(ValeurRef, '[:lower:]')

Résultat :

Code :

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

1
2
3
4
 
- 527 040,00
- 0
- 858 000,00

donc le regexp_like me supprime bien tous les champs alphabétique.


mais quand j'essai de sommer ces valeurs ça marche pas :

Code :

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

1
2
3
4
5
 
Select IdRef, Sum(ValeurRef)
From MaTable 
Where not regexp_like(ValeurRef, '[:lower:]')
Group by IdRef

Résultat : message d'erreur : ORA-01722:Nombre non valide.

j'ai essayé avec

Code :

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

Select Sum(to_number(ValeurRef))

mais ca marche pas non plus.

Une idée de comment je peux sommer ces valeurs svp?

[Scriuiw]

Comme expliqué précédemment, vous devez avoir des valeurs qui ne sont pas alphanumériques en tant que telle mais qui ne sont pas non plus des numériques valides.

Code :

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

1
2
3
4
 
0.1.1
..1
1..4

L'unique façon de s'assurer qu'il s'agit bien d'une valeur transposable en numérique est de la convertir en numérique via TO_NUMBER.

De plus, attention au séparateur décimale utilisé.
Les solutions que nous vous avons proposé ne donne pas un résultat correct ?
Ou alors vous ne pouvez pas créer de fonctions sur votre BDD ?

[laure07]

Bonjour,

comme mentionné dans mon dernier message, j'ai deja essayé To_number mais ca n'a rien changé.

j'ai essayé la solution proposé par Scriuiw -> Résultat : 0 (càd qu'il ne reconnu que 0 autant que nombre et donc ne somme que le 0)

par contre à l'exécution de la solution de waldar ...J'ai eu un message d'erreur : PLS-00103:Symbole""rencontré à la place d'un symbole suivants : (begin case declar en execption exit for goto if loop mod null pragma raise return select update while with <ident

[Scriuiw]

Vous avez bien créé la fonction avant d'exécuter la requête ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
create or replace function tst_num (p$_str in varchar2)
return number deterministic
is
begin
    return (to_number(p$_str));
    exception when VALUE_ERROR then
        return null;
end;
/

Fonction créée.

Exécution de la requête de Waldar

Code :

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

1
2
3
4
5
6
7
8
9
10
11
  select Reference
        , sum((select tst_num(ValeurRef) from dual)) as ValeurRef
     from TableA
 group by Reference
 /

REFERENCE  VALEURREF
--------- ----------
        1         26
        2         17

Contenu de la table TableA

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 * from tablea;

REFERENCE VALEURREF
--------- ---------
        1 1
        1 R5
        1 10
        1 AAABBB
        1 15
        2 1..1
        2 5
        2 12
        2 KK

[laure07]

oui biensure.
j'ai fait copié collé du script de la fonction et j l'ai exécuté. ça me donne toujours le message d'erreur.

PS : je suis sur BDD Oracle.

[Scriuiw]

Quelle version d'Oracle ?
Lors de sa création, pas d'erreur ?

Que donne ces requêtes ?

Code :

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

1
2
3
4
5
6
 
desc is_number
 
select is_number('1') from dual;
 
select is_number('A') from dual;

[laure07]

Oracle 11.6.

le résultat de chaque requetés est dans le document suivant :


ca.docx

[Scriuiw]

La fonction est invalide.
Quel est le message d'erreur lors de sa création ?

[laure07]

Le message d'erreur est le suivant :
PLS-00103:Symbole""rencontré à la place d'un symbole suivants : (begin case declar en execption exit for goto if loop mod null pragma raise return select update while with <ident

[Scriuiw]

Il doit s'agir d'un problème au niveau de ton éditeur SQL
Peux-tu essayer de la créer via SQLplusw ou un autre éditeur ?