Solved

Perform Queries on Calculated fields within OnCalcFields Event

Posted on 2004-04-26
27
708 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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry, also, how to perform the query in the OnCalcFields event

Shane
0
 
LVL 11

Author Comment

by:shaneholmes
Comment Utility
Something like...

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


Shane
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>, 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
Comment Utility
>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
Comment Utility
//must not be runtime

?


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



Shane
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
//must not be runtime

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

Author Comment

by:shaneholmes
Comment Utility
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
Comment Utility

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

Shane
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
? 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 27

Expert Comment

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

yep, for match-table for t1
0
 
LVL 11

Author Comment

by:shaneholmes
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ok, let me try that, and i'll keep this open for you until tommorrow..

Shane
0
 
LVL 11

Author Comment

by:shaneholmes
Comment Utility
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
Comment Utility
oops i mean you can't sum a string field....

SHane
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
??? T1Points1 is a stringfield ???
0
 
LVL 11

Author Comment

by:shaneholmes
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
:-))

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

glad to helped you

meikl ;-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

11 Experts available now in Live!

Get 1:1 Help Now