• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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
0
jdthedj
Asked:
jdthedj
1 Solution
 
TheLeaderCommented:
did you use Count function  !
ex:

Count (FieldName)
it will returns you the right result
0
 
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
0
 
lamtl354Commented:
i duno what is about?
could you post some code of yours here?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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.
0
 
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 ?
0
 
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
0
 
RickJCommented:
I have not tried this but it may work

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

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

SpazMODic
EE Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now