 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
srdaniel
Guest
|
 Posted: Tue Aug 15, 2006 3:10 am Post subject: QBE Calculation |
 |
|
Does anyone remember how to use QBE's?
I can create a simple one like so
QUERY
CUSTOMER | CustNo | Company | Addr1 |
| Check | NOT BLANK | Check |
ENDQUERY
But what I was wondering was, is it possible to limit the records based
on a calculation between two fields? I have two currency fields and I
only want the records where the difference between the two currency
fields is greater than a specified percentage.
If not QBE, is it possible with SQL?
srdaniel |
|
| Back to top |
|
 |
Bill Todd
Guest
|
| Posted: Tue Aug 15, 2006 6:31 am Post subject: Re: QBE Calculation |
|
|
srdaniel wrote:
| Quote: | Does anyone remember how to use QBE's?
I can create a simple one like so
QUERY
CUSTOMER | CustNo | Company | Addr1 |
| Check | NOT BLANK | Check |
ENDQUERY
But what I was wondering was, is it possible to limit the records
based on a calculation between two fields? I have two currency fields
and I only want the records where the difference between the two
currency fields is greater than a specified percentage.
If not QBE, is it possible with SQL?
srdaniel
|
It is possible with both QBE and SQL, however, I am on the road and do
not remember the exact syntax for QBE. I have no way to look it up
until I get home. With SQL:
SELECT * FROM ATABLE
WHERE FIELD1 - FIELD2 > 23
--
Bill Todd (TeamB) |
|
| Back to top |
|
|
srdaniel
Guest
|
| Posted: Tue Aug 15, 2006 7:33 pm Post subject: Re: QBE Calculation |
|
|
Bill Todd wrote:
| Quote: |
It is possible with both QBE and SQL, however, I am on the road and do
not remember the exact syntax for QBE. I have no way to look it up
until I get home. With SQL:
SELECT * FROM ATABLE
WHERE FIELD1 - FIELD2 > 23
|
Looks like it has something to do with the CALC operator. My old paradox
book mentions hitting F5 and using CALC, but I don't have the full blown
Paradox editor, just Database Desktop and I'm not sure how to get a CALC
working. Here is the delphi function I'm trying to duplicate:
function PremiumChangedEnough( aPremium, aOldPremium: Double;
aChangePercent: Integer ): Boolean;
var
ActualDifference, CheckDifferenceAmount: Double;
begin
ActualDifference := Abs( aPremium - aOldPremium );
CheckDifferenceAmount := Abs((aChangePercent / 100) * aPremium);
Result := (ActualDifference >= CheckDifferenceAmount);
end; |
|
| Back to top |
|
|
srdaniel
Guest
|
| Posted: Tue Aug 15, 2006 11:12 pm Post subject: Re: QBE Calculation |
|
|
Bill Todd wrote:
| Quote: | It is possible with both QBE and SQL, however, I am on the road and do
not remember the exact syntax for QBE. I have no way to look it up
until I get home. With SQL:
SELECT * FROM ATABLE
WHERE FIELD1 - FIELD2 > 23
|
I got the percentage difference to work with SQL, but I'd still like to
know how to do it with a QBE.
select OldPrem, NewPrem, (((NewPrem-OldPrem)/OldPrem)*100) as
FieldPercentage
from "%s"
where ((((NewPrem-OldPrem)/OldPrem)*100) >= 15) or
((((NewPrem-OldPrem)/OldPrem)*100) <= -15) |
|
| Back to top |
|
|
Bill Todd
Guest
|
| Posted: Wed Aug 16, 2006 7:25 am Post subject: Re: QBE Calculation |
|
|
Using QBE you need an example element in each of the columns to be used
in the calculation then use the calc operator for the calculation.
QUERY
ORDERITEM | Quantity | UnitPrice | OrderNumber |
| _Qty | _UP | calc _Qty * _UP as ExtendedPrice |
ENDQUERY
If you are going to do much of this try to find a used copy of Paradox
Queries - A Complete Reference by Dan Ehrmann published by M&T. It
truly is a complete reference.
--
Bill Todd (TeamB) |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
