Solved

# Perform Queries on Calculated fields within OnCalcFields Event

Posted on 2004-04-26
722 Views
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
Question by:shaneholmes
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 14
• 12

LVL 17

Expert Comment

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

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

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

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

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

Shane
0

LVL 11

Author Comment

ID: 10928683
Something like...

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

Shane
0

LVL 27

Expert Comment

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

like

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

meikl ;-)
0

LVL 27

Expert Comment

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

ID: 10928892
//must not be runtime

?

var q : TADOQuery;  //must not be runtime
begin

Shane
0

LVL 27

Expert Comment

ID: 10928941
//must not be runtime

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

LVL 11

Author Comment

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

ID: 10929031

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

Shane
0

LVL 27

Expert Comment

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

LVL 27

Expert Comment

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

yep, for match-table for t1
0

LVL 11

Author Comment

ID: 10929112

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

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

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

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

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

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

Shane
0

LVL 11

Author Comment

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

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

SHane
0

LVL 27

Expert Comment

ID: 10930645
??? T1Points1 is a stringfield ???
0

LVL 11

Author Comment

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

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

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

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

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

ID: 10942064
:-))

meikl ;-)
0

## Featured Post

Question has a verified solution.

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

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â€¦
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â€¦
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to fâ€¦
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll