?
Solved

Calculated Fields in a Query

Posted on 2003-11-26
10
Medium Priority
?
420 Views
Last Modified: 2010-04-05
Hi

I have a series of queries where I populate the SQL at runtime so I do not have lots of Query components on my form.  

I need to add calculated fields at runtime, but I cannot work out how to do it.

I cannot just add to my SQL something like "Qty*UnitPrice AS Amount" - I need to be able to call functions from OnCalcFields.

John
0
Comment
Question by:jdthedj
[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
10 Comments
 
LVL 2

Expert Comment

by:TheLeader
ID: 9828657
did you use Count function  !
ex:

Count (FieldName)
it will returns you the right result
0
 
LVL 3

Author Comment

by:jdthedj
ID: 9829281
Thanks for the comment TheLeader.  

That is not the problem.  I need to programatically add extra fields to the query at runtime.  I have several calculated fields per query which all do different calculations.

John
0
 

Expert Comment

by:lamtl354
ID: 9829431
i duno what is about?
could you post some code of yours here?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:jdthedj
ID: 9829550
Hi lamtl354

I have no problem adding calculated fields to a TQuery at design time.  I just double-click the TQuery component to bring up the fields editor and then click Add New Field.

I want to do the same thing in code at run time because when I clear out the SQL the extra fields I have added at design time disappear.
0
 
LVL 2

Expert Comment

by:TheLeader
ID: 9829874
well !!!
do you want the Extra Feild to be Count then !?
eventhough it will be a count(feild)query

could you be more specific ?
0
 
LVL 3

Author Comment

by:jdthedj
ID: 9830042
OK here is one of my SQLs:

SELECT T.ApplNum, T.FIRST, T.LAST, T.Exempt1, C.Contract, C.UniqueVal, C.WEnded, C.TimeSheet, C.Code, C.Qty, C.UnitPrice, C.TUnit, C.Com, C.InvoiceNo, E.OurRef, E.SplitWith, E.SplitRate, E.HolPercent,C1.Company, C2.TCode, C3.EXECUTIVE, C3.PermRate, C3.TempRate, TGross(Qty,TUnit,TCode) AS Gross, HQty(Qty,Gross,Code) AS Daily_Hours
FROM Accounts.WageInfo W, Applic_Temp.TempEmployeesFile T, JobOrder.ClientChargeout C, JobOrder.ExtrasChargeable E, JobOrder.ClientOrderDetails C1, JobOrder.ClientInvoiceDescripts C2, Other.Consultants C3
WHERE T.UniqueVal=W.UniqueVal AND C.UniqueVal=T.UniqueVal AND C.EmployeeLink=E.EmployeeLink AND E.OurRef=C1.OurRef AND C1.Executive=C3.Executive AND C.Code=C2.Code AND C.Contract =0 AND C.Com =-1 TGross(Qty,TUnit,TCode)
AND C3.Executive=:var1
AND C.WEnded=:var2
ORDER BY C.UniqueVal

The parts that I need calculated fields for are the expression TGross(....) and HQty(...)

I do not want to make a huge SQL to cater for them.  I want to add extra fields to the TQuery at runtime instead of design time.  As mentioned previously, I can easily add them at design time in the same way you add calculated fields to a TTable.

All I want to know is what code do I use to add the extra fields programatically at runtime. - Something like Query1.Fields.Add('Somename'), but I do not know the correct syntax. I have tried all sorts of variations on that syntax but it does not work.

John
0
 
LVL 8

Expert Comment

by:RickJ
ID: 9836689
I have not tried this but it may work

qry.FieldDefs.Add('TGross', ftFloat, 0) ;
qry.Fields[IndexOfField].FieldKind := fkCalculated ;

Hope this helps.
0
 
LVL 3

Author Comment

by:jdthedj
ID: 9839561
Hi RickJ

Thanks for your reply.

I have tried your method and I cannot get it to work.  However, I have now stumbled upon the answer myself:

Procedure TForm1.AddCalcField(Dataset: TDataset; FldType: TFieldType; FldName :String);
var Fld : TField; N : integer;
begin
      Dataset.FieldDefs.Update;
      For N := 0 to Dataset.FieldDefs.Count - 1 do
        If Dataset.FindField(Dataset.FieldDefs[N].Name) = nil then
          Dataset.FieldDefs.Items[N].CreateField(Dataset);

      If Dataset.FindField(fName) <> nil then Exit;
      Case FldType of
        ftString: Fld := TStringField.Create(Dataset);
        ftInteger: Fld := TIntegerField.Create(Dataset);
        ftCurrency: Fld := TCurrencyField.Create(Dataset);
        ftDateTime: Fld := TDateTimeField.Create(Dataset);
        ftFloat: Fld := TFloatField.Create(Dataset);
      end;
      Fld.Name := Dataset.Name+FldName;
      Fld.FieldName := FldName;
      Fld.DisplayLabel := FldName;
      Fld.Calculated := True;
      Fld.DataSet := Dataset;
end;

John
0
 

Accepted Solution

by:
SpazMODic earned 0 total points
ID: 9853170
PAQed, with points refunded (125)

SpazMODic
EE Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

777 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