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

Connecting multiple Query to one Grid

Hi,

Backend - Progress Database
Front End - Delphi
ODBC - SQL92

I have lots of Restriction using SQL commands with Progress Database, since it doesn't support all of them.  For example the below Query doesn't work.

SELECT t1.tr_effdate, COUNT(t1.tr_effdate) AS DayCount, (SELECT COUNT(*) FROM tr_hist t2
    WHERE t2.tr_effdate BETWEEN '1 Jun 2006' AND t1.tr_effdate) AS MonthToDate
FROM tr_hist t1
GROUP BY t1.tr_effdate
ORDER BY t1.tr_effdate


My Questions now,

I want to used multiple Queries and display the data on one TDBGrid. I will have a key field in each query .

Is this possible , if so can someone explan with a small example.

Let me know if you need any other details.

Thanks
0
saroren
Asked:
saroren
1 Solution
 
TheRealLokiSenior DeveloperCommented:
Do you wish to have write access to the dbgrid's data for these multiple queries, or will it be read only?
0
 
wimmeyvaertCommented:
I don't think it is possible to have multiple queries linked to 1 dbgrid at the same time.

The only way I see is to make use of a UNION-Clause in your Query to combine the results of several queries.
Only condition is that all queries have same output-fields I guess.

Example :
SELECT 'Customer' AS PersonType, Firstname, LastName, Address
FROM Customers
UNION
SELECT 'Supplier' AS PersonType, FirstName, LastName, Address
FROM Suppliers
ORDER BY PersonType, LastName, FirstName

By use of such a query I can see all suppliers AND all Customers in 1 grid. But in fact I still use 1 SQL-Statement to retrieve these data.
But that will be the closest you can get I guess.

Best regards,

The Mayor.
0
 
cobi100Commented:
how about feeding the data to a ClientDataset?

Drop a TClientDataSet
Add 3 fields to it one to hold the date, and the other for DayCount and MonthToDate
once the fields are created, call the CreateDataset method of the TClientDataset. or at design time Right-Click select Create Dataset
iterate through each of the queries and assign the values of the ClientDataSet fields to the query values


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sarorenAuthor Commented:
Hi TheRealLoki   - I want the grid to be read only.

Hi wimmeyvaert  - My query is too complex and too many record .

Hi cobi100  - i will try this, as i am new to Delphi, if iam not able to figure this i will let you know.

Thanks for the help!!!!
0
 
cobi100Commented:
well on my post, probably the most complicated part would be to iterate through the records, try it like this:

query1.open;
query1.first;
while not(query1.eof) do
begin
  ClientDataset1.FieldbyName('DayCount').AsInteger := query1.FieldByName('DayCount').AsInteger;
  query1.next;
end
0
 
sarorenAuthor Commented:
Hi Cobi100,

Sorry for the dealy.

I tried your Query but it works only for one Query. But i want to display data's from two queries in a single grid.

If i right click on the Tclient dataset and choose create Dataset, then i choose Assign Local Data, at thsi point i am able to choose only one query .  

is there a way i can choose multiple query and display the data on a singel Grid.

Thanks
0
 
cobi100Commented:
yes, there is, don't assign local data instead use the code I posted in my previous post, maybe on form activate or something like that, you have to use that code for each of the queries you want to add to the client dataset, like this:

query1.open;
query1.first;
while not(query1.eof) do
begin
  ClientDataset1.Insert;
  ClientDataset1.FieldbyName('DayCount').AsInteger := query1.FieldByName('DayCount').AsInteger;
  ClientDataset1.Post;
  query1.next;
end

this is for the first query, and for the second one you want to do this:

query2.open;
query2.first;
ClientDataset1.First;
while not(query2.eof) do
begin
  ClientDataset1.Edit;  
  ClientDataset1.FieldbyName('MonthToDate').AsInteger := query1.FieldByName('MonthToDate').AsInteger;
  ClientDataset1.Post;
  query2.next;
end

hmmmm, now that I'm seeing it, this will only work if both of the queries return the same number of records and in the same order, if that's not the case then you need to locate the right record to do the update, I think you can use ClientDataset1.Locate
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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