We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Connecting multiple Query to one Grid

saroren
saroren asked
on
Medium Priority
295 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
Comment
Watch Question

TheRealLokiSenior Developer
CERTIFIED EXPERT

Commented:
Do you wish to have write access to the dbgrid's data for these multiple queries, or will it be read only?
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.

Commented:
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


Author

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!!!!

Commented:
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

Author

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
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.