Tri d'une vue par n° de commande [Résolu]

[juju05]

Bonjour,

Je cherche à trier une vue sql par n° de commande. Ce n° est alphanumérique et s'écrit de la forme : ssaa-sequence.
La longueur du n° de séquence n'est pas fixe. Je peux donc avoir 2011-99 et 2011-1012.
Je souhaite avoir un tri décroissant de sorte que 2011-10001 se trouve avant 2011-9999.

Je pense fixer un nombre de caractères maximum à 12. Je voudrai concaténer ssaa au n° de séquence complété à gauche par des zéro de manière à atteindre les 12 caractères et trier par cette concaténation.
Je suis sur une bd Oracle.

Julien.

[Waldar]

Quelle idée ! Pourquoi ne pas avoir deux colonnes, année et numéro de commande, et gérer l'affichage par une vue avec une simple concaténation ?

Si vous pouvez encore changer votre modèle, faites-le, vous ne respectez pas le principe d'atomicité des données de la première forme normale.

[pacmann]

Salut !

Envoyé par Waldar

Quelle idée ! Pourquoi ne...gérer l'affichage par une vue avec une simple concaténation ?

Si j'ai bien compris, c'est justement une vue

Envoyé par juju

Je cherche à trier une vue sql par n° de commande.

Bref, juju, tu peux parser tout ça sur le "-" :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SQL> ed
écrit file afiedt.sql
 
  1  WITH t AS (
  2  SELECT '2011-10001' cd FROM DUAL UNION ALL
  3  SELECT '2011-9999' FROM DUAL UNION ALL
  4  SELECT '2010-11111' FROM DUAL)
  5  SELECT substr(cd, 1, 4), lpad(substr(cd, instr(cd, '-') + 1), 12, '0')
  6  FROM t
  7* ORDER BY substr(cd, 1, 4), lpad(substr(cd, instr(cd, '-') + 1), 12, '0')
SQL> /
 
SUBSTR(CD,1,4)   LPAD(SUBSTR(CD,INSTR(CD,'-')+1),12,'0')
---------------- ------------------------------------------------
2010             000000011111
2011             000000009999
2011             000000010001

=> Avec INSTR tu cherches la position du '-'
=> Avec SUBSTR tu découpes la chaîne d'une position A, pour un nombre de caractères B (si le deuxième paramètre est omis, c'est jusqu'à la fin)
=> avec lpad, tu "shiftes" une chaîne en remplissant avec le troisième paramètre

Voilà

(Bien sûr, si tu veux ajouter ça dans la définition de la vue, il sera plus facile d'utiliser les colonnes d'origine)

[Oishiiii]

Bonsoir,

Envoyé par Waldar

Si vous pouvez encore changer votre modèle, faites-le, vous ne respectez pas le principe d'atomicité des données de la première forme normale.

En réalité la table qui aurait ce genre de valeur dans une de ses colonnes respecterait tout de même la première forme normale.
Le paragraphe auquel vous faites référence explique d'ailleurs que la notion d' "atomicité" est ambigüe et maintenant obsolète.

Pour un exemple de violation de première forme normale, voir par exemple : La première forme normale [Résolu]

Cela dit, je suis d’accord sur le fait qu'il s'agit ici certainement d'une erreur de modélisation et qu'il serait intéressant de créer deux colonnes. Une vue pourra éventuellement reconstruire le numéro de commande pour les utilisateurs.

[juju05]

Bonjour,

Je travaille sur un ERP sur lequel il est possible de définir le n° de commande tel quel afin que sur un écran de liste des commandes on puisse trier par celui-ci.

Chose faite très rapidement puisque cela ne fonctionne que pour les n° de séquence ayant le même nombre de caractères !

Merci pour vos réponses.

Julien.

[SQLpro]

Pour être vrai, la notion de 1FN à besoin du contexte de modélisation. Or à l'évidence, la requête demandée est catastrophique dans le modèle présenté par la vue.
Mais peut être la vue a fait une synthèse de deux colonnes....
Si dans la table cette données est en une seule colonne alors la 1FN n'est pas respectée compte tenu du contexte !

Lisez ce que je viens de publier à ce sujet : http://blog.developpez.com/sqlpro/p1...ances-petites/

A +

[Oishiiii]

Envoyé par SQLpro

Si dans la table cette données est en une seule colonne alors la 1FN n'est pas respectée compte tenu du contexte !

Je persiste La table serait tout de même en 1NF avec une colonne numéro de commande de ce style.

Nous n'avons pas la même compréhension de la définition de 1NF. Apparemment votre position n'a pas évoluée depuis cette discussion à ce sujet avec fsmrel ou vous semblez être en désaccord. Je me range du côté d'fsmrel à ce sujet.

Par exemple le viol de la notion d'atomicité (première forme normale), c'est à dire mettre plusieurs informations dans une même colonne est une erreur aux conséquences lourdes. Elle conduira tôt ou tard à des tables obsèdes et des acrobaties pour écrire des requêtes qui ne pourront jamais être optimisées.

Certes je suis entièrement d'accord avec vous, placer plusieurs informations (de même nature ou non) dans une colonne est une erreur de modélisation et c'est une source de complication, mais pas forcément une violation de 1NF si l'on suit la définition de Chris Date et fsmrel par exemple.

Bonne journée

[pacmann]

Pour moi, ce sont des problématiques différentes :
- La discussion dont tu donnes le lien traite de colonnes distinctes qui forment en quelques sortes une liste (et là oui, ça n'a presque rien à voir avec le 1NF). Dans la mesure où la liste des colonnes est fixe, le problème est à mon sens assez proche de celui de l'acceptation du NULL

- Ici, on se pose la question : est-ce qu'une colonne résultant sémantiquement de la composition /concaténation de deux autres colonnes respecte où non le principe de l'atomicité, et donc la 1NF ?

Pour cette dernière question, il y a plusieurs interprétations, et même plusieurs axes d'interprétations.
Si on reprend le poste de fsmrel, les différentes précisions qu'ils citent n'ont à mon goût pas le même sens :

Envoyé par fsmrel

Vu du SGBD, les valeurs des domaines sur lesquels chaque relation est définie doivent être atomiques.
Ce qui veut dire que, vu du SGBD, ces valeurs ne sont pas décomposables.

ET
(

Envoyé par fsmrel

Les valeurs des domaines ne sont ni des listes, ni des ensembles.

OU

Envoyé par fsmrel

Une table est en première forme normale si aucune de ses colonnes ne peut prendre de valeur qui soit elle-même un ensemble.

)

1) L'équivalence entre "être atomique" et "ne pas être une liste ou un ensemble" n'est pas immédiate (bien entendu, pour moi il n'y a pas d'équivalence ici...).
Dans le cas du numéro résultant de la concaténation de deux données définies, on peut penser que la valeur n'est pas atomique. Par contre, je ne vois pas du tout en quoi elle constituerait une liste ou un ensemble.

2) Mais même si on considère la notion d'atomicité, on peut au minimum se poser cette question : est-ce que le lien entre cette donnée concaténée et ces composantes sont fixes en un point du temps, ou dynamique ?

En gros, supposons que pour la ligne donnée, le numéro de séquence puisse changer (paraît absurde ici, mais bon), on pourrait cependant vouloir garder la n° de commande (genre parce qu'il a déjà été communiqué au client).

Dans ce cas, il faudrait que la vue ne fasse non pas la concaténation des deux données, mais ailles chercher dans une table d'historique les valeurs au moment de la création de la ligne... (plus chiant déjà)

[EDIT]
Ah tiens juju05, en dehors des débats sur la forme (ou le fond ? ), si tu cherches la solution, tu peux quand même essayer ce que je te proposais plus haut

[Oishiiii]

Nous sommes d'accord.
La définition de la 1NF a évoluée. La notion d' "atomicité" utilisée par Ted Codd à la naissance du Model Relationnel n'est plus à utiliser. Elle est trop ambigüe.

La valeur 'Pierre' pour une colonne prénom est-elle atomique, non-décomposable par le SGBDR ?

Une valeur '2009-1234' pour une colonne numéro de commande, ou une valeur '13;90;78' pour une colonne listeClient ne viole pas la 1NF.

Utilisons la définition suivante : Normalisation 2.7

Tout tuple contient exactement une valeur pour chacun de ses attributs.

La valeur '2009-1234' de la colonne numCommande est légale sur le domaine des chaines de caractères sur lequel est définit la colonne. Aucun soucis.

[skuatamad]

Envoyé par Oishiiii

ou une valeur '13;90;78' pour une colonne listeClient ne viole pas la 1NF.

Je suis surpris, j'aurais pensé que ce type de colonne tombait dans la catégorie (mentionné dans ton 1er lien):

Envoyé par Oishiiii

Pour violer la 1NF, il faudrait qu'un n-uplet possède un groupe, une liste, un ensemble de valeur du type sur lequel l'attribut est définit.

A moins que tu sous-entendes qu'une colonne listeVille contenant 'Paris;Lille' viole la 1NF car on a une liste de VARCHAR stockée dans un VARCHAR à la différence de listeClient où on a une liste d'entier stocké dans un VARCHAR.
Si c'est le cas je trouve cela tiré par les cheveux.

Tout tuple contient exactement une valeur pour chacun de ses attributs.

Pour moi '13;90;78' est bien une liste de valeur, contrairement à '2009-1234' qui se retrouve avoir une signification en lui même, le numéro de la commande.

[Oishiiii]

Envoyé par skuatamad

Je suis surpris, j'aurais pensé que ce type de colonne tombait dans la catégorie (mentionné dans ton 1er lien):

Non, car si on lit bien :

Pour violer la 1NF, il faudrait qu'un n-uplet possède un groupe, une liste, un ensemble de valeur du type sur lequel l'attribut est définit.

Si le type sur lequel est définit cette colonne est par exemple VARCHAR(50).
Cette valeur ('13;90;78') n'est pas une liste de VARCHAR(50).

Toujours en ayant en tête:

Tout tuple contient exactement une valeur pour chacun de ses attributs.

Vous donnez un sens à cette valeur. C'est une liste. D'accord. Problème de modélisation.
Mais du point de vue du domaine, du type qui définit la colonne ce n'est pas une liste. C'est une valeur. Respect de 1NF.

Voir l'exemple de violation dans ce sujet là, où j'ai utilisé une colonne qui prend pour valeur des relations à la place de varchar, mais c'est la même idée.

[Waldar]

Donc en gros, tant qu'on ne fait pas de tables imbriquée on ne viole pas la 1NF ?

[Oishiiii]

Envoyé par Waldar

Donc en gros, tant qu'on ne fait pas de tables imbriquée on ne viole pas la 1NF ?

En gros oui. Même si dans la théorie relationnelle un attribut peut prendre comme valeur des relations

Par définition toute relation est en 1NF.

Une table est en 1NF, qu'elle contiennent des listes dans les colonnes (col1, col2) ou des listes de valeurs pour une colonne 'cli1, cli2'. C'est des considérations qui concernent la modélisation.

Dans la pratique, je ne voit pas comment créer une table qui ne respecte pas la 1NF, mis à part autoriser NULL, qui n'est pas une valeur.

PS: La création d'une table sans contrainte de clé étrangère ni contrainte d'unicité est potentiellement une violation de 1NF puisqu'il y a possibilité d'insérer des doublons.

[Waldar]

C'est faisable avec les SGBD qui gèrent les objets, Oracle par exemple :

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
27
28
29
30
31
32
CREATE type UDT AS TABLE of number;
-- Type created.
 
CREATE TABLE NTT
(
    col1   integer,
    col2   UDT
)
NESTED TABLE col2 STORE AS NT_COL2;
-- Table created.
 
INSERT INTO NTT (col1, col2)
SELECT 1, cast(collect(nb) AS UDT)
  FROM (SELECT 1 AS nb FROM dual union ALL
        SELECT 2       FROM dual union ALL
        SELECT 3       FROM dual);
-- 1 row created.
 
commit;
-- Commit complete.
 
SELECT * FROM NTT;
 
      COL1 COL2(ELEMENT)
---------- -------------
         1 UDT(1,2,3)
 
DROP TABLE NTT;
-- Table dropped.
 
DROP type UDT;
-- Type dropped.

Quand on regarde le contenu de la table avec un éditeur graphique (Toad ici), la donnée COL2 est présentée en tant que (DATASET), et en double cliquant dessus on ouvre l'intérieur sous forme de table.

[skuatamad]

Envoyé par Oishiiii

Dans la pratique, je ne voit pas comment créer une table qui ne respecte pas la 1NF, mis à part autoriser NULL, qui n'est pas une valeur.

Ok j'en étais aussi arrivé à cette conclusion, merci.

Waldar, tel que je le comprends la table NTT est toujours en 1NF.

[pacmann]

Ben ça va un peu plus loin : 1NF ne veut rien dire, et on s'en fout de 1NF. Car :

1) S'il faut que toutes les colonnes soient non nullables pour que la table soit 1NF, on peut juste arrêter de se poser la question "est-ce que cette table est 1NF". Parce qu'à part les moments de débats purement théoriques qu'on peut avoir fsmrel, je pense que le fait que des colonnes soient nullables ne pose de problème à personne. (du moins, je n'ai jamais vu personne en faire la remarque sur un cas concret)

Et sérieusement, vous en avez beaucoup chez vous, des tables sans colonnes nullables ?

Code :

1
2
3
4
5
6
7
8
 
SQL> SELECT count(*)
  2  FROM dba_tab_columns
  3  WHERE owner = 'owner_anonymisé_pour_la_bonne_cause'
  4  GROUP BY TABLE_NAME
  5  HAVING sum(case nullable when 'Y' then 1 end) = 0;
 
aucune ligne sÚlectionnÚe

2) Si on met de côté la nullabilité, toutes les tables "sont 1NF", et il n'y a donc plus lieu d'en discuter

Bref c'est juste un problème de termes, on aurait du se limiter ici à l'expression "modélisation discutable"...

Au passage quand même et toujours dans le même ordre d'idée :

Envoyé par fsmrel

Intérêt des RVA

Une relation comme F_EXT présente des avantages indéniables :

Dans la Figure 2.7, le fait que le fournisseur S5 (Alain) n'a pas livré de pièces est représenté par l'ensemble vide au sein de l'attribut Piece_Qte de la relation F_EXT, alors que — en se plaçant dans un contexte SQL — si l'on utilisait une jointure externe (LEFT OUTER JOIN), le résultat ci-dessous serait pollué par le bonhomme NULL et ne pourrait même pas être doté d'une clé, en l'occurrence la paire {Four_No, Piece_No} : F_EXT ne pourrait donc être une relation et le principe de fermeture ne serait pas respecté (horresco referens !) :

Si on considère le NULL comme non relationnel (ce que je peux comprendre), je ne vois pas pourquoi on considèrerait une "nested" relation vide moins nulle conceptuellement (OK, techniquement, ce n'est pas l'ensemble vide, c'est une entête non-vide dont le contenu est vide... mais ça sent plus la mauvaise fois qu'autre chose).
En gros s'il y a pour moi la même différence entre :
- Etre "en relation avec un sac vide" et "ne pas être en relation"
et
- Etre "en relation avec null" et "ne pas être en relation"

On pourrait pousser le vice jusqu'à dire qu'un attribut est une relation à une colonne avec un entête, et remplacer le null par une relation vide à une colonne...

D'un point de vue SQL, il y a tant de chose squi ne sont pas relationnelles (comme le SELECT par exemple !), mais au final, on a la clôture algébrique de "l'algèbre resultset SQL", et on fait pas mal de chose avec !

[Oishiiii]

Envoyé par pacmann

1NF ne veut rien dire, et on s'en fout de 1NF, car : [etc]

Je suis plus ou moins d'accord oui.
A la base je suis intervenu pour signaler que la table décrite dans le premier message de ce topic respecte la 1NF contrairement à ce qui est écrit dans le second message ou ce qui peut être écrit par SQLPro.

On fait appelle un peu vite au viol de la 1NF alors que c'est rarement le cas.

Envoyé par pacmann

D'un point de vue SQL, il y a tant de chose qui ne sont pas relationnelle (comme le SELECT par exemple !), mais au final, on a la clôture algébrique de l'algèbre "resultset SQL", et on fait pas mal de chose avec !

Yes