Discussion :

[Nerva]

Bonsoir.

Existe-t-il un moyen d'utiliser les aliases (ou autre technique) dans des colonnes calculées, comme dans le SQL d'Access ? Je suis en train de créer une requête de facturation et la longueur des lignes vire au cauchemar.

Merci.

[tatayo]

Bonjour,
Bien sûr, il suffit de mettre l'alias après le "calcul" de la colonne dans la clause SELECT, comme pour n'importe quelle autre colonne.

Tatayo.

[Nerva]

Sans alias, fonctionne :

Code :

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

1
2
3
4
5
6
7
SELECT ACH_ID,
ACH_PUHT,
ACH_QTE,
ACH_RAB_T,
ACH_PUHT * (1 - ACH_RAB_T) AS ACH_PUNHT,
ACH_PUHT * (1 - ACH_RAB_T) * ACH_QTE AS ACH_TNHT
FROM T_ACHATS_SUB;

Avec alias, ne fonctionne pas :

Code :

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

1
2
3
4
5
6
7
SELECT ACH_ID,
ACH_PUHT,
ACH_QTE,
ACH_RAB_T,
ACH_PUHT * (1 - ACH_RAB_T) AS ACH_PUNHT,
ACH_PUNHT * ACH_QTE AS ACH_TNHT
FROM T_ACHATS_SUB;

Je ne sais pas ce qu'il faut modifier.

[tatayo]

Bonjour,
J'avais compris que tu voulais affecter un alias à une colonne calculée, et non utiliser cet alias dans le calcul d'une autre colonne.
A ma connaissance ce n'est pas possible.
Un expert pourra confirmer (ou me corriger), mais l'alias est "affecté" en dernier, et n'est pas connu quand le moteur génère la liste des colonnes.

Il existe peut-être une technique avec le SGBD utilisé, mais comme tu ne l'as pas précisé, impossible de répondre.

Tatayo.

[Nerva]

Ça fait partie des mauvaises habitudes prises avec Access depuis des années.

Il existe peut-être une technique avec le SGBD utilisé, mais comme tu ne l'as pas précisé, impossible de répondre.

Pour le SGBDR, j'utilise HSQL (LO Base) et MySql.

[escartefigue]

Pour LO je n'en sais rien, mais pour MySQL c'est du standard :

select (Col1-Col2) * Col3 as mon_calcul

[tatayo]

Ce n'est pas tout à fait sa demande.
Il veut faire un truc du genre:

Code sql :

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

1
2
3
4
 
select col1 + col2 as tot1,
tot1 + col3+ as tot2
from LaTable

Donc utiliser l'alias d'une colonne calculée dans le calcul d'une autre colonne, dans mon exemple l'alias du premier calcul (tot1) dans le deuxième calcul (tot2).

Tatayo.

[escartefigue]

en ce cas il faut faire comme ceci :

Code :

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

1
2
3
select (Col1-Col2) * Col3 as truc
     , (select truc + Col1) as bidule
from Latable

[Nerva]

Ça fonctionne parfaitement dans MySql :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT
ACH_ID,
ACH_QTE,
ACH_PUHT,
ACH_RAB_T,
ACH_TVA_T,
ACH_PUHT * (1 - ACH_RAB_T) AS ACH_PUNT,
(SELECT ACH_PUNT * ACH_QTE) AS ACH_TNHT,
(SELECT ACH_TNHT * ACH_TVA_T) AS ACH_TVA,
(SELECT ACH_TNHT + ACH_TVA) AS ACH_TTC
FROM T_ACHATS

Mais erreur de syntaxe dans LO Base. Je vais regarder dans la documentation, ça fonctionne peut-être avec une syntaxe différente.

Merci.

[escartefigue]

essayez peut-être sans le mot "AS" des fois ça passe sans (même si je trouve que c'est plus explicite avec)

[Waldar]

Il y a peu de SGBDR qui supportent la réutilisation des alias au sein du même niveau de requête, je ne sais pas pourquoi mais ce n'est pas prévu par la norme SQL il me semble.
C'est vrai que quand on y a goûté c'est toujours un peu pénible de s'en passer, SQL étant déjà assez verbeux comme ça pas la peine d'en rajouter.

En attendant la solution normative serait celle-ci :

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
select ACH_ID
     , ACH_QTE
     , ACH_PUHT
     , ACH_RAB_T
     , ACH_TVA_T
     , ACH_PUNT
     , ACH_TNHT
     , ACH_TVA
     , ACH_TNHT + ACH_TVA as ACH_TTC
  from (select ACH_ID
             , ACH_QTE
             , ACH_PUHT
             , ACH_RAB_T
             , ACH_TVA_T
             , ACH_PUNT
             , ACH_TNHT
             , ACH_TNHT * ACH_TVA_T as ACH_TVA
          from (select ACH_ID
                     , ACH_QTE
                     , ACH_PUHT
                     , ACH_RAB_T
                     , ACH_TVA_T
                     , ACH_PUNT
                     , ACH_PUNT * ACH_QTE AS ACH_TNHT
                  from (select ACH_ID
                             , ACH_QTE
                             , ACH_PUHT
                             , ACH_RAB_T
                             , ACH_TVA_T
                             , ACH_PUHT * (1 - ACH_RAB_T) AS ACH_PUNT
                          from T_ACHATS
                       ) t1
               ) t2
       ) t3;

Ou encore, si votre SGBD supporte les expressions de table communes :

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
42
43
with cte_PUNT as
(
select ACH_ID
     , ACH_QTE
     , ACH_PUHT
     , ACH_RAB_T
     , ACH_TVA_T
     , ACH_PUHT * (1 - ACH_RAB_T) AS ACH_PUNT
  from T_ACHATS
)
  ,  cte_TNHT as
(
select ACH_ID
     , ACH_QTE
     , ACH_PUHT
     , ACH_RAB_T
     , ACH_TVA_T
     , ACH_PUNT
     , ACH_PUNT * ACH_QTE AS ACH_TNHT
  from cte_PUNT
)
  ,  cte_TVA as
(
select ACH_ID
     , ACH_QTE
     , ACH_PUHT
     , ACH_RAB_T
     , ACH_TVA_T
     , ACH_PUNT
     , ACH_TNHT
     , ACH_TNHT * ACH_TVA_T as ACH_TVA
  from cte_TNHT
)
select ACH_ID
     , ACH_QTE
     , ACH_PUHT
     , ACH_RAB_T
     , ACH_TVA_T
     , ACH_PUNT
     , ACH_TNHT
     , ACH_TVA
     , ACH_TNHT + ACH_TVA as ACH_TTC
  from cte_TVA;

La "bonne nouvelle" c'est que l'optimiseur fera toutes les transformations pour vous en une seule fois, mais je reconnais volontiers que c'est pénible à écrire.

[Nerva]

@Waldar, le premier script fonctionne, le second retourne une erreur de syntaxe. Par contre, vu qu'il faut commencer par la fin va falloir prendre le coup pour les écrire. Et j'ai intérêt à garder les requêtes dans un fichier texte bien indenté car le SQL de LO Base place tout sur une seule ligne une fois la requête enregistrée.

Merci.

[escartefigue]

@Nerva : avez vous essayé ma réponse n° 10 (supprimer simplement le "AS") ?

[Waldar]

le second retourne une erreur de syntaxe.

Comme indiqué ça fonctionne uniquement si votre SGBD supporte les expressions de table communes (CTE en anglais).

Par contre, vu qu'il faut commencer par la fin va falloir prendre le coup pour les écrire.

Justement ça se lit de haut en bas sans avoir à détricoter trop d'imbrication, et vous pouvez réutiliser plusieurs fois une CTE déclarée.
Si vous commencez par le bas c'est que vous faites du reverse engeneering, et du coup c'est assez normal comme comportement.

Et j'ai intérêt à garder les requêtes dans un fichier texte bien indenté car le SQL de LO Base place tout sur une seule ligne une fois la requête enregistrée.

[SQLpro]

@Waldar, le premier script fonctionne, le second retourne une erreur de syntaxe. Par contre, vu qu'il faut commencer par la fin va falloir prendre le coup pour les écrire. Et j'ai intérêt à garder les requêtes dans un fichier texte bien indenté car le SQL de LO Base place tout sur une seule ligne une fois la requête enregistrée.

Pas très grave il y a des dizaine d'indenteurs en ligne :
https://sqlformat.org/
https://sql-format.com
http://dpriver.com/pp/sqlformat.htm
https://codebeautify.org/sqlformatter
https://www.freeformatter.com/
...

[Nerva]

@Nerva : avez vous essayé ma réponse n° 10 (supprimer simplement le "AS") ?

Erreur de syntaxe...

[Nerva]

Je corrige partiellement : il accepte sans les "AS" mais pas dans les colonnes calculées. Dommage...

[aieeeuuuuu]

bonjour,

Visiblement, il s'agit de faire le calcul du montant TTC à partir du montant HT et du taux de TVA.

Vous n'en avez pas fait une "vraie" colonne dans votre table, et c'est très bien !
Cependant, vous pouvez faire une colonne calculée, voire même une vue, qui effectue le calcul à partir de la table. ensuite dans vos requetes, vous utilisez directement la vue, ce qui évite de devoir réécrire le calcul à chaque fois, et ce qui laisse en plus un peu de souplesse en cas d'évolution ( du modèle, des règles de calcul...)

[escartefigue]

Il me semble qu'ici au contraire il y a une exigence réglementaire de redondance qui stipule de stocker en base à la fois le montant HT, le montant TTC et montant de TVA à des fins de contrôle par l'administration fiscale.
Il y a très longtemps que je n'ai pas travaillé dans le domaine de la facturation, je peux donc me tromper, mais à vérifier.

[Nerva]

Il s'agit d'une facturation assez complète (j'avais fortement réduit les lignes quand j'ai posté le sujet et là j'ai supprimé tout ce qui est inutile) avec pour la saisie 1 formulaire et 2 sous-formulaires (détail et totalisation).

- R_ACHATS_SUB pour les lignes.
- R_ACHATS_SUB_CUMULS pour la totalisation des lignes (requête intermédiaire car je n'ai pas réussi à sommer les lignes correctement dans R_ACHATS_TOTAL. Malgré une tentative de moyennage, les frais de port et d'expédition étaient cumulés dans le calcul de la TVA. Et la longueur des lignes n'a pas aider à m'y retrouver !).
- R_ACHATS_TOTAL pour la totalisation de la facture.

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
CREATE TABLE T_ACHATS (
	ACH_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT BY 1) NOT NULL,
	ACH_REM_T DECIMAL(12,2),
	ACH_ESC_T DECIMAL(12,2),
	ACH_FRP DECIMAL(12,2),
	ACH_FRE DECIMAL(12,2),
	ACH_TVA_T DECIMAL(12,3) NOT NULL
);
 
CREATE TABLE T_ACHATS_SUB (
	ACH_ID INTEGER NOT NULL,
	ACH_QTE DECIMAL(12,2) NOT NULL,
	ACH_PUHT DECIMAL(12,2) NOT NULL,
	ACH_RAB_T DECIMAL(12,2)
);
 
-- R_ACHATS
SELECT
T_ACHATS.ACH_ID,
T_ACHATS.ACH_REM_T,
T_ACHATS.ACH_ESC_T,
T_ACHATS.ACH_FRP,
T_ACHATS.ACH_FRE,
T_ACHATS.ACH_TVA_T
FROM T_ACHATS;
 
-- R_ACHATS_SUB
SELECT ACH_ID,
PRO_ID,
ACH_QTE,
ACH_PUHT,
ACH_RAB_T,
ACH_PUHT * ACH_RAB_T AS ACH_RAB,
( ACH_QTE * ACH_PUHT * ( 1 - ACH_RAB_T ) ) / ACH_QTE AS ACH_PUNHT,
ACH_QTE * ACH_PUHT * ( 1 - ACH_RAB_T ) AS ACH_TNHT
FROM T_ACHATS_SUB ORDER BY ACH_ID ASC;
 
-- R_ACHATS_SUB_CUMULS
SELECT ACH_ID,
SUM( ACH_QTE * ACH_PUHT * ( 1 - ACH_RAB_T ) ) AS ACH_TNHT
FROM T_ACHATS_SUB GROUP BY ACH_ID;
 
-- R_ACHATS_TOTAL
SELECT T_ACHATS.ACH_ID,
R_ACHATS_SUB_CUMULS.ACH_TNHT AS ACH_TNHT,
T_ACHATS.ACH_REM_T AS ACH_REM_T,
ACH_TNHT * ACH_REM_T AS ACH_REM,
ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) AS ACH_MNC,
T_ACHATS.ACH_ESC_T AS ACH_ESC_T,
( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) * ACH_ESC_T AS ACH_ESC,
( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) - ( ( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) * ACH_ESC_T ) AS ACH_MNHT,
T_ACHATS.ACH_FRP AS ACH_FRP,
T_ACHATS.ACH_FRE AS ACH_FRE,
( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) - ( ( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) * ACH_ESC_T ) + ACH_FRP + ACH_FRE AS ACH_MTHT,
T_ACHATS.ACH_TVA_T AS ACH_TVA_T,
( ( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) - ( ( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) * ACH_ESC_T ) + ACH_FRP + ACH_FRE ) * ACH_TVA_T AS ACH_TVA,
( ( ( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) - ( ( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) * ACH_ESC_T ) + ACH_FRP + ACH_FRE ) ) + ( ( ( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) - ( ( ACH_TNHT - ( ACH_TNHT * ACH_REM_T ) ) * ACH_ESC_T ) + ACH_FRP + ACH_FRE ) * ACH_TVA_T ) AS ACH_MTTTC
FROM T_ACHATS, R_ACHATS_SUB_CUMULS WHERE T_ACHATS.ACH_ID = R_ACHATS_SUB_CUMULS.ACH_ID;