Solved

Connecting multiple Query to one Grid

Posted on 2006-10-19
7
268 Views
Last Modified: 2010-04-05
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
Comment
Question by:saroren
7 Comments
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 17770442
Do you wish to have write access to the dbgrid's data for these multiple queries, or will it be read only?
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 17771686
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
 
LVL 3

Expert Comment

by:cobi100
ID: 17777527
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:saroren
ID: 17777601
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
 
LVL 3

Expert Comment

by:cobi100
ID: 17777643
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
 

Author Comment

by:saroren
ID: 17822347
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
 
LVL 3

Accepted Solution

by:
cobi100 earned 500 total points
ID: 17845266
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
proper way to parse url in delphi 2 201
Virtuailstring tree compare node issue 14 116
RESTRequest Parameter 4 30
Breakpoint doesn't stop in my variable 3 27
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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