#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Unhappy Is it possible to use a single tquery for more than 5 tables?


    hi,
    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);
    begin
    PRQry.close;
    PRQry.SQL.Add('select count(*) as tot from PReturns;');
    PRQry.FieldByName('tot').AsInteger;
    PRQry.Active := true;
    PRQry.open;
    end;

    procedure Tquotfrm.Button5Click(Sender: TObject);
    begin
    quotQry.close;
    quotQry.SQL.Add('select count(*) as tot from QuHead;');
    quotQry.FieldByName('tot').AsInteger;
    quotQry.Active := true;
    quotQry.open;
    end;

    procedure Tquotfrm.Button6Click(Sender: TObject);
    begin
    DOQry.close;
    DOQry.SQL.Add('select count(*) as tot from DoHead;');
    DOQry.FieldByName('tot').AsInteger;
    DOQry.Active := true;
    DOQry.open;
    end;

    procedure Tquotfrm.Button7Click(Sender: TObject);
    begin
    SIQry.close;
    SIQry.SQL.Add('select count(*) as tot from SInvoice;');
    SIQry.FieldByName('tot').AsInteger;
    SIQry.Active := true;
    SIQry.open;
    end;

    procedure Tquotfrm.Button8Click(Sender: TObject);
    begin
    SRQry.close;
    SRQry.SQL.Add('select count(*) as tot from SReturns;');
    SRQry.FieldByName('tot').AsInteger;
    SRQry.Active := true;
    SRQry.open;
    end;

    procedure Tquotfrm.Button9Click(Sender: TObject);
    begin
    TRQry.close;
    TRQry.SQL.Add('select count(*) as tot from StkRptHdr ;');
    TRQry.FieldByName('tot').AsInteger;
    TRQry.Active := true;
    TRQry.open;

    I am using Delphi 2010 and sql
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Augsburg Germany
    Posts
    12
    Rep Power
    0
    Code:
    procedure TForm5.Button1Click(Sender: TObject);
    const
     Tables:Array[0..4] of String =('PReturns','QuHead','DoHead','SInvoice','SReturns');
     var
      i:Integer;
    begin
      for I := low(Tables) to High(Tables) do
          begin
             AQry.close;
             AQry.SQL.Text := 'select count(*) as tot from ' + Tables[i] + ';';
             AQry.open;
             Memo1.lines.add( AQry.FieldByName('tot').AsString;
          end;
    end;
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    356
    Rep Power
    7
    If you write individual queries and union them together, then you can get your output from just one Query. The query would look like:
    Code:
    select count(*) as tot, "PReturns" as FromTable from PReturns
    union
    select count(*) as tot, "QuHead" as FromTable from QuHead
    union
    select count(*) as tot, "DoHead" as FromTable from DoHead
    union
    select count(*) as tot, "SInvoise" as FromTable from SInvoice
    union
    select count(*) as tot, "SReturns" as FromTable from SReturns
    union
    select count(*) as tot, "StkRptHdr" as FromTable from StkRptHdr
    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.

    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)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    7

    Thumbs up


    Dear Friend,

    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
    union
    Select fieldTYPENumber from table2

    Note: Some types are supported in their types.

    ANother form to your problem, can be:

    Select fieldX,
    (Select FieldY from TB2) as AnotherNamefieldY,
    (Select FieldZ from TB3) as AnotherNamefieldZ
    from TB1

    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 03:10 PM.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Unhappy


    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
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    356
    Rep Power
    7
    Originally Posted by maxresh
    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
    I don't understand, I showed you how to get it down to ONE query and ONE grid?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    7

    Thumbs up


    Originally Posted by maxresh
    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
    I DONT UNDERSTANT very well! but...

    For to reduce your coding, the better form is to use "inheritance" VISUAL or in CODE:

    Example:

    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.

IMN logo majestic logo threadwatch logo seochat tools logo