Calculated Fields in a Query

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
LVL 3
jdthedjAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TheLeaderCommented:
did you use Count function  !
ex:

Count (FieldName)
it will returns you the right result
jdthedjAuthor Commented:
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
lamtl354Commented:
i duno what is about?
could you post some code of yours here?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

jdthedjAuthor Commented:
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.
TheLeaderCommented:
well !!!
do you want the Extra Feild to be Count then !?
eventhough it will be a count(feild)query

could you be more specific ?
jdthedjAuthor Commented:
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
RickJCommented:
I have not tried this but it may work

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

Hope this helps.
jdthedjAuthor Commented:
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
SpazMODicCommented:
PAQed, with points refunded (125)

SpazMODic
EE Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.