Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.
Procedure SpeedMeUp;
begin
DummyTable.DisableControls;
Query1.SQL.Clear;
Query1.SQL.Add('Select Some fields from some tables using join');
Query1.Open; // Note there is OnCalcFields Event
While not Query1.EOF do
begin
A := Query1.Fields[0].AsFLoat;
B := Query1.Fields[5]. AsString ++ 'Hello';
.
.
.
DummyTable.AppendRecord([A,B, Query1.Fields[4].AsString]);
Query1.Next;
end;
DummyTable.EnableControls; // Show Dummy Table in a DBGrid
end;
procedure TFormX.Query1CalcFields(DataSet: TDataSet);
begin
Query1QQQ.AsFloat := Fun1(Cmbx1.Text,Query1N.AsString,Query1S.AsString,0,Date);
Query1CCC.AsFloat := Fun2(Query1N.AsString,Query1S.AsString,1,0,Date);
end;
// Now The Problem (I Think) is in Func1 and Func2
function Func1(S1, S2, S3 : String; K : Integer; DD : Date);
begin
Query1.SQL.Add('select some fields from table1');
Query1.Open;
X1 := Query1.Fields[0].AsFloat;
Query2.SQL.Add('select some fields from table2');
Query2.Open;
X2 := Query2.Fields[0].AsFloat;
Query3.SQL.Add('select some fields from table3');
Query3.Open;
X3 := Query3.Fields[0].AsFloat;
.
.
.
.
.
Query50.SQL.Add('select some fields from table50');
Query50.Open;
X50 := Query50.Fields[0].AsFloat;
Result := X1 + X2 (X3 * X5) + X6 - X7 ... + X50;
end;
{*****************************}
function Func2(S1, S2, : String; K,J : Integer; DD : Date);
begin
Query1.SQL.Add('select something else than selected in Queries in Func1 from table1');
Query1.Open;
XX := Some Calculations;
WW := Some Calculations
While not Query1.EOF do
begin
DummyTable.AppendRecord[Query1.Fields[0].AsString, XX, WW]);
Query1.Next;
end;
Query2.SQL.Add('select something else than selected in Queries in Func1 from table2');
Query2.Open;
XX := Some Calculations;
WW := Some Calculations
While not Query2.EOF do
begin
DummyTable.AppendRecord[Query2.Fields[0].AsString, XX, WW]); //same dummy table
Query2.Next;
end;
Query3.SQL.Add('select something else than selected in Queries in Func1 from table3');
Query3.Open;
XX := Some Calculations;
WW := Some Calculations
While not Query3.EOF do
begin
DummyTable.AppendRecord[Query3.Fields[0].AsString, XX, WW]); // same dummy table
Query3.Next;
end;
.
.
.
.
.
Query60.SQL.Add('select something else than selected in Queries in Func1 from table60');
Query60.Open;
XX := Some Calculations;
WW := Some Calculations
While not Query60.EOF do
begin
DummyTable.AppendRecord[Query60.Fields[0].AsString, XX, WW]);
Query60.Next;
end;
{*******}
Result := Do some calculation on Dummy table;
end;
INSERT INTO DummyTable
(
Field[0],
field[1],
field[2]
)
SELECT field[0],
Some Calculations,
Some Calculations]
FROM table1
WHERE whatever);
function CalcQTY(PPPStore : String;PPPNumber : String;PPPSource : String; WhatEx : Integer; Exclude : String; EndDate : TDate) : Real;
var
GOrders, LOrders, First, LDInvoices, DInvoices, AdjustPlus, AdjustMinus, GroupMake, GroupMakeMinus, MI, TIN,TOUT : Real;
SDate : String;
CalcQTYQuery : TQuery;
begin
SDate := DateToStr(EndDate);
CalcQTYQuery := TQuery.Create(Form1);
CalcQTYQuery.DatabaseName := 'MyDataBase';
{ Starting the job }
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "GlobalOrderList.db" d, "GlobalOrders.db" d1 WHERE (d."Part Number" = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '") AND (d1."Order Number" = d."Order Number") AND (d1."Store Number" = "' + PPPStore + '")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
GOrders := CalcQTYQuery.Fields[0].AsFloat
else
GOrders := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "LocalOrderList.db" d, "LocalOrders.db" d1 WHERE (d."Part Number" = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '") AND (d1."Order Number" = d."Order Number") AND (d1."Store Number" = "' + PPPStore + '")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
LOrders := CalcQTYQuery.Fields[0].AsFloat
else
LOrders := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "InvoicesList.db" d, "Invoices.DB" D1 WHERE (d.Number = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '") AND (D1.Number = d.INumber) AND (D1.Store = "' + PPPStore + '") AND (D1.ICase = "1.0")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
LDInvoices := CalcQTYQuery.Fields[0].AsFloat
else
LDInvoices := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "InvoicesList.db" d, "Invoices.DB" D1 WHERE (d.Number = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '") AND (D1.Number = d.INumber) AND (D1.Store = "' + PPPStore + '") AND (D1.ICase = "0.0")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
DInvoices := CalcQTYQuery.Fields[0].AsFloat
else
DInvoices := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "Adjust.DB" WHERE ("Adjust.DB"."Part Number" = "' + PPPNumber + '") AND ("Adjust.DB"."Date" <= "' + SDate + '") AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '") AND (QTY > "0")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
AdjustPlus := CalcQTYQuery.Fields[0].AsFloat
else
AdjustPlus := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "Adjust.DB" WHERE ("Adjust.DB"."Part Number" = "' + PPPNumber + '") AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '") AND ("Adjust.DB"."Date" <= "' + SDate + '") AND (QTY < "0")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
AdjustMinus := CalcQTYQuery.Fields[0].AsFloat * -1
else
AdjustMinus := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "FirstTime.DB" WHERE (Number = "' + PPPNumber + '") AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
First := CalcQTYQuery.Fields[0].AsFloat
else
First := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(D1.QTY) FROM "MI.db" d, "MI List.DB" D1 WHERE (d.Store = "' + PPPStore + '") AND (D1."MI Number" = d."MI Number") AND (D1."Part Number" = "' + PPPNumber + '") AND (D1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
MI := CalcQTYQuery.Fields[0].AsFloat
else
MI := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(d1.QTY) FROM "Move.db" d, "MoveList.db" d1 WHERE (d."Out Store" = "' + PPPStore + '") AND (d1."Move Number" = d.Number) AND (d1."Part Number" = "' + PPPNumber + '") AND (d1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
TOUT := CalcQTYQuery.Fields[0].AsFloat
else
TOUT := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(d1.QTY) FROM "Move.db" d, "MoveList.db" d1 WHERE (d."In Store" = "' + PPPStore + '") AND (d1."Move Number" = d.Number) AND (d1."Part Number" = "' + PPPNumber + '") AND (d1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
TIN := CalcQTYQuery.Fields[0].AsFloat
else
TIN := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "GroupMaker.DB" WHERE ("GroupMaker.DB"."GroupNumber" = "' + PPPNumber + '") AND ("GroupMaker.DB"."Source" = "' + PPPSource + '") AND ("GroupMaker.DB"."Store" = "' + PPPStore + '") AND ("GroupMaker.DB"."Date" <= "' + SDate + '")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
GroupMake := CalcQTYQuery.Fields[0].AsFloat
else
GroupMake := 0;
CalcQTYQuery.Close;
CalcQTYQuery.SQL.Clear;
CalcQTYQuery.SQL.Add('SELECT SUM(D.QTY) FROM "GroupMakerList.DB" D, "GroupMaker.DB" D1 WHERE (D."Part Number" = "' + PPPNumber + '") AND (D."Part Source" = "' + PPPSource + '") AND (D1.Number = D."Maker Number") AND (D1.Store = "' + PPPStore + '") AND (D1."Date" <= "' + SDate + '")');
CalcQTYQuery.Open;
if not CalcQTYQuery.IsEmpty then
GroupMakeMinus := CalcQTYQuery.Fields[0].AsFloat
else
GroupMakeMinus := 0;
CalcQTYQuery.Free;
Result := (GOrders + LOrders + AdjustPlus + First + MI + TIN + GroupMake) - (LDInvoices + DInvoices + AdjustMinus + TOUT + GroupMakeMinus);
end;
create function totalsum return number is
temp number(53,20);
begin
select sum(calc_GOrders)+sum(calc_LOrders)+sum(calc_AdjustPlus)+sum(calc_First)+sum(calc_MI)+sum(calc_TIN)+sum(calc_GroupMake))-(sum(calc_LDInvoices) + sum(calc_DInvoices) + sum(calc_AdjustMinus) + sum(calc_TOUT) + sum(calc_GroupMakeMinus)) as Total_Table_Sum
into temp
From
-- rest of query from FactorB
return temp;
end;
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Delphi Dbf export problem to a Visual Foxpro application | 6 | 187 | |
Performance of SQL statement | 37 | 111 | |
PHP preg_replace code convert to Delphi | 14 | 55 | |
How to make Sign in, using Clientdataset? | 1 | 14 |
Join the community of 500,000 technology professionals and ask your questions.