Solved

Connecting multiple Query to one Grid

Posted on 2006-10-19
7
265 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now