What could this obscure result from a Query be? 1

[terrytype]

I use Delphi6 Professional.

I have a [Paradox] table in which plus and minus values are put into a particular field 'Balance' in each record of the table. At all times the plusses and minusses should equal one another - leaving the SUM of 'Balance' to be "0". If it does not there is an error which the code below detects and triggers an alert with a Click Procedure.

This works perfectly in one table - but in ANOTHER identical unrelated table [USED ELSEWHERE] where in fact Balance correctly reflects as being "0" [in Delphi DataBaseDesktop] - a break-point at LINE A in the code below reflects Balance to be[obscure] "=1.182234e-17"; and the alert is falsely triggered.

Any ideas as to why?

 with dmStdAcc.qryDLTBError do
    begin
      SQL.Clear;
      SQL.Add('SELECT SUM (Balance)TOTAL_ERROR');
      s:=format('FROM %s',[qryname1]);
      SQL.Add (s);
      s:=format('WHERE %s',[qryname2]);
      SQL.Add (s);
      Active := True;
    end;
    if dmStdAcc.qryDLTBError.FieldByName('TOTAL_ERROR').Value <> 0 then  // LINE A
      Click;

Thanks in advance!

Old Man Delphi

 

[towerbase]

How is the column Balance defined?

I suspect that it is a floating point number.

By their nature floating point numbers are often not precise and there is a risk that you will get this kind of error. Money values are almost always best defined as an integer. For example as cents or pennies.

Can you try running this query to identify any rogue values:

SELECT *
FROM table
WHERE FLOOR(balance*100) <> balance*100

This should identify any balances are have fractional cents (or pennies) and may be causing the problem.



Andrew
Hampshire, UK

 

[terrytype]

Thanks Andrew. In fact BALANCE is defined in the Paradox table as a "currency" field.

Nevertheless I suspect you are right. That TOTAL_ERROR.value is not a proper value.
The mystery being why it is acceptable when the code is applied to another [totaly different - but identical] table.

Unfortunately I cannot apply your suggestion which probably flows from my not having
reflected the preceding code - which, as you can see, doesn't leave me free
to alter 'WHERE'. The code [more explicitly] looks like this ..

    qryname1 := '''C:\h\StdAcc\DL'' t1, ''C:\h\StdAcc\PivotTbl1'' t2';
    qryname2 := ('(t1.PNo = t2.PNo) AND (t1.PDate >= t2.AltStartDate) AND (t1.PDate <= t2.AsAtDate)');
    with dmStdAcc.qryDLTBError do
    begin
      SQL.Clear;
      SQL.Add('SELECT SUM (Balance)TOTAL_ERROR');
      s:=format('FROM %s',[qryname1]);
      SQL.Add (s);
      s:=format('WHERE %s',[qryname2]);
      SQL.Add (s);
      Active := True;
    end;
    if dmStdAcc.qryDLTBError.FieldByName('TOTAL_ERROR').Value <> 0 then
      Click;

Perhaps you can expand on this?

Thanks in advance.

Old Man Delphi

 

[towerbase]

In your initial posting you mentioned a table but in fact your query involves two tables (DL and PivotTbll). How do you know that the two versions of the the two tables contain identical data and have identical structure?

I was hoping that you would execute the SQL I suggested in Database Desktop in order to identify rogue values.


Andrew
Hampshire, UK

 

[terrytype]

My apologies Andrew. I misunderstood. I have now run a query in Delphi Desktop using your code exactly but get exception "Capability not supported."

>How do you know that the two versions of the the two tables >contain identical data and have identical structure?

First [working] DL table is "C:\h\50\DL"
Second [suspect] table is is "C:\h\8\DL".
identical in structure to the first.

I'll check to make abolutely sure but there shouldn't BE a difference - other than the content.

PivotTbl1 merely [as the name suggests] is merely a "pivot" whereby DL is indentified as "C:\h\8\DL"; or "C:\h\50\DL"; or "C:\h\14\DL". Etc. etc.




Old Man Delphi

 

[towerbase]

Sorry, apparently Paradox SQL does not support the FLOOR function. I am not sure what the equivalent name for this function is in Paradox (or even if it is supported).

Whilst Paradox was cutting edge when it was introduced to a PC world hooked on dBASE back in the late 1980s, it is really out of date now and I strongly recommend that you replace it with a modern, powerful and robust alternative.

Personally, I find that MySQL is pretty good but I am also looking at SQLite to see if it is suitable for certain applications. I am sure you will find other folk recommending PostgreSQL, Interbase, Firebird, MS SQL Server and so on. But do yourself a favour and migrate from Paradox as soon as possible if you can.

Andrew
Hampshire, UK

 

[terrytype]

Thanks for your patience and interest Andrew. I have indeed been considering migrating from Paradpx for some time but have been put off by the sheer scale of the cnanges to extensive software not to mention the migrating of the content of the Paradox Tables. Guess I'll have to confront that now.

A closing note.

After my girations in duplicating the two tables to have identical structure having identical content [except for the value in destinguishing field 'PNo' being '50' in one table and '8' in the other - the sole difference between the two tables] the one having PNo = 8 now produces value 'NUL' [causing my problem]whilst the one having PNo=50 correctly produces '0' [as required].

Wierd stuff!



Old Man Delphi

 

[sggaunt]

I did some work with Paradox whilst on placment in 1992!

Steve: N.M.N.F.
If something is popular, it must be wrong: Mark Twain