Problèmes de champs dans SQL

Bonjour,
Encore un problème récemment apparu. Certains connaissent cette partie de code :

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

if Datas.QNomenclature.FieldByName('Imputation_compte').Value = 0 then begin Datas.QJournal.fieldbyname('Montant_credit').value := edtMontant.text; Datas.QJournal.fieldbyname('Imputation').Value := 0; With Datas.QNomenclature do begin SQL.Clear; SQL.Text := 'UPDATE tblNomenclature SET TotalActif = TotalActif + :aMontant WHERE Num_Compte = :aNumero'; ParamByName('aMontant').AsFloat := edtMontant.Value; ParamByName('aNumero').AsString := edtNumero.Text; ExecSql; end; end else begin Datas.QJournal.fieldbyname('Montant_debit').value := edtMontant.text; Datas.QJournal.fieldbyname('Imputation').Value := 1; With Datas.QNomenclature do begin SQL.Clear; SQL.Text := 'UPDATE tblNomenclature SET TotalPassif = TotalPassif + :pMontant WHERE Num_Compte = :pNumero'; ParamByName('pMontant').AsFloat := edtMontant.Value; ParamByName('pNumero').AsString := edtNumero.Text; ExecSql; end; end;

---

Il fait la somme des actifs et des passifs des comptes identiques de la table tblJournal et les stocke dans la table tblNomenclature par grands comptes. Cela a fonctionné longtemps parfaitement mais depuis quelques jours UPDATE n'UPDATE plus rien et les sommes ne sont plus affichées nulle part. J'ai un enregistrement vide. Et ceci sans avoir changé quoi que ce soit dans le code qui est le même depuis plusieurs semaines. Sqlite Studio n'affiche rien non plus.

Condition : Delphi 10.2, Zeos 8, sqlite3 v.3.29 Win32 à l'origine, puis 3.49, puis retour à la 3.29 pensant que la cause venait de là. Mais que nenni apparemment.

Merci pour votre aide.
Cordialement

Je tique sur

Code:

---

ParamByName('aMontant').AsFloat := edtMontant.Value;

---

SI EdtMontant est un TEdit il y a un loup
je verrai plus

Code:

---

ParamByName('aMontant').AsFloat := StrToFloatDef(edtMontant.text,0.00);

---

Après changement rien n'a changé si j'ose dire ! Toujours une ligne avec le n° de compte et le libellé mais le cumul ne s'affiche toujours pas. Je suis même allé jusqu'à reconstruire la BDD. Rien n'y fait

OK, mais, si tu as appliqué ce que j'ai écrit et comme j'ai mis un

Code:

---

StrToFloatDef(edtMontant.text,0.00);

---

Il y a peut-être une erreur de conversion (genre point décimal au lieu de virgule). Il existe une version de StrToFloatDef qui indique en troisième argument un TFormatSettings (si tu veux garder le point décimal)

exemple

Code:

---

1 2 3 4 5 6 7 8

var fmtpointdec : TFormatSettings; // peut très bien être créé en création de la forme pour ne plus avoir à l'initialiser r : Double; // j'aurai plutôt mis currency dans ton cas begin fmtpointdec.DecimalSeparator:='.'; r:=StrToFloatDef('450.12',0.00,fmtpointdec); showmessage(r.ToString); // juste pour confirmer cet essai end;

---

Appliqué à la lettre ta suggestion. Aucun résultat, toujours pas de cumul des champs. Prise de tête parce que ça eut fonctionné !

Je n'ai pas assez de billes pour avancer d'avantage, déjà que je trouve cette table tblnomenclature un peu "too much"

En debug, tu passes bien dans ces instructions ?

Oui en debug ces instructions ne génèrent rien de spécial, pas de messages d'aucune sorte. Mais tu as raison je voudrais laisser à la table tblNomenclature sont rôle premier c'est-à dire une simple liste des comptes utilisables sans calculs. J'ai voulu trop lui en demander.
Voici une image de la table principale tblJournal qui est peuplée par les opérations au jour le jour. Les champs "Label" et "Nom" sont fantaisistes et n'ont aucune importance en l'occurence.
Pièce jointe 665506
Comme on peut le voir, certains comptes sont utilisés plusieurs fois.
Je recherche une chose :
- faire la somme des champs <Montant_credit> et <Montant_debit> par numéro de compte et faire la liste des comptes différents (ça fonctionne) avec les sommes (ça ne fonctionne pas).
Quelle pourrait être la requête efficace ? Je l'adapterai ensuite si besoin.

Merci. Cordialement.

Rappel

Null + Int => Null à remplacer par COALESCE

Donc ne pas oublier de mettre 0 comme valeur par défaut de TotalActif et TotalPassif


Ensuite pourquoi QJournal reçoit des valeurs, il y a un Append\Edit + Post quelque part, c'est une query en mémoire, pas de UpdateSQL qui traine

SQLite, si cela supporte les Triggers, je n'ai pas vérifié, si oui, cela rend tout ce code inutile en gérant directement cela dans le Trigger en insert update de tblJournal


En plus, le IF est inutile,

Tout ce code résume après le Post ou UPDATE de tblJournal

Code:

---

1 2 3 4 5 6 7

SQL.Text := ' UPDATE tblNomenclature ' + ' SET TotalActif = ( SELECT SUM(COALESCE(Montant_credit, 0)) ' + ' FROM tblJournal WHERE Num_Compte = :aNumero ) ' + ' , TotalPassif = ( SELECT SUM(COALESCE(Montant_debit, 0)) ' + ' FROM tblJournal WHERE Num_Compte = :aNumero ) ' + ' WHERE Num_Compte = :aNumero';

---

Il n'y aura jamais aucun doute d'une écriture incomplète

D'ailleurs je recommande que les UPDATE de tblJournal et tblNomenclature soient faits dans la même Transaction

Ce même SQL à peu de chose près pourrait être celui du Trigger en insert update de tblJournal

Bonjour,

Citation:

---

Envoyé par ShaiLeTroll

SQLite, si cela supporte les Triggers, je n'ai pas vérifié, si oui, cela rend tout ce code inutile en gérant directement cela dans le Trigger en insert update de tblJournal

---

:plusser: Oui, cela existe SQLite triggers, bien que je n'ai jamais essayé

:plusser: aussi pour une seule transaction dans le cas de non utilisation de trigger et "au cas où plantage edit sans post sur tbljournal"

si je devais écrire un trigger, je ferai quelque chose comme ça (non vérifié car cela demande d'avoir la structure précise des deux tables)

Code:

---

1 2 3 4 5 6

CREATE Cumulparnomen AFTER UPDATE OR INSERT OF Num_Compte ,montant_debit,montant_credit ON tblJournal BEGIN INSERT OR REPLACE INTO tblnomenclature(num_compte,TotalActif,TotalPassif) VALUES(old.Num_compte,Coalesce(totalActif,0)+old.montant_credit,Coalesce(totalpassif,0)+old.montant_debit); END;

---

APRES TESTS : La syntaxe AFTER UPDATE OR INSERT n'est pas acceptée, le OF c'est juste pour l'UPDATE, INSERT OR REPLACE n'est pas acceptée non plus :roll:


De plus, il y a un cas non prévu dans ton code, l'update ne fonctionnera pas si le compte n'est pas dans la table tblnomenclature
soit ton SQL doit être, non pas un UPDATE mais, un INSERT OR REPLACE
soit, il faut faire un test d'existence de num_compte avant SELECT num_compte from tblNomenclature WHERE Num_Compte = :aNumero

J'ai eu un peu de mal avec SQLITE mais voici une proposition avec TRIGGERS
Cela étant, il n'y a pas tous les triggers : que faire en cas de suppression d'un compte, d'une opération ???, ni non plus un traitement des clés étrangères (ON UPDATE CASCADE ?, ON DELETE CASCADE ?)

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 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

-- Tableau : Comptes CREATE TABLE IF NOT EXISTS Comptes ( id_compte INTEGER PRIMARY KEY AUTOINCREMENT, Libelle VARCHAR (60) NOT NULL );     -- Tableau : operations CREATE TABLE IF NOT EXISTS operations ( ID_OPE INTEGER PRIMARY KEY AUTOINCREMENT, NUM_COMPTE INTEGER NOT NULL REFERENCES Comptes (id_compte), MONTANT CURRENCY DEFAULT (0.0), CREDIT_DEBIT INTEGER DEFAULT (0) CONSTRAINT CREDIT1_DEBIT0 CHECK (CREDIT_DEBIT = 0 OR CREDIT_DEBIT = 1) );     -- Tableau : Recap CREATE TABLE IF NOT EXISTS Recap ( compte INTEGER REFERENCES Comptes (id_compte) PRIMARY KEY, Actif CURRENCY DEFAULT (0), Passif CURRENCY DEFAULT (0) );     -- Déclencheur : AJOUTCOMPTE CREATE TRIGGER IF NOT EXISTS AJOUTCOMPTE AFTER INSERT ON COMPTES BEGIN INSERT INTO RECAP ( Compte, actif, passif ) VALUES ( new.id_compte, 0, 0 ); END;     -- Déclencheur : NOUVELLE_OP CREATE TRIGGER IF NOT EXISTS NOUVELLE_OP AFTER INSERT ON OPERATIONS BEGIN UPDATE RECAP SET ACTIF = ACTIF + IIF(new.CREDIT_DEBIT = 1, new.MONTANT, 0), PASSIF = PASSIF + IIF(new.CREDIT_DEBIT = 0, new.MONTANT, 0) WHERE COMPTE = new.NUM_COMPTE; END;

---

Avantage des triggers présents ? plus besoin de code dans le programme, du moins pour la table RECAP ;)
Même si, de mon point de vue cette table n'est pas très utile car remplaçable par un SELECT (qui d'ailleurs pourrait être dans une vue pour plus de facilités niveau programme)

Pour en finir avec mon test, j'ai voulu aussi "remplacer/tester" la table recap par une vue

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

CREATE VIEW vue_recap AS WITH O AS ( SELECT NUM_COMPTE, SUM(IIF(CREDIT_DEBIT = 1, MONTANT, -MONTANT) ) TOTAL, SUM(IIF(CREDIT_DEBIT = 1, MONTANT, 0) ) ACTIF, SUM(IIF(CREDIT_DEBIT = 0, MONTANT, 0) ) PASSIF FROM OPERATIONS GROUP BY NUM_COMPTE ) SELECT c.id_compte, c.libelle, COALESCE(o.actif, 0) Actif, Coalesce(o.passif, 0) passif, coalesce(o.total, 0) total FROM Comptes c LEFT JOIN O ON o.num_compte = c.id_compte;

---

REMARQUE l'utilisation d'une vue permettrait de ne pas utiliser les triggers et du coup rend la table recap caduque

Bien sûr, comme tu le remarqueras, j'utilise toutes les techniques "modernes" du SQL :

• les Common Table Expressions (CTE), déclarées par WITH .....
• Les jointures LEFT JOIN
• etc...

Mais, à ma souvenance, j'avais déjà proposé une syntaxe similaire dans une autre discussion.

Pourquoi il me manquait des billes ?
J'ai l'impression qu'il s'agit d'un pseudo programme de comptabilité (je déteste cette matière) d'association ?. Dans les dernières propositions que j'ai faites j'ai donc considéré que les "écritures" (opérations) n'était pas modifiables une fois créées.
On n'a pas non plus évoqué le nombre de comptes ni d'opérations (question performances ça joue).
Je n'ai pas pris en compte la colonne 'Imputation_compte' facile à intégrée dans une vue mais pas dans les triggers. À expliquer en cas de trigger, que faire dans la table recap si cette colonne est modifiée !?)

Veuillez m'excuser mais je ne suis pas parvenu à insérer les pièces jointes sans les afficher
Pour répondre à ton intuition Serge, tu as deviné que cette appli est un ersatz de comptabilité d'une petite association dont je suis membre mais pas trésorier. Si tu n'aimes pas la comptabilité, rassure-toi moi non plus mais c'est un exercice pour moi. Servira-t-elle ou pas, en fait peu importe, notre trésorière utilise le bon vieux cahier de comptes. C'est son développement qui m'intéresse.
Les 2 tables principales sont (voir images) :
- tblJournal qui stocke les opérations au jour le jour. Rien n'est calculé, juste la somme des champs <Montant_credit> et <Montant_debit> affichées sur l'interface. Elle était modifiable à l'origine mais comme c'est de la compta j'ai fait le choix de la rendre non modifiable. Je précise qu'elle fonctionne parfaitement. Le champ <Print> ne sert plus (je vais le supprimer) et le champ <Nomen> stocke le nom du compte au sens de la nomenclature. Le champ <Imputation> porte 0 ou 1 selon que l'opération concerne de l'actif ou du passif. Ceci aussi fonctionne.
- tblNomenclature qui stocke la liste des comptes utilisables par notre association avec leur numéro et le libellé du compte. J'ai voulu y ajouter une fonction de cumul des montants par compte dans les 2 champs :
- TotalActif
- TotalPassif
Et c'est là que ça foire parce que le cumul des montants passif ou actif ne s'affiche pas quelque soient les solutions que vous y apportez les uns et les autres, ce dont je vous remercie encore. Comme si la table tblNomenclature était en ReadOnly (je plaisante). Comme je l'ai déjà dit, ça a fonctionné puisque la table a été peuplée une 1ère fois, mais je ne sais pas à la suite de quel évènement ça ne fonctionne plus. Je ne peux plus continuer le "peuplement".
Voilà vous en savez un peu plus. Si vous voulez d'autres précisions, même adresse !
Pièce jointe 665563Pièce jointe 665564

ARRG !! pas de PK, pas de FK c'est très, très gênant et contrariant ça ! (et pas bon pour la fiabilité de la BDD)

En général, une comptabilité arrête un exercice, tu as prévu la fonctionnalité ou pas encore ?
TotalActif et TotalPassif dans TblNomenclature, je n'en vois pas l'intérêt de même que imputation_compte (sauf si, par exemple, c'est un plan comptable complet et que cela indique quels sont les comptes qui seront uniquement ceux utilisés)

Citation:

---

<Montant_credit> et <Montant_debit> affichées sur l'interface. et le champ <Nomen> stocke le nom du compte au sens de la nomenclature. Le champ <Imputation> porte 0 ou 1 selon que l'opération concerne de l'actif ou du passif.

---

une écriture ne peut "normalement" n'être qu'en Credit ou en Débit, donc un seul Montant et ce que tu appelles Imputation fera la différence
le champ Nomen (le numéro de compte du plan comptable), sera une source de tracas (redondance)

Si tu n'aimes pas la comptabilité Serge, rassure-toi moi non plus. Je n'ai jamais voulu être trésorier mais cette appli est avant tout un exercice. Sera-t-elle utilisé ou pas ? Cela m'importe peu car notre trésorière en est encore au bon vieux cahier-Journal. A côté, mon appli en fait beaucoup plus.

Citation:

---

ARRG !! pas de PK, pas de FK c'est très, très gênant et contrariant ça ! (et pas bon pour la fiabilité de la BDD)

---

Oui c'est vrai ça manque cruellement de clés mais je n'en suis pas encore là dans mon apprentissage des BDD. Et puis je me pose une question : la table des opérations journalières (tblJournal) comportent des champs qui auraient nécessairement le même numéro de compte mais une clé forcément différente. Comment alors sélectionner ces enregistrements ? Mais je vais me rancarder sur les clés, ce sera intéressant.

Citation:

---

En général, une comptabilité arrête un exercice, tu as prévu la fonctionnalité ou pas encore ?

---

Pour ce qui est de la clôture d'un exercice, c'est tout simple : en fin d'année je sauvegarde toute la BDD en la datant automatiquement et je repars sur un exercice vide qui réinitialise tout. Je peux aussi rappeler un exercice antérieur et revenir à l'exercice courant.

Citation:

---

le champ Nomen (le numéro de compte du plan comptable), sera une source de tracas (redondance)

---

Tu as raison, le champ <Nomen> de tblJournal ne sert en fait à rien et je l'ai supprimé depuis sans conséquences. Comme disait Saint-Exupéry : La perfection n'est pas atteinte quand il n'y a plus rien à ajouter, mais quand il n'y a plus rien à enlever.
Je cherche maintenant à tracer la valeur qui s'affiche dans tblJournal mais pas dans tblNomenclature. Pourquoi et que devient-elle entre les 2 ?
Voilà où j'en suis, c'est-à-dire au même point !!!
Cordialement

Ton problème au niveau de la BD c'est que tu vois ça comme des tables "indépendantes" et que tu n'utilises pas la puissance d'un SGBD

Citation:

---

la table des opérations journalières (tblJournal) comportent des champs qui auraient nécessairement le même numéro de compte mais une clé forcément différente.

---

c'est là où, un identifiant, auto-incrémenté et clé primaire est utile

Code:

---

1 2	ID_OPE INTEGER PRIMARY KEY AUTOINCREMENT,

---

Ce que tu nommes Nomen (ou numéro de compte) doit être lié à la table plan comptable.
Dans la table compte que je te proposais, faite rapidement, je considérais la colonne auto incrémentée comme le numéro de compte (pour les tests c'était ok)

Code:

---

id_compte INTEGER PRIMARY KEY AUTOINCREMENT,

---

Mais en fait je créerai la table ainsi

Code:

---

1 2 3 4 5 6 7 8 9 10

CREATE TABLE IF NOT EXISTS Comptes ( id_compte INTEGER PRIMARY KEY AUTOINCREMENT, num_compte INTEGER UNIQUE NOT NULL, Libelle VARCHAR (60) NOT NULL );   -- Index : IDX_Compte, ne pas hésiter à créer des indexs supplémentaires CREATE UNIQUE INDEX IF NOT EXISTS IDX_Compte ON Comptes ( num_compte );

---

et du coup changer la contrainte de clé étangère en

Code:

---

NUM_COMPTE INTEGER NOT NULL REFERENCES Comptes (num_compte),

---

Pour ce qui est de la cloture d'un exercice, j'utiliserai les possibilités de la SGBD pour créer une nouvelle table (par exemple OperationsAA) , pour la remplir avec les écritures puis supprimer les écritures cloturées, ajouter les écritures de récap de l'exercice en ayant fait un reset de l'incrément. (cela étant, je viens de penser que d'autres écritures sont peut-être non cloturées, il y là une manip à voir)

Mais, ce que je recommande surtout c'est d'utiliser à fond les fonctionnalités d'un SGBD (ne pas l'utiliser comme des tables comme paradox ou excel ;))

Une bonne, une excellente nouvelle : ça fonctionne. J'ai été cherché bien loin ce qui crevait les yeux --> une différence de typage entre la zone de saisie du montant d'une opération et les paramètres <aNumero> et <pNumero>. La requête SQL n'inscrivait rien dans le champ <Montant> de la BDD, défini à Integer, sans pour autant qu'il y ait de message d'erreur.
Avant correction :

Code:

---

1 2 3

ParamByName('pMontant').asFloat := StrToFloatDef(edtMontant.text,0.00); ParamByName('pNumero').asString := edtNumero.text; //TEdit

---

Après correction :

Code:

---

1 2 3

ParamByName('pMontant').asFloat := StrToFloatDef(edtMontant.text,0.00); ParamByName('pNumero').asDouble := StrToIntDef(edtNumero.text,0);

---

Et ce simple changement a rétabli la situation. Désormais les valeurs s'affichent enfin et correctement. Il n'empêche que même si tout fonctionne la BDD est un peu exotique. Cela va être la prochaine étape : rendre tout cela un peu plus conforme à la philosophie d'une BDD qui se respecte (clés).
Merci à tous pour votre participation.
Cordialement