Solved

Perform Queries on Calculated fields within OnCalcFields Event

Posted on 2004-04-26
27
711 Views
Last Modified: 2010-04-05
Is it possible to Perform Queries on Calculated fields within OnCalcFields Event? If so, how would I go about doing it?


Example:

Consider the following:


Teams
- id
- Team
- Points - Calculated field (calculated from sql on Players Table)

Players
- id
- TeamId
- Player
- Points - Calculated field (calculated from sql on Matches Table)

Matches
- id
- Date
- Team1 - Lookup from Teams
- T1Points - Calculated field (calculated from sql on Games Table)
- Team2 - Lookup from Teams
- T2Points - Calculated field (calculated from sql on Games Table)

Games
 - Id
 - MatchId
 - MatchType - {Singles, Doubles}
 - T1Player1 - Team 1 Player 1 - A Lookup from Players
 - T1Points1
 - T1Player2 - Team 1 Player 2 - A Lookup from Players
 - T1Points2
 - T2Player1  - Team 2 Player 1 - A Lookup from Players
 - T2Points1
 - T2Player2  - Team 2 Player 2 - A Lookup from Players
 - T2Points2


The Games table will be the only point aby which points will be entered.

The Matche's table calculated field (T1Points * T2Points) should be calculated from the total points of T1Points1 & T1Points2 from the Games table.
(e.g. for all those records with matchId)

and

The Player's Table calculated field (points) should be calculated from the Games table all matching records of Player.

and

The Team's Table calculated field (points) should be calculated from the Matches table all matching records of Teams
.

What would the queries look like? How would I write the OnCalcField Events?


Shane
0
Comment
Question by:shaneholmes
  • 14
  • 12
27 Comments
 
LVL 17

Expert Comment

by:mokule
ID: 10923231
Now You believe that my solution was not so bad.

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20968246.html

You would have no such a problems :)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10925231
>Is it possible to Perform Queries on Calculated fields
>within OnCalcFields Event? If so, how would I go about doing it?

yes, is possible, coming back later


0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10925866
hmm,

its very ugly with this kind of db-design,
guess you can't do it with one sql-query,
you need atleast two queries to sum
games.T1Points1 + games.T1Points2
and
games.T2Points1 + games.T2Points2

because one team could be in one game team1
and in another game team2

meikl ;-)

 

0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10928656
Could I create Points1 & Points2 as Calculated fields in Matches table
and perform 2 queries in the OnCalcFields event

Pseudo:

where POINTS1 = SUM of T1Player1 & T1Player2 of all records in Games table with MatchId

where POINTS2 = SUM of T2Player1 & T2Player2 of all records in Games table with MatchId

if so, can anyone give me the SQL statement for this? Ive been away from SQL long time. I think i remember how dto do a Select though - <SMILE>

Shane
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10928659
Sorry, also, how to perform the query in the OnCalcFields event

Shane
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10928683
Something like...

Select Sum(T1Player1, T2Player2) from Games where MatchId=:id


Shane
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10928704
>, how to perform the query in the OnCalcFields event

like

var q : TADOQuery;  //must not be runtime
begin
  q := TADOQuery.Create(self);
  q.connection := adoconnection1;
  q.sql.text := 'Select sum().....';
  q.open;
  dataset.fieldbyname('CalculatedFieldName').AsInteger := q.Fields[0].AsInteger;
  q.close;
end;

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10928719
>if so, can anyone give me the SQL statement for this?
>Ive been away from SQL long time. I think i remember how dto do a
>Select though

will try later, just under pressure yet :-)

0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10928892
//must not be runtime

?


var q : TADOQuery;  //must not be runtime
begin
  q := TADOQuery.Create(self);



Shane
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10928941
//must not be runtime

means you can also drop and define querys at designtime ;-)
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10929017
yeah, i knew that, i thought you were saying that you can't create the query at runtime in a OnCalcFields Event, but then you went ahead and did it in code anyway - SMILE

Shane
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10929031

Select (SUM(T1Player1) + SUM(T1Player2)) From Games Where MatchId=:id

Shane
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10929057
? am i wrong with this english interpretation
(english is not my primary language)

may not -> is not allowed, use another way
must not -> is allowed, but other ways possible

:-))
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 10929073
>Select (SUM(T1Player1) + SUM(T1Player2)) From Games Where MatchId=:id

yep, for match-table for t1
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10929112

I get a EOleException : 'data type mismatch in criteria expression'

I assuming the TADO Query....

I even cut back on the sum, to one field and still get it...

qryMatchPoints.Close;
 qryMatchPoints.SQL.Clear;
 qryMatchPoints.SQL.Add('Select SUM(T1Player1) From Games Where MatchId=:id');
 QryMatchPoints.Parameters.ParamByName('id').Value:= DataSet.FieldByName('Id').AsInteger;
 if not QryMatchPoints.Prepared then
  QryMatchPoints.Prepared:= True;
 qryMatchPoints.Open;
 DataSet.FieldByName('Points1').asInteger:= qryMatchPoints.Fields[0].AsInteger;
 qryMatchPoints.Close;



Shane




0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10929150
Actually, I haven't changed my database table structure, still the original.

So, im summing the Points in games based on the Id of Matches.

Shane
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10929337
ANd this works, as long as there are no records in GAMES table:

procedure TfrmData.tblMatchesCalcFields(DataSet: TDataSet);
begin
 qryMatchPoints.Close;
 qryMatchPoints.SQL.Clear;
 qryMatchPoints.SQL.Add('Select SUM(Games.T1Player1) From Games Where MatchId=:id');
 QryMatchPoints.Parameters.ParamByName('id').Value:= DataSet.FieldByName('Id').AsInteger;
 if not QryMatchPoints.Prepared then
  QryMatchPoints.Prepared:= True;
 qryMatchPoints.Open;
 DataSet.FieldByName('Points1').asInteger:= qryMatchPoints.Fields[0].AsInteger;
 qryMatchPoints.Close;
end;

When there are records in Games table, it crashes, giving me the error above

Shane
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10930210
looks like a variant conversion problem

change this line

 QryMatchPoints.Parameters.ParamByName('id').Value:= DataSet.FieldByName('Id').AsInteger;

into

 QryMatchPoints.Parameters.ParamByName('id').Value:= DataSet.FieldByName('Id').Value;

or

 QryMatchPoints.Parameters.ParamByName('id').asInteger:= DataSet.FieldByName('Id').asInteger;

maybe you must also set the datatype of the parameter

hope this helps

(help on the sql-statements i can provide tomorrow)

meikl ;-)
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10930370
ok, let me try that, and i'll keep this open for you until tommorrow..

Shane
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10930515
Oh my god this is embarassing.... I can't believe I did that....

qryMatchPoints.SQL.Add('Select SUM(T1Player1) From Games Where MatchId=:id');


should be

qryMatchPoints.SQL.Add('Select SUM(T1Points1) From Games Where MatchId=:id');

You cant sum an integer filed


<smile>

Shane
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10930560
oops i mean you can't sum a string field....

SHane
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10930645
??? T1Points1 is a stringfield ???
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10930744

No T1Player1 was a string field  T1Points1 was the integer field

Games
_____
Id
MatchId

T1Player1 string
T1Points1 integer
T1Player2 string
T1Points2 integer
 
T2Player1 string
T2Points1 integer
T2Player2 string
T2Points2 integer

Shane

in the query, i had placed a call to sum the the T1Player1 field, and i was to blind to see it, it should have been T1Points1

anyway, it seams this approach works for now, until i decide what i am going to do with the normalization of the tables

procedure TfrmData.tblMatchesCalcFields(DataSet: TDataSet);
begin
 qryMatchPoints.Close;
 qryMatchPoints.SQL.Clear;
 qryMatchPoints.SQL.Add('Select (SUM(Games.T1Points1) + SUM(Games.T1Points1)) From Games Where MatchId=:p_Id');
  QryMatchPoints.Parameters.ParamByName('p_Id').Value:= DataSet.FieldByName('Id').AsInteger;
 if not QryMatchPoints.Prepared then
  QryMatchPoints.Prepared:= True;
 qryMatchPoints.Open;
 DataSet.FieldByName('Points1').asInteger:= qryMatchPoints.Fields[0].AsInteger;

 qryMatchPoints.Close;
 qryMatchPoints.SQL.Clear;
 qryMatchPoints.SQL.Add('Select (SUM(Games.T2Points1) + SUM(Games.T2Points2)) From Games Where MatchId=:p_Id');
  QryMatchPoints.Parameters.ParamByName('p_Id').Value:= DataSet.FieldByName('Id').AsInteger;
 if not QryMatchPoints.Prepared then
  QryMatchPoints.Prepared:= True;
 qryMatchPoints.Open;
 DataSet.FieldByName('Points2').asInteger:= qryMatchPoints.Fields[0].AsInteger;
end;

It does sum them up nicely..... but i know im going to have trouble when it comes to doing the sums for the players table and teams table....

Shane
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 500 total points
ID: 10938064
puu, shane,

had now setup an access database in your tabledesign.
the match-sums are already ok

for the players you need four sql-selects

select sum(t1points1) from games where T1player1 = :Player
select sum(t1points2) from games where T1player2 = :Player
select sum(t2points1) from games where T2player1 = :Player
select sum(t2points2) from games where T2player2 = :Player

the results itself must be sumed together

for the teams you need four selects too like

select sum(t1points1) from games where T1player1 in (select Player from players where team_id = :id)
....  other three same scheme as above ...

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10938079
btw. there may other selects possible,
but this may depend on the used database
(subselects and unions are not allways supported)
0
 
LVL 11

Author Comment

by:shaneholmes
ID: 10941586
kretzschmar, im actually changing design of my tables, if i dont then i know im gonna run into trouble in the future for more SQlstatements - especially related to reports.... but i thank you for the push... in the right direction.

More questions in the future....

Shane
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10942064
:-))

don't hestitate to ask about design and other issues about your project

glad to helped you

meikl ;-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delphi XE2 application frozen on Windows 10 10 277
Delphi TcxGrid group footer summary 3 211
How to debug For loops? 3 46
Convert MS Word document to a PDF file 9 64
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now