November 29th, 2012, 01:45 AM
Is it possible to use a single tquery for more than 5 tables?
I have a small doubt,, may be a stupid question. I have more than 5 tables, I need all those. what I have done now is I made 5 dbgrid , 5 tquery and 5 data source. designing looks so complicated. I need to count all those elements in the table,dat I have given in a button event.so der is 5 button event also. what io wish to do is I need a single dbgird to sove my pbm, below is my code please help me to solve this pbm. as I am new to my job and programming)
procedure Tquotfrm.Button4Click(Sender: TObject);
PRQry.SQL.Add('select count(*) as tot from PReturns;');
PRQry.Active := true;
procedure Tquotfrm.Button5Click(Sender: TObject);
quotQry.SQL.Add('select count(*) as tot from QuHead;');
quotQry.Active := true;
procedure Tquotfrm.Button6Click(Sender: TObject);
DOQry.SQL.Add('select count(*) as tot from DoHead;');
DOQry.Active := true;
procedure Tquotfrm.Button7Click(Sender: TObject);
SIQry.SQL.Add('select count(*) as tot from SInvoice;');
SIQry.Active := true;
procedure Tquotfrm.Button8Click(Sender: TObject);
SRQry.SQL.Add('select count(*) as tot from SReturns;');
SRQry.Active := true;
procedure Tquotfrm.Button9Click(Sender: TObject);
TRQry.SQL.Add('select count(*) as tot from StkRptHdr ;');
TRQry.Active := true;
I am using Delphi 2010 and sql
November 29th, 2012, 02:13 AM
procedure TForm5.Button1Click(Sender: TObject);
Tables:Array[0..4] of String =('PReturns','QuHead','DoHead','SInvoice','SReturns');
for I := low(Tables) to High(Tables) do
AQry.SQL.Text := 'select count(*) as tot from ' + Tables[i] + ';';
November 29th, 2012, 08:27 AM
If you write individual queries and union them together, then you can get your output from just one Query. The query would look like:
So now you are getting two columns, the first is still the count while the 2nd is a constant which is the name of the table it is coming from. Now you can have just one dbgrid and it can display all the rows.
select count(*) as tot, "PReturns" as FromTable from PReturns
select count(*) as tot, "QuHead" as FromTable from QuHead
select count(*) as tot, "DoHead" as FromTable from DoHead
select count(*) as tot, "SInvoise" as FromTable from SInvoice
select count(*) as tot, "SReturns" as FromTable from SReturns
select count(*) as tot, "StkRptHdr" as FromTable from StkRptHdr
Also, obviously, this query is not updatable (nor where your original ones either).
(you may need to change the double quotes to single quotes depending on the database you are using)
November 29th, 2012, 02:05 PM
It is always advisable to avoid using COUNT (*) in SQL queries.
In the case of using "UNION", all the fields to be listed in the "SELECT" should have the same type in the sequence they were nominated. then:
Select from table1 fieldTYPENumber
Select fieldTYPENumber from table2
Note: Some types are supported in their types.
ANother form to your problem, can be:
(Select FieldY from TB2) as AnotherNamefieldY,
(Select FieldZ from TB3) as AnotherNamefieldZ
Note: YOu can to use "ALIAS" to fields as: FieldX as IDClient2
Search in FIrebird Forum (FAQ) about TABLES CTE (or CET) heheheh (I dont remember the name correct in moment)
Note: DONT USE DOUBLE QUOTE, see the Firebird forum
Last edited by emailx45; November 29th, 2012 at 02:10 PM.
November 30th, 2012, 09:23 AM
Thank you for the help frnds....
But what about the design part
.. i need to reduce number of tqueries. Sources and grid.. what to do
November 30th, 2012, 09:33 AM
I don't understand, I showed you how to get it down to ONE query and ONE grid?
Originally Posted by maxresh
December 1st, 2012, 10:35 AM
I DONT UNDERSTANT very well! but...
Originally Posted by maxresh
For to reduce your coding, the better form is to use "inheritance" VISUAL or in CODE:
If you have 1 form with your components and coding needs, so,
FILE -> NEW - OTHERs -> inherited -> choise the form model for another forms in your project.