Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Perform Queries on Calculated fields within OnCalcFields Event

Posted on 2004-04-26
27
Medium Priority
?
734 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
[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
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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 my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

636 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