Discussion :

[danlyme]

Bonjour,
Je rencontre un problème d'arrondi sur cette requette.
il y a une déférence 0.01 entre le FACTURE_TOTAL_TVA et FACTURE_TOTAL_TTC.

J’étulisé FIREBIRD 2.5

merci

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
37
38
39
40
41
 
 
SELECT 
          FC.FAC_NUMERO,             
          FC.FAC_NOM,     
          (               
          SELECT        
 
            CAST( SUM (L.LIGNE_HT_MONTANT *  ((100 - FC.FAC_TX_REMISE) / 100) * ((100 - FC.FAC_TX_ESCOMPTE) / 100) * (1 + (FC.FAC_TX_MAJORATION / 100))) AS DECIMAL(10,2))  
 
          FROM            
          T_LIGNE_FACTURE_C L 
          WHERE           
          L.LIGNE_FAC_ID = FC.FAC_ID 
          ) AS FACTURE_TOTAL_HT 		
         , 
		(
		  SELECT
			CAST( SUM ((L.LIGNE_HT_MONTANT  * ((100 - FC.FAC_TX_REMISE) / 100) * ((100 - FC.FAC_TX_ESCOMPTE) / 100)  * (1 + (FC.FAC_TX_MAJORATION / 100)) *  (T.TVA_TAUX / 100) ) AS DECIMAL(10,2)) 
		  FROM 
            T_LIGNE_FACTURE_C L 
          LEFT JOIN T_TAXE_TVA T ON 
           (L.LIGNE_TVA_ID = T.TVA_ID) 
          WHERE 
            L.LIGNE_FAC_ID = FC.FAC_ID           
		  )  AS FACTURE_TOTAL_TVA	
		  , 	 
          (              
          SELECT         
            CAST( SUM (L.LIGNE_HT_MONTANT  * ((100 - FC.FAC_TX_REMISE) / 100) * ((100 - FC.FAC_TX_ESCOMPTE) / 100)  * (1 + (FC.FAC_TX_MAJORATION / 100)) * (1 + (T.TVA_TAUX / 100)) ) AS DECIMAL(10,2)) 
          FROM 
            T_LIGNE_FACTURE_C L 
          LEFT JOIN T_TAXE_TVA T ON 
           (L.LIGNE_TVA_ID = T.TVA_ID) 
          WHERE 
            L.LIGNE_FAC_ID = FC.FAC_ID 
          ) AS FACTURE_TOTAL_TTC		  
 
 
         FROM
           T_FACTURE_C FC

[al1_24]

Il serait plus correct de faire des SUM(CAST(exp)) que des CAST(SUM(exp))...

[danlyme]

Bonjour al1_24,

merci de ta réponse.

Je viens de tester toujours le même problème une déférence 0.01.

[freud]

Bonjour,

Et avec AS NUMERIC(10,2) ca donne quoi ?

[SergioMaster]

Bonjour,

il s'agit plutôt d'un autre problème.
Déjà je trouve le SQL compliqué trop de (SELECT ) dans le SELECT, une CTE serait certainement plus efficace et plus facile à lire/contrôler
mais surtout le CAST ne fait pas forcément un arrondi correct contrairement au ROUND

[danlyme]

Bonjour,

freud
J'ai remplacer AS DECIMAL par AS NUMERIC(10,2) mais toujours le problème arrondi

SergioMaster
ok je regarde de coté CTE



merci

[danlyme]

Bonjour,

Freud
Quel est la déférence entre DECIMAL ET NUMERIC ?

merci

[freud]

Bonjour danlyme,

Quel est la déférence entre DECIMAL ET NUMERIC

Code :

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

1
2
3
4
5
6
What's the difference between NUMERIC and DECIMAL

In Firebird, there is no difference.

In SQL Standard, NUMERIC is a more strict datatype which should enforce the declared precision, while DECIMAL can accept more numbers than declared. However, in Firebird, both types accept more numbers (i.e. they behave like DECIMAL).

Comme indiquer dans la FAQ il n'y a pas de différence dans Firebird sauf que dans le SQL standard le NUMERIC est un type de données plus stricte.
http://www.firebirdfaq.org/faq340/

C'est quoi le type de données de tous les champs impliqués dans le calcul ?

[danlyme]

ok merci freud

[danlyme]

Bon après plusieurs tests j'ai ajouté un autre CAST et là je trouve le bon résultat.
mais je ne comprends pas pourquoi.

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
37
38
39
40
 
SELECT 
          FC.FAC_NUMERO,             
          FC.FAC_NOM,     
          (               
          SELECT        
 
            CAST( SUM( CAST(L.LIGNE_HT_MONTANT *  ((100 - FC.FAC_TX_REMISE) / 100) AS DECIMAL(10,2)) * ((100 - FC.FAC_TX_ESCOMPTE) / 100) * (1 + (FC.FAC_TX_MAJORATION / 100))) AS DECIMAL(10,2))  
 
          FROM            
          T_LIGNE_FACTURE_C L 
          WHERE           
          L.LIGNE_FAC_ID = FC.FAC_ID 
          ) AS FACTURE_TOTAL_HT 		
         , 
		(
		  SELECT
			CAST( SUM ( CAST (L.LIGNE_HT_MONTANT  * ((100 - FC.FAC_TX_REMISE) / 100) AS DECIMAL(10,2)) * ((100 - FC.FAC_TX_ESCOMPTE) / 100)  * (1 + (FC.FAC_TX_MAJORATION / 100)) *  (T.TVA_TAUX / 100) ) AS DECIMAL(10,2)) 
		  FROM 
            T_LIGNE_FACTURE_C L 
          LEFT JOIN T_TAXE_TVA T ON 
           (L.LIGNE_TVA_ID = T.TVA_ID) 
          WHERE 
            L.LIGNE_FAC_ID = FC.FAC_ID           
		  )  AS FACTURE_TOTAL_TVA	
		  , 	 
          (              
          SELECT         
            CAST( SUM ( CAST(L.LIGNE_HT_MONTANT  * ((100 - FC.FAC_TX_REMISE) / 100)  AS DECIMAL(10,2)) * ((100 - FC.FAC_TX_ESCOMPTE) / 100)  * (1 + (FC.FAC_TX_MAJORATION / 100)) * (1 + (T.TVA_TAUX / 100)) ) AS DECIMAL(10,2)) 
          FROM 
            T_LIGNE_FACTURE_C L 
          LEFT JOIN T_TAXE_TVA T ON 
           (L.LIGNE_TVA_ID = T.TVA_ID) 
          WHERE 
            L.LIGNE_FAC_ID = FC.FAC_ID 
          ) AS FACTURE_TOTAL_TTC		  
 
 
         FROM
           T_FACTURE_C FC

[freud]

Tant mieux !

mais je ne comprends pas pourquoi.

Il n' y a que les chevronnés en Firebird qui peuvent l'expliquer

Curiosité..... les types de données des champs utilisés sont comment ?

[danlyme]

Bonjour Freud,

Je mets à jour une application développée il y a 15 ans sur Delphi 6.
Cette même application fonctionne de depuis 15 ans sans erreur avec Interbase.
Depuis le passage à Firebird 2.5 que nous rencontrons ce problème

merci

Les voici types de données des champs.

Code :

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

1
2
3
4
5
6
 
LIGNE_HT_MONTANT  : DECIMAL(16,3)
FAC_TX_REMISE  : FLOAT
FAC_TX_ESCOMPTE : FLOAT
FAC_TX_MAJORATION  : FLOAT
TVA_TAUX : FLOAT

[freud]

Je pense que les Float doivent-être remplacés par le type DECIMAL
Pouvez-vous poster juste les données de ces champs où l'erreur se produit ?

[escartefigue]

Je pense que les Float doivent-être remplacés par le type DECIMAL
Pouvez-vous poster juste les données de ces champs où l'erreur se produit ?

Tout à fait, ce sujet est archi récurrent : le type float est destiné à des très grandes valeurs sous formes de puissances de 10 dans lesquelles la partie décimale est stockée de façon approximative.
Pour des montants facturés, il faut être précis c'est règlementaire et le type DECIMAL(n,p) ou NUMERIC(n,p) qui sont équivalents sont requis.

[SergioMaster]

Bonjour,

Les champs de type DECIMAL sont quand même stocké de façon "approximative" par exemple 12.45 sera peut être stocké comme 12.4499999
ou 12.4500001, je n'ai jamais vérifié ce qu'il en était pour les NUMERIC

il faut aussi savoir qu'une multiplication de deux type DECIMAL(n,2) fournira un valeur de type DECIMAL(n,4) un CAST en DECIMAL(n,2) de ce résultat ne fournira pas forcément un arrondi financier correct contrairement au ROUND(colonne,2)

Quant à la requête votre règle de gestion est-elle juste ?
vous calculez les montants, taxes et remises par ligne alors qu'il me parait que vos taux de Remise, escompte et majoration sont à la facture

D'expérience, une remise à la ligne, à cause des arrondis, peut être différente d'une remise totale.

Je continu de critiquer votre requête qui, en gros, balaie quand même 4 fois votre table !
même si votre règle de gestion est celle que j'ai déduite, une requête avec jointures et GROUP BY me semble quand même mieux convenir


il me semble que ce SQL ferait la même chose (ici, je ne m'occupe pas des arrondis)

Code :

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

1
2
3
4
5
6
7
8
9
SELECT 
          FC.FAC_NUMERO,             
          FC.FAC_NOM,     
          CAST( SUM( CAST(L.LIGNE_HT_MONTANT *  ((100 - FC.FAC_TX_REMISE) / 100) AS DECIMAL(10,2)) * ((100 - FC.FAC_TX_ESCOMPTE) / 100) * (1 + (FC.FAC_TX_MAJORATION / 100))) AS DECIMAL(10,2))    AS FACTURE_TOTAL_HT , 
         CAST( SUM ( CAST (L.LIGNE_HT_MONTANT  * ((100 - FC.FAC_TX_REMISE) / 100) AS DECIMAL(10,2)) * ((100 - FC.FAC_TX_ESCOMPTE) / 100)  * (1 + (FC.FAC_TX_MAJORATION / 100)) *  (T.TVA_TAUX / 100) ) AS DECIMAL(10,2))  AS FACTURE_TOTAL_TVA, 	 
            CAST( SUM ( CAST(L.LIGNE_HT_MONTANT  * ((100 - FC.FAC_TX_REMISE) / 100)  AS DECIMAL(10,2)) * ((100 - FC.FAC_TX_ESCOMPTE) / 100)  * (1 + (FC.FAC_TX_MAJORATION / 100)) * (1 + (T.TVA_TAUX / 100)) ) AS DECIMAL(10,2))  AS FACTURE_TOTAL_TTC		  
          FROM   T_LIGNE_FACTURE_C L JOIN T_FACTURE_C FC ON   L.LIGNE_FAC_ID = FC.FAC_ID
                                                   LEFT JOIN  T_TAXE_TVA T ON L.LIGNE_TVA_ID = T.TVA_ID -- si LEFT alors un coalesce sur le T.TVA_TAUX me semble judicieux 
         GROUP BY F.FAC_NUMERO,F.FAC_NOM

plutôt que des CAST , j'essaie ici d'utiliser des ROUND (écrit à la volée, je n'ai peut être pas le nombre de parenthèses correct

Code :

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

ROUND(L.LIGNE_HT_MONTANT *  ROUND((100 - FC.FAC_TX_REMISE) / 100),2) * ROUND((100 - FC.FAC_TX_ESCOMPTE) / 100,2) * ROUND( (1 + (FC.FAC_TX_MAJORATION / 100)),2)

il n'empêche que l'utilisation d'une CTE clarifierait les calculs !

Code :

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

1
2
3
4
5
6
7
WITH S AS (SELECT LIGNE_FAC_ID,SUM(L.LIGNE_HT_MONTANT) AS HTLIGNES,ROUND(SUM(L.LIGNE_HT_MONTANT* T.TVA_TAUX / 100),2) AS TVA  GROUP BY LIGNE_FAC_ID L LEFT JOIN T_TAXE_TVA T ON L.LIGNE_TVA_ID = T.TVA_ID )
 
SELECT FC.FAC_NUMERO,             
           FC.FAC_NOM, 
           S.HTLIGNES-ROUND(S.HTLIGNES*((100 - FC.FAC_TX_REMISE) / 100),2)-ROUND(S.HTLIGNES*((100 - FC.FAC_TX_ESCOMPTE) / 100),2) .....  AS FACTURE_TOTAL_HT , 
           ..... 
FROM  T_FACTURE_C FC JOIN S on FC.FAC_ID=S.LIGNE_FAC_ID

vous noterez que, pour ce calcul, j'ajoute les différents montants de remise, escompte. Je n'ai pas utilisé le taux de majoration car je ne comprend pas ce que c'est

P.S. le taux de majoration qu'est-ce ? de toute ma carrière c'est la première fois que je vois ça ! on "majore" une facture pour quelle raison ? à moins que cela corresponde à des frais de port à inclure peut être ?

[SQLpro]

Bonjour,

Les champs de type DECIMAL sont quand même stocké de façon "approximative" par exemple 12.45 sera peut être stocké comme 12.4499999
ou 12.4500001, je n'ai jamais vérifié ce qu'il en était pour les NUMERIC

Et bien non !!!
S'il existe un type NUMERIC dans SQL c'est justement parce que l'encodage des valeurs en binaire dans du flottant laisse des erreurs d'écarts d'arrondi en base 10.

Par exemple 0,2 en base 10 s'écrit

Code :

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

0,001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011001100110011...

C'est à dire une suite infinie de décimales binaires...
Malheureusement les ordinateurs étant des machines finies elles arrêtent les calcul sur la longueur du mot du processeurs (32 bits ou 64 bits. Dans notre cas :

Code :

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

0,0011001100110011001100110011001100110011001100110011001100110011

Ceci provoque une très légère erreur qui peut se répercuter dans les calculs.
En revanche le DECIMAL ou le NUMERIC (types normalisés en SQL) sont encode en DBC (Decimal Codé Binaire) c'est à dire en fait sur deux entiers dont l'un donne la précision (nombre de chiffres significatifs) et l'autre l'échelle (c'est à dire la position de la décimale). De ce fait il n'y a jamais d'erreur de calcul, à la précision soihaitée.
Par exemple pour un DECIMAL(16,2), le binaire correspondant est codé de la manière suivante :

Code :

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

10100(10)

Cet ensemble de deux nombres est d'ailleurs appelé "cohorte" dans la norme IEEE 754.
Le premier chiffre est 20 en décimal et le second 2. À l'affichage on positionne alors la virgule après le 2e chiffre en partant de la droite et en remontant. Le chiffre affiché est donc

Code :

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

.20

ou pour certains afficheur

Code :

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

0.20

Au moins si vous lisiez mes bouquins sur SQL, vous n'écriveriez pas de telles inepties !


A +

[SergioMaster]

Bonjour,

J'utilise mon droit de réponse car, comme d'habitude SQLPro vous êtes abrupt et prompt à critiquer (et fourguer vos livres )

S'il existe un type NUMERIC dans SQL c'est justement parce que l'encodage des valeurs en binaire dans du flottant laisse des erreurs d'écarts d'arrondi en base 10.

je n'ai jamais osé dire le contraire mais j'avoue avoir fait des raccourcis (mes inepties) d'explication dictée par mon gros bon sens pratique (dit de bourrin) et j'aurai du écrire rendra (avec Delphi) plutôt que stockera.

Je parlais du type DECIMAL avec Firebird qui n'est pas tout à fait un synonyme de NUMERIC (contrairement à MSSQL)
avec Firebird il existe une différence subtile, certes, mais non négligeable NUMERIC veut dire précision exacte (sous entendu en nombre de décimales) tandis que pour DECIMAL cela veut dire une précision "d'au moins" le nombre de décimales.

Au besoin j'ai des exemples précis où l'utilisation du type DECIMAL dans une base de données à pu conduire à des erreurs avec des programmes : les types DECIMAL ou NUMERIC étant considéré (ou plutôt reconverti) comme Float (j'ai noté que c'était Delphi qui était utilisé)


De toute façon, cela ne concerne pas la question au niveau d'une facturation
primo - il faut appliquer un arrondi monétaire
secundo - en général,et sauf règle de gestion différente, il faut faire cet arrondi après chaque application de taux (ce que je n'ai pas totalement fait d'ailleurs dans ma proposition de SQL )

[escartefigue]

Je parlais du type DECIMAL avec Firebird qui n'est pas tout à fait un synonyme de NUMERIC (contrairement à MSSQL)
avec Firebird il existe une différence subtile, certes, mais non négligeable NUMERIC veut dire précision exacte (sous entendu en nombre de décimales) tandis que pour DECIMAL cela veut dire une précision "d'au moins" le nombre de décimales.

Je pensais que quelque soit le SGBD DECIMAL et NUMERIC étaient synonymes(pour ceux qui acceptent les deux types)
Cet article semble d'ailleurs le confirmer pour ce qui concerne Firebird

Peut être en fonction des versions

[SergioMaster]

Bonjour,

pour répondre à ce point, j'ai tiré cette phrase du livre d'Helen Borrie : The Firebird Book 2nde Edition 2004 (page 132 et +, page 167 et + du pdf), mais également retrouvé dans différentes notes de version par exemple https://www.firebirdsql.org/pdfmanua...ata-types.html

j'y lis aussi que NUMERIC et DECIMAL sont identique sauf si la précision est inférieure à 5. Et que le mode de stockage dépend beaucoup de la déclaration ainsi que du DIALECT.

Il m'est impossible de reproduire le texte et c'est dommage car je fini par m'y perdre entre les deux types !
selon le livre, à moins que cela ait changé depuis 2004
- NUMERIC n'est pas conforme à la norme
- dans une colonne NUMERIC(4,2) il est possible de mettre une valeur jusqu'à 327,67, dans une colonne DECIMAL(4,1) il serait possible de mettre une valeur jusqu'à 214748364,7 et ce sans qu'il y ait d'erreur de levé

[alanglet]

Bonjour,

La raison de ces limites est expliquée dans la documentation https://www.firebirdsql.org/file/doc...ixedtypes.html
Elle vient du type du champ utilisé pour stocker la valeur, SMALLINT, INTEGER ou BIGINT selon les cas.

André