SQL Strings kleine Unterschiede --grosse Wirkung ??


Hello,

Although I tested these strings in ACCESS without using delphi I would like to ask if someone understands why these two apparently similar strings give very different results:

I have three tables:
first table: "Elements" with all chemical elements and the atomic masses (here approximately) eg.

symbol Elementname MolarMass
Na sodium 23 (u)
Cl chlor 35 (u)

second table : "PConComposition" contains the elemental composition of a chemical compound, with three fields :
field 0.name "PCon" (Phase constituent) contains name of compound eg CompNaCl
field 1.name: "Element" contains symbol of Element
field 2.name: "NumberOfElement" contains number of Elementatoms
in this case 2 etries:
"PCon" "Element" "NumberOfElement"
CompNaCl Na 1
CompNaCl Cl 1

third table: "Reactions" contains three fields and contains the formation reaction : Na + Cl --> NaCl

(here name of NaCl is "CompNaCl") as a table :

PConProduct PConReactant Coefficient
CompNaCl NaCl 1 (remark : built up by reaction)
CompNaCl Na -1 (consumed by reaction)
CompNaCl Cl -1 (consumed by reaction)

this means Na + Cl --> NaCl (mathematically: NaCl - Na -Cl = 0)

gives zero as it should :

SELECT sum(C.Coefficient * D.Molmasse) AS Bilanz
FROM public_Reactions AS C INNER JOIN (SELECT PCon, sum(A.MolarMass*B.NumberOfElement) AS Molmasse
FROM public_Element AS A INNER JOIN public_PConComposition AS B ON A.symbol=B.Element
GROUP BY B.Pcon) AS D ON D.PCon = C.PConReactant
GROUP BY PConProduct



but this string gives 116 !

SELECT sum(D.Molmasse*C.coefficient) AS GesamtBilanz
FROM public_Reactions AS C
INNER JOIN [SELECT sum(A.MolarMass*B.NumberOfElement) AS [Molmasse]FROM public_Element AS A INNER JOIN public_PConComposition AS B ON B.Element = A.symbol group by B.PCon]. AS D
ON C.PConReactant = D.PCon
GROUP BY C.PConProduct;


Anyone any idea how this is possible ?

Thomas Willms

.

Relevant Pages

  • sorry once more in english: sql : little differences big effects ??
    ... Although I tested these strings in ACCESS without using delphi I would like to ask if someone understands why these two apparently similar strings give very different results: ... field 0.name "PCon" contains name of compound eg CompNaCl ... PConProduct PConReactant Coefficient ... FROM public_Reactions AS C INNER JOIN ...
    (borland.public.delphi.database.ado)
  • Re: Seperating a Timestamp
    ... Try using DateValue and TimeValue functions. ... Or use Cdate to force conversion of your strings. ... a date fiend and a time field. ... FROM dbo_vwPlant INNER JOIN (dbo_Shipment INNER JOIN dbo_Sleeve ON ...
    (microsoft.public.access.queries)