gaona
asked on
How to transfer the result of a consultation SQL for a matrix and a file text?
How to transfer the result of a consultation SQL for a matrix and a file text?
I have a Table in Paradox with the following fields:
Field Type Size
Code char 3
Valor1 num 12
Valor2 num 12
Valor3 num 12
With these fields, I want to generate a consultation SQL, grouped by the first digit of the field code with the totals for each digit of the fields Valor1, Valor2, Valor3. After generating the consultation, I want that these total ones are transferred for a matrix and a file text, with the accumulated values of the fields and the digit regarding these values.
How can I make this?
Thank you
Gaona
I have a Table in Paradox with the following fields:
Field Type Size
Code char 3
Valor1 num 12
Valor2 num 12
Valor3 num 12
With these fields, I want to generate a consultation SQL, grouped by the first digit of the field code with the totals for each digit of the fields Valor1, Valor2, Valor3. After generating the consultation, I want that these total ones are transferred for a matrix and a file text, with the accumulated values of the fields and the digit regarding these values.
How can I make this?
Thank you
Gaona
Can you provide some sample data and sample output? I'm not clear what you're trying to do...
ASKER
Dear Crhismo,
Independently of the data that I to enter, he would like to know as to save the result of a consultation in a head office and I File text.
Thankful Gaona
Independently of the data that I to enter, he would like to know as to save the result of a consultation in a head office and I File text.
Thankful Gaona
ASKER
Dear Crhismo,
Independently of the data that I to enter, he would like to know as to save the result of a consultation in a Matrix and I File text.
Thankful Gaona
Independently of the data that I to enter, he would like to know as to save the result of a consultation in a Matrix and I File text.
Thankful Gaona
Are you needing help with the SQL Statement - or with the code to output the results to a text file? What exactly do you mean by a Matrix? Do you need to display the results on a form in a grid?
gaona,
?
Just iterate through the SQL table and copy then row-by row to a text file or whatever.
Or use a batchmove, or use the ascii type of a table as output, ...
ZiF.
?
Just iterate through the SQL table and copy then row-by row to a text file or whatever.
Or use a batchmove, or use the ascii type of a table as output, ...
ZiF.
ASKER
Dear Crhismo and ZifNab
As my English is bad, I think I didn't get myself to explain well.
I want to save the data in an Array and a file text, so that it can use the results after the research to be done. In my case, I have to save the results of the research, to print them later.
Thank you
Gaona
As my English is bad, I think I didn't get myself to explain well.
I want to save the data in an Array and a file text, so that it can use the results after the research to be done. In my case, I have to save the results of the research, to print them later.
Thank you
Gaona
gaona, I still can't see the problem about that.
An SQL gives a result, then just iterate through this SQL-table and put it in a text-file or an array like you want. Or use batchmove to transfer the results SQL table to whatever you want.
Can you give me a sort of example what you want to achive?
Zif.
An SQL gives a result, then just iterate through this SQL-table and put it in a text-file or an array like you want. Or use batchmove to transfer the results SQL table to whatever you want.
Can you give me a sort of example what you want to achive?
Zif.
ASKER
Dear Zif
In my file, I have the following fields and values.
Code Val01 Val02 Val03
7.13 150,00 100,00 70,00
1.15 75,00 95,00 80,00
1.12 102,00 85,00 80,00
1.15 120,00 110,00 98,00
7.13 65,00 80,00 75,00
1.15 115,00 100,00 105,00
1.12 125,00 115,00 50,00
1.15 45,00 65,00 20,00
Of this table, me that to obtain the sum of Val01, Val02, Val03 for each typed Code, separating for type of Code, that is:
For Code 7.13, Sum_Val01=215,00; Sum_Val02=180,00 and Sum_Val03=145,00
For Code 1.15, Sum_Val01=355,00; Sum_Val02=375,00 and Sum_Val03=303,00
For Code 1.12, Sum_Val01=227,00; Sum_Val02=200,00 and Sum_Val03=130,00
With these results, later on, I have to print these values added by Code and the total sum of everybody the values. For this, I elaborated a consultation SQL, containing for code and adding these values in calculated fields.
The one that I want now, is to record the results of this consultation in an array and in a file text so that later I can print them.
Thankful
Gaona
In my file, I have the following fields and values.
Code Val01 Val02 Val03
7.13 150,00 100,00 70,00
1.15 75,00 95,00 80,00
1.12 102,00 85,00 80,00
1.15 120,00 110,00 98,00
7.13 65,00 80,00 75,00
1.15 115,00 100,00 105,00
1.12 125,00 115,00 50,00
1.15 45,00 65,00 20,00
Of this table, me that to obtain the sum of Val01, Val02, Val03 for each typed Code, separating for type of Code, that is:
For Code 7.13, Sum_Val01=215,00; Sum_Val02=180,00 and Sum_Val03=145,00
For Code 1.15, Sum_Val01=355,00; Sum_Val02=375,00 and Sum_Val03=303,00
For Code 1.12, Sum_Val01=227,00; Sum_Val02=200,00 and Sum_Val03=130,00
With these results, later on, I have to print these values added by Code and the total sum of everybody the values. For this, I elaborated a consultation SQL, containing for code and adding these values in calculated fields.
The one that I want now, is to record the results of this consultation in an array and in a file text so that later I can print them.
Thankful
Gaona
So you already have accomplished the result SQL (= the table which contains the values which have to be put into the array and file). Correct?
ASKER
Dear Zif
Its placement is correct. The consultation was already created. The one that I need now is to record the results in an array and a file text.
Thankful
Gaona
Its placement is correct. The consultation was already created. The one that I need now is to record the results in an array and a file text.
Thankful
Gaona
what about this simple aproach :
Use TFileStream for opening your textfile... or use the AssignFile method, with Write(FType, ...);
TableValues.Open;
TableValues.First;
{open filename}
While not TableValues.EOF do begin
Writeln(TxtFile, TableValues.FieldByName('N ame').AsSt ring
+ '[seperator]'
+ TableValues.FieldByName('V alue').AsS tring
+ ...
)
{put values in array }
TableValues.Next;
end;
{close file}
TableValues.Close;
Am I still missing something?
Zif.
Use TFileStream for opening your textfile... or use the AssignFile method, with Write(FType, ...);
TableValues.Open;
TableValues.First;
{open filename}
While not TableValues.EOF do begin
Writeln(TxtFile, TableValues.FieldByName('N
+ '[seperator]'
+ TableValues.FieldByName('V
+ ...
)
{put values in array }
TableValues.Next;
end;
{close file}
TableValues.Close;
Am I still missing something?
Zif.
goana,
I guess for the text-file, it can even easier!
put a table onto form, give it as tabletype : ttASCII,
then use a batchmove to copy the table with values to the ASCII type table.
Use previous iteration for filling of array.
Regards, Zif.
I guess for the text-file, it can even easier!
put a table onto form, give it as tabletype : ttASCII,
then use a batchmove to copy the table with values to the ASCII type table.
Use previous iteration for filling of array.
Regards, Zif.
ASKER
Dear Zif
I think I didn't know how to express me right. To transfer the values of a table for an array or a file text, I already know. The one that I don't know, is to transfer the values of a consultation SQL for an array or a table.
Thank you
Gaona
I think I didn't know how to express me right. To transfer the values of a table for an array or a file text, I already know. The one that I don't know, is to transfer the values of a consultation SQL for an array or a table.
Thank you
Gaona
then i've problems... what do you mean with consultation SQL... if i may ask
ASKER
Dear Zif
I think I am having problems in translating what I want for English. Imagine that you had a query that of that as result the sum of several fields of a table Paradox groped by a certain field (in my case a field code). It supposed that these results could not be printed directly by Quick Report in a band detail, and, that you had to record these results to print them later.
How would you do?
Thankful
Gaona
I think I am having problems in translating what I want for English. Imagine that you had a query that of that as result the sum of several fields of a table Paradox groped by a certain field (in my case a field code). It supposed that these results could not be printed directly by Quick Report in a band detail, and, that you had to record these results to print them later.
How would you do?
Thankful
Gaona
... sorry still don't understand... maybe you can show me your SQL code here... maybe with one of DBDEMOS tables...
ASKER
As you asked, I created a simple form, using the table Items.db of DBDEMO in a DBGrid, according to the list below:
Form1.DFM
object Form1: TForm1
Left = 313
Top = 150
Width = 234
Height = 387
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Panel1: TPanel
Left = 0
Top = 56
Width = 225
Height = 304
Caption = 'Panel1'
TabOrder = 0
object DBGrid1: TDBGrid
Left = 1
Top = 1
Width = 224
Height = 302
DataSource = DataSource1
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
Columns = <
item
Expanded = False
FieldName = 'OrderNo'
Visible = True
end
item
Expanded = False
FieldName = 'ValTot'
Visible = True
end
item
Expanded = False
FieldName = 'QtyTot'
Visible = True
end>
end
end
object BitBtn1: TBitBtn
Left = 128
Top = 16
Width = 75
Height = 25
Caption = 'Exec'
TabOrder = 1
OnClick = BitBtn1Click
end
object DataSource1: TDataSource
DataSet = Query1
Left = 16
Top = 8
end
object Query1: TQuery
Active = True
DatabaseName = 'DBDEMOS'
SQL.Strings = (
'select OrderNo, Qty*1.5 as QtyTot, PartNo as ValTot'
'from items.db')
Left = 48
Top = 8
ParamData = <>
object Query1OrderNo: TFloatField
FieldName = 'OrderNo'
end
object Query1QtyTot: TFloatField
FieldName = 'QtyTot'
end
object Query1ValTot: TFloatField
FieldName = 'ValTot'
end
end
end
Unit1.Pas
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Db, DBTables, Grids, DBGrids, ExtCtrls, StdCtrls, Buttons;
type
TForm1 = class(TForm)
Panel1: TPanel;
DBGrid1: TDBGrid;
DataSource1: TDataSource;
Query1: TQuery;
BitBtn1: TBitBtn;
Query1OrderNo: TFloatField;
Query1QtyTot: TFloatField;
Query1ValTot: TFloatField;
procedure BitBtn1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure TForm1.BitBtn1Click(Sender : TObject);
begin
Query1.Active:=False;
Query1.Sql.Clear;
Query1.Sql.Add('Select OrderNo, Sum(Qty) as QtyTot, Sum(Qty*PartNo) as ValTot');
Query1.Sql.Add('from ''Items.db''');
Query1.Sql.Add('Group by OrderNo');
Query1.Active:=True;
end;
end.
Project1.dpr
program Project1;
uses
Forms,
Unit1 in 'Unit1.pas' {Form1};
{$R *.RES}
begin
Application.Initialize;
Application.CreateForm(TFo rm1, Form1);
Application.Run;
end.
The one that I want to know, is as copying the data presented in DBGrid for an array and a file text.
Form1.DFM
object Form1: TForm1
Left = 313
Top = 150
Width = 234
Height = 387
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Panel1: TPanel
Left = 0
Top = 56
Width = 225
Height = 304
Caption = 'Panel1'
TabOrder = 0
object DBGrid1: TDBGrid
Left = 1
Top = 1
Width = 224
Height = 302
DataSource = DataSource1
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
Columns = <
item
Expanded = False
FieldName = 'OrderNo'
Visible = True
end
item
Expanded = False
FieldName = 'ValTot'
Visible = True
end
item
Expanded = False
FieldName = 'QtyTot'
Visible = True
end>
end
end
object BitBtn1: TBitBtn
Left = 128
Top = 16
Width = 75
Height = 25
Caption = 'Exec'
TabOrder = 1
OnClick = BitBtn1Click
end
object DataSource1: TDataSource
DataSet = Query1
Left = 16
Top = 8
end
object Query1: TQuery
Active = True
DatabaseName = 'DBDEMOS'
SQL.Strings = (
'select OrderNo, Qty*1.5 as QtyTot, PartNo as ValTot'
'from items.db')
Left = 48
Top = 8
ParamData = <>
object Query1OrderNo: TFloatField
FieldName = 'OrderNo'
end
object Query1QtyTot: TFloatField
FieldName = 'QtyTot'
end
object Query1ValTot: TFloatField
FieldName = 'ValTot'
end
end
end
Unit1.Pas
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Db, DBTables, Grids, DBGrids, ExtCtrls, StdCtrls, Buttons;
type
TForm1 = class(TForm)
Panel1: TPanel;
DBGrid1: TDBGrid;
DataSource1: TDataSource;
Query1: TQuery;
BitBtn1: TBitBtn;
Query1OrderNo: TFloatField;
Query1QtyTot: TFloatField;
Query1ValTot: TFloatField;
procedure BitBtn1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure TForm1.BitBtn1Click(Sender
begin
Query1.Active:=False;
Query1.Sql.Clear;
Query1.Sql.Add('Select OrderNo, Sum(Qty) as QtyTot, Sum(Qty*PartNo) as ValTot');
Query1.Sql.Add('from ''Items.db''');
Query1.Sql.Add('Group by OrderNo');
Query1.Active:=True;
end;
end.
Project1.dpr
program Project1;
uses
Forms,
Unit1 in 'Unit1.pas' {Form1};
{$R *.RES}
begin
Application.Initialize;
Application.CreateForm(TFo
Application.Run;
end.
The one that I want to know, is as copying the data presented in DBGrid for an array and a file text.
well then,
why not just :
Query1.First;
{ open file here }
while not Query1.EOF do begin
{ Put here code to put data in field or array }
{e.g.}
Writeln(F,Query1.FieldByNa me('...'). AsString+' '+Query1.FieldByName('..') .AsString) ;
Query1.Next;
end;
{ close file here }
Regards, Zif.
why not just :
Query1.First;
{ open file here }
while not Query1.EOF do begin
{ Put here code to put data in field or array }
{e.g.}
Writeln(F,Query1.FieldByNa
Query1.Next;
end;
{ close file here }
Regards, Zif.
ASKER
Dear Ziff,
Its answer was correct and it worked very well. Please tell me as doing to transfer the points of the question for you.
Thankful
Gaona
Its answer was correct and it worked very well. Please tell me as doing to transfer the points of the question for you.
Thankful
Gaona
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear Zif
Its solution, in spite of difficult, is correct and he/she didn't leave you doubt with relationship to the operation. The one that I didn't know, is that a Query can be read as if it went a common table.
Hugs
Gaona
Its solution, in spite of difficult, is correct and he/she didn't leave you doubt with relationship to the operation. The one that I didn't know, is that a Query can be read as if it went a common table.
Hugs
Gaona