Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to transfer the result of a consultation SQL for a matrix and a file text?

Posted on 1998-09-29
21
Medium Priority
?
221 Views
Last Modified: 2010-04-06
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
0
Comment
Question by:gaona
[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
  • 9
  • 2
21 Comments
 

Expert Comment

by:chrismo
ID: 1341148
Can you provide some sample data and sample output? I'm not clear what you're trying to do...
0
 

Author Comment

by:gaona
ID: 1341149
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  

0
 

Author Comment

by:gaona
ID: 1341150
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  

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Expert Comment

by:chrismo
ID: 1341151
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?
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1341152
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.


0
 

Author Comment

by:gaona
ID: 1341153
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
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1341154
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.
0
 

Author Comment

by:gaona
ID: 1341155
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  

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1341156
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?
0
 

Author Comment

by:gaona
ID: 1341157
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
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1341158
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('Name').AsString
                 + '[seperator]'
                 + TableValues.FieldByName('Value').AsString
                 + ...
        )
 {put values in array }


 TableValues.Next;
end;
{close file}
TableValues.Close;


Am I still missing something?

Zif.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1341159
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.
0
 

Author Comment

by:gaona
ID: 1341160
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  

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1341161
then i've problems... what do you mean with consultation SQL... if i may ask
0
 

Author Comment

by:gaona
ID: 1341162
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
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1341163
... sorry still don't understand... maybe you can show me your SQL code here... maybe with one of DBDEMOS tables...
0
 

Author Comment

by:gaona
ID: 1341164
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(TForm1, 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.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1341165
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.FieldByName('...').AsString+' '+Query1.FieldByName('..').AsString);

 Query1.Next;
end;
{ close file here }

Regards, Zif.
0
 

Author Comment

by:gaona
ID: 1341166
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
0
 
LVL 8

Accepted Solution

by:
ZifNab earned 200 total points
ID: 1341167
goana,

 Sorry, I forgot that I was working on this question... terrible sorry, hope you forgive me in a time.

 Are you sure everything worked or are there still some problems?

Zif.
0
 

Author Comment

by:gaona
ID: 1341168
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

670 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