Discussion :

[chekkal]

Bonjour à tous,

Ma question est un peu compliquée alors je vais essayer d'être le plus explicite possible. Voila j'ai une TABLE FICHE contenant les champs suivants (IDENT varchar(20), DESIGNATION VARCHAR(100)) et comme clé primaire IDENT et en prenant l'exemple de saisie suivant

IDENT DESIGNATION
ARM1 ARMOIRE COULOIR
ARM2 ARMOIRE BUREAU
BURE1 BUREAU CLASSIQUE
BURE2 BUREAU TRADITIONNEL

....
ARM10 ARMOIRE CAISSE
ARM11 ARMOIRE CHAMBRE
ect

avec la commande sql suivante

Code :

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

1
2
 
select * from FICHE order by ident

j’obtiens ce résultat

IDENT DESIGNATION
ARM1 ARMOIRE COULOIR
ARM10 ARMORE CAISSE
ARM11 ARMOIRE CHAMBRE
ARM2 ARMOIRE BUREAU
BURE1 BUREAU CLASSIQUE
BURE2 BUREAU TRADITIONNEL
ect

la question est comment ordonner par rapport à la partie alphabétique et la partie numérique en même temps sachant que la partie alphabétique a une taille variable .

[escartefigue]

Bonjour,

Problèmes avérés :
- la position des chiffres peut varier : dans votre extrait, le 1er chiffre est soit en 4ème (ARM1) soit en 5ème position (BURE1)
- la valeur du 1er chiffre peut varier : dans votre extrait c'est soit 1 (ARM1) soit 2 (ARM2)

Problèmes potentiels
- y a -t- il systématiquement au moins un chiffre dans l'identifiant ?
- ne peut on pas avoir des identifiants qui contiennent alternativement des chiffres et des lettres (ex: ARM3XX5) en ce cas quelle règle appliquer ?
- ne peut on avoir des caractères minuscules, accentués, spéciaux, en ce cas comment souhaitez vous les trier ?

Bref, c'est un joyeux souk

De plus l'utilisation d'un identifiant primaire de type (var)char est une véritable hérésie

Si vous en avez la possibilité, la seule bonne solution est de refaire une table avec un identifiant asémantique pour en garantir la stabilité et concis pour optimiser les performances.
Le meilleur moyen est une colonne de type integer, dont la valeur est attribuée par le SGBD (identity column).

[chekkal]

Bonjour,

Problèmes avérés :
- la position des chiffres peut varier : dans votre extrait, le 1er chiffre est soit en 4ème (ARM1) soit en 5ème position (BURE1)
- la valeur du 1er chiffre peut varier : dans votre extrait c'est soit 1 (ARM1) soit 2 (ARM2)

La partie alphabétique est toujours en premier dans le champs "IDENT" EXPLE(MARTI1, MARTI2,...MARTIn, ARB1,ARB2..,ARBn)

- y a -t- il systématiquement au moins un chiffre dans l'identifiant ?

Il peut y avoir, plusieurs chiffres et plusieurs caractères. La seule règle c'est que la partie alphabétique est en premier.

- ne peut on pas avoir des identifiants qui contiennent alternativement des chiffres et des lettres (ex: ARM3XX5)
- ne peut on avoir des caractères minuscules, accentués, spéciaux, en ce cas comment souhaitez vous les trier ?

NON.

De plus l'utilisation d'un identifiant primaire de type (var)char est une véritable hérésie

Je ne comprends pas où est le problème ?

Bref, c'est un joyeux souk

évitez les sous-entendus s'il vous plait.

[escartefigue]

il peut y avoir, plusieurs chiffres et plusieurs caractères. la seule règle c'est que la partie alphabétique est en premier.

Ma question était, y en a -t- il forcément au moins un ? autrement dit, peut il y avoir des identifiants sans aucun chiffre

je comprend pas ou est le probléme!?

C'est une règle de base de modélisation : l'identifiant primaire doit être stable, c'est à dire invariant dans le temps.
Or ici vous avez utilisé un identifiant ayant une signification (ARMxx pour les armoires, BUREyy pour le bureaux)
Si demain quelqu'un décide que le BUREAU BURE33 n'est finalement pas un bureau mais une commode, il faut en modifier son identifiant, ce qui aura comme conséquence
- la mise à jour de l'index primaire et de la page DATA pour la table concernée
- le déplacement éventuel des pages INDEX et DATA (risque accru si la longueur de l'identifiant augmente)
- la mise à jour en cascade de toutes les occurrences liées dans les tables filles liées par des contraintes de type "reference"
- le déplacement éventuel des pages INDEX et DATA pour les occurrences des tables filles concernées
Bref une multitudes de d'entrées-sorties inutiles qui vont plomber les perfs, et désorganiser la base de données
De plus, pour un nombre d'identifiants égal, le type (var)char est beaucoup plus encombrant qu'un type integer, là encore, ça pénalise les performances
Le (var)char est sensible à la collation, ce qui impacte le résultat des tris et regroupements (ORDER BY, DISTINCT)
Enfin, pour certaines opérations, le varchar nécessite un réa alignement sur une longueur fixe, ce qui a également un coût, rebelotte les performances !

évitez les sous-entendu s'il vous plait.

Il n'y a aucun sous-entendu, du char et pire encore du varchar, c'est une très mauvaise idée pour l'identifiant primaire. Un identifiant alternatif de type (var)char ne pose lui aucun souci (par exemple pour une recherche sur le nom), mais encore une fois, pas le primaire !

[SergioMaster]

Bonjour,

à mon avis, faut pas rêver c'est votre structure de table/base de données qu'il faut revoir.
Outre les hérésies pointées par escartefigue, je remarque que selon vos données vous avez en quelque sorte raté une notion de famille (ARMOIRE, BUREAU) dans votre analyse


Il y a certainement moyen de faire ce que vous demandez en passant par une procédure ou une fonction UDF personnelle mais le jeu n'en vaut pas la chandelle

J'ai passé un peu de temps sur le truc qui suit (ce qui fait que la discussion à évoluer depuis) , pour la partie procédure vous pouvez vous en inspirer

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
27
28
29
30
31
32
 
SET TERM ! ;
 
EXECUTE BLOCK 
RETURNS (ABLOC VARCHAR(10),
         ANUMBER INTEGER)
AS
DECLARE I SMALLINT;
DECLARE L SMALLINT;
DECLARE PARTN VARCHAR(10);
DECLARE ATEXT VARCHAR(10);
 
BEGIN
ATEXT='AZRE10';
L=CHAR_LENGTH(ATEXT);
I=0;
WHILE (L>=I) DO 
BEGIN
 PARTN=SUBSTRING(ATEXT FROM L-i FOR I+1);
 ABLOC=SUBSTRING(ATEXT FROM 1 FOR L-i-1);
 ANUMBER=CAST(PARTN AS INTEGER);
 I=I+1;
END 
SUSPEND;
 WHEN ANY DO BEGIN  -- erreur de conversion
   I=I-1;
   PARTN=SUBSTRING(ATEXT FROM L-i FOR I+1);
   ABLOC=SUBSTRING(ATEXT FROM 1 FOR L-i-1);
   ANUMBER=CAST(PARTN AS INTEGER);
   SUSPEND;
  END           
END!

P.S. le "joyeux souk" n'est pas un sous-entendu à votre nationalité c'est devenu une expression plutôt commune dans la langue française escartefigue aurait pu écrire à la place de souk : bordel (pas très poli) ou capharnaüm tout aussi bien, pas la peine de prendre la mouche !

[escartefigue]

P.S. le "joyeux souk" n'est pas un sous-entendu à votre nationalité c'est devenu une expression plutôt commune dans la langue française escartefigue aurait pu écrire à la place de souk : bordel (pas très poli) ou capharnaüm tout aussi bien, pas la peine de prendre la mouche !

Je confirme, je n'avais d'ailleurs pas prêté attention à la nationalité de Chekkal

@Chekkal : toutes mes excuses si toutefois j'ai été maladroit, c'était tout à fait involontaire

[SergioMaster]

J'ai légèrement modifié cette "procédure" pour les cas où !

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
27
28
29
30
31
32
SET TERM ! ;
 
EXECUTE BLOCK 
RETURNS (ABLOC VARCHAR(10),
         ANUMBER INTEGER)
AS
DECLARE I SMALLINT;
DECLARE L SMALLINT;
DECLARE PARTN VARCHAR(10);
DECLARE ATEXT VARCHAR(10);
 
BEGIN
ATEXT='ARM10A';   -- codification non conforme   
L=CHAR_LENGTH(ATEXT);
I=0;
WHILE (L>=I) DO 
BEGIN
 PARTN=SUBSTRING(ATEXT FROM L-i FOR I+1);
 ABLOC=SUBSTRING(ATEXT FROM 1 FOR L-i-1);
 ANUMBER=CAST(PARTN AS INTEGER);
 I=I+1;
END 
SUSPEND;
 WHEN ANY DO BEGIN  -- erreur de conversion
   I=I-1;
   PARTN=SUBSTRING(ATEXT FROM L-i FOR I+1);
   ABLOC=SUBSTRING(ATEXT FROM 1 FOR L-i-1);
   IF (CHAR_LENGTH(PARTN)=0)  THEN ANUMBER=0;
                              ELSE ANUMBER=CAST(PARTN AS INTEGER);
   SUSPEND;
  END           
END!

et me pose la question de savoir si une expression régulière (SIMILAR) ne pourrait pas fonctionner
[Edit] les expressions régulières dans un substring ne sont valable qu'à partir de firebird 3.0.3 https://firebirdsql.org/file/documen...02ch09s05.html

[chekkal]

Bonjour,

Ma question était, y en a -t- il forcément au moins un ? autrement dit, peut il y avoir des identifiants sans aucun chiffre

NON, la saisie est toujours ALPHABETIQUE+NUMERIQUE, SEULE LA TAILLE CHANGE.

C'est une règle de base de modélisation : l'identifiant primaire doit être stable, c'est à dire invariant dans le temps.

Je travaille selon une demande fournie par un client.

De plus, pour un nombre d'identifiants égal, le type (var)char est beaucoup plus encombrant qu'un type integer, là encore, ça pénalise les performances
Le (var)char est sensible à la collation, ce qui impacte le résultat des tris et regroupements (ORDER BY, DISTINCT)
Enfin, pour certaines opérations, le varchar nécessite un réa alignement sur une longueur fixe, ce qui a également un coût, rebelotte les performances !

J'ai utilisé le varchar, parce que la saisie peut varier plus tard vers d'autres caractères.

je remarque que selon vos données vous avez en quelque sorte raté une notion de famille (ARMOIRE, BUREAU) dans votre analyse

Non, je n'ai pas oublié la Notion de Famille, mais je me soumets seulement à une demande d'un client.

Il y a certainement moyen de faire ce que vous demandez en passant par une procédure ou une fonction UDF personnelle mais le jeu n'en vaut pas la chandelle

J'ai passé un peu de temps sur le truc qui suit (ce qui fait que la discussion à évoluer depuis) , pour la partie procédure vous pouvez vous en inspirer

Franchement, je n'ai pas compris cette procédure. En quoi peut-elle m'aider dans "order by" ?

[SergioMaster]

Bonjour,

Non, je n'ai pas oublié la Notion de Famille, mais je me soumets seulement à une demande d'un client.

Ce n'est pas pour cela qu'il ne faut pas lui signaler ! cela fait partie du job d'analyste, le client n'y connait peut être rien en base de données et demain il peut très bien vouloir , par exemple, ne voir que les Bureaux

Franchement, je n'ai pas compris cette procédure. En quoi peut-elle m'aider dans "order by" ?

Vous remarquerez qu'il ne s'agit pas d'une procédure mains d'un BLOCK qui permet de vérifier un algorithme de transformation en deux parties d'une donnée.
j'ai pensé, à tort semble t-il, que vous auriez pu :
1- la tester pour voir (en changeant la valeur de ATEXT à chaque essai)
2- l'analyser pour comprendre le principe
3- l'appliquer comme suit

ce qui suit n'est pas testé par manque de temps, il manque peut être des : par ci par là
fournir un script de création de la table et d'insertion de données m'aurait certainement permis de le faire!

Construisez une procédure

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
27
28
29
30
31
32
33
34
35
36
 
SET TERM ^ ;
 
CREATE PROCEDURE  FICHETRIE
RETURNS 
 ( IDENT VARCHAR(10),
   DESIGNATION VARCHAR(100),
   PARTALPHA VARCHAR(10),
   PARTNUMBER INTEGER )
AS 
DECLARE I SMALLINT;
DECLARE L SMALLINT;
DECLARE PARTNA VARCHAR(10); 
BEGIN
  FOR SELECT IDENT,DESIGNATION from FICHE INTO :IDENT,:DESIGNATION DO 
  BEGIN
     L=CHAR_LENGTH(IDENT);
     I=0;
    WHILE (L>=I) DO 
       PARTNA=SUBSTRING(IDENT FROM L-i FOR I+1);
       PARTALPHA=SUBSTRING(IDENT FROM 1 FOR L-i-1);
       PARTNUMBER=CAST(PARTNA AS INTEGER);
       I=I+1;
    END -- WHILE
    WHEN ANY DO BEGIN  -- erreur de conversion
     I=I-1;
     PARTNA=SUBSTRING(IDENT FROM L-i FOR I+1);
     PARTALPHA=SUBSTRING(IDENT FROM 1 FOR L-i-1);
     IF (CHAR_LENGTH(PARTN)=0)  THEN PARTNUMBER=0;
                                             ELSE PARTNUMBER=CAST(PARTN AS INTEGER);
    END -- ERREUR          
    SUSPEND;
 END  -- FOR SELECT 
END^
 
SET TERM ; ^

utilisez alors

Code :

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

SELECT IDENT,DESIGNATION FROM FICHETRIE ORDER BY PARTALPHA,PARTNUMBER

[escartefigue]

Je travaille selon une demande fournie par un client.

[. . .]

Non, je n'ai pas oublié la Notion de Famille, mais je me soumets seulement à une demande d'un client.

Chacun doit être dans son rôle : le client exprime le "quoi", l'informaticien choisit le "comment" obtenir ce "quoi"
Le client souhait identifier ses meubles par un code genre "BURE2", "ARM1" etc... qu'à cela ne tienne, conservez cet identifiant comme clef alternative, et choisissez en plus un identifiant primaire dans les règles de l'art.
Ca ne gênera en rien la satisfaction du client, et c'est même tout le contraire car un identifiant primaire bien construit est d'une part un gage de fiabilité et d'autre part un gage de performances.