select Max d'un varchar

Bonjour,

dans une BDD Oracle j'ai la table suivante :

Code:

---

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:

---

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.

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

j'ai essayé ceci :

Code:

---

1 2 3 4

SELECT max(to_number(VALEURRef)) FROM TableA Group by REFERENCE

---

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

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:

---

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

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

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:

---

1 2 3 4 5

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

---

Merci bcp pour vos réponses.

Bonjour

le Max fonctionne bien.

Code:

---

SELECT Max(LPAD(VALEURRef,18))

---

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

Code:

---

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

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:

---

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:

---

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:

---

1 2 3 4 5

SELECT REFERENCE, SUM(VALEURRef) FROM TableA WHERE IS_NUMBER(VALEURRef) = 'TRUE' Group by REFERENCE

---

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

Code:

---

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:

---

1 2 3 4

select Reference , sum((select is_number(ValeurRef) from dual)) as ValeurRef from TableA group by Reference;

---

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:

---

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:

---

1 2 3 4

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

---

Résultat :

Code:

---

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:

---

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:

---

Select Sum(to_number(ValeurRef))

---

mais ca marche pas non plus.

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

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:

---

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 ?

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

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

Code:

---

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:

---

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:

---

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

---

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.

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

Que donne ces requêtes ?

Code:

---

1 2 3 4 5 6

desc is_number   select is_number('1') from dual;   select is_number('A') from dual;

---

Oracle 11.6.

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


Pièce jointe 337721

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

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

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 ?

je travaille dans une bdd d'un client. peut être c liée aux droits sur la Bdd

Le message d'erreur est tout de même relatif à la structure du code et non à un manque de droits

je ne peux pas essayé avec un autre éditeur. j'ai pas le droit d'avoir le mot de passe du shéma oracle dans lequel j'ai ma bdd.

je peux essayé sur mon pc personnel. la requête est la même sur une bdd SQL Server?

Je ne peux t'assurer que la syntaxe soit exactement la même car les fonctions diffèrent entre SGBD, mais doit s'en rapprocher
Une recherche sur google devrait te permettre d'y voir plus clair

Non ça ne fonctionnera pas sur SQL-Server, si les dialectes SQL se ressemblent, les langages de programmation T-SQL & PL/SQL sont très différents.

problème réglé avec

Code:

---

select Replace(ValRf, ' ', '') From MaTable Where not regexp_like(ValRef, '[:lower:]' )

---

et j'arrive à sommer les valeur.


Résultat : ca me donne des chiffres sans espace et me supprime les lignes qui contiennent les caractères sauf pour un seul enregistrement qui contient le mot "TEST".

une explication?

Oui, c'est le format de vos nombres, c'est à dire la représentation d'un nombre en tant que chaîne de caractères.
Si votre nombre est 9, la représentation est simple, ce sera '9'.
Si votre nombre est 320241.1, la représentation dépend de certains paramètres locaux / de base / de session, qu'on appelle NLS (National Language support).

En effet, en France ce nombre s'écrira '320 241,1'.
Aux USA ce serait '320,241.1'. Et cetera.

Dans cet exemple, la fonction to_number prend un format d'entrée, et des paramètres NLS : le premier est le séparateur décimal, le second est le séparateur de milliers :

Code:

---

1 2 3

select to_number(ValRf, '999G999G999D99', 'nls_numeric_characters='', ''') from MaTable where not regexp_like(ValRef, '[:lower:]')

---

Merci Wladar pour votre réponse.

j'ai exécuté ta requête et le résultat est le suivant :

Ora-12702: la chaine de paramètre NLS n'est pas valide dans la fonction SQL

Citation:

---

Envoyé par laure07

Résultat : ca me donne des chiffres sans espace et me supprime les lignes qui contiennent les caractères sauf pour un seul enregistrement qui contient le mot "TEST".

---

TEST est en majuscule...
Qu'est ce que ça donne comme ça :

Code:

---

Where not regexp_like(lower(ValRef), '[:lower:]' )

---

Code:

---

1 2 3 4

select Reference , sum((select tst_num(ValeurRef) from dual)) as ValeurRef from TableA group by Reference

---

Inutile de faire un from dual !

Code:

---

1 2 3 4

select Reference , sum(tst_num(ValeurRef)) as ValeurRef from TableA group by Reference

---

Garuda, je vous invite à faire quelques recherches sur le mécanisme de cache des fonctions dans Oracle.
La syntaxe je vous l'accorde n'est pas naturelle, mais le résultat est là.

Bonjour,

Code:

---

Where not regexp_like(lower(ValRef), '[:lower:]' )

---

=> enlève bien le mot TEST.

Ma requête donne le bon résultat.

Merci bcp à vous tous :applo::applo:

Citation:

---

Envoyé par Waldar

Garuda, je vous invite à faire quelques recherches sur le mécanisme de cache des fonctions dans Oracle.
La syntaxe je vous l'accorde n'est pas naturelle, mais le résultat est là.

---

jamais eu de problème de perf en appelant directement une fonction !
Et à mon avis pas un exemple à suivre pour un débutant !