Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jun 2008
    Posts
    6

    Unanswered: SQL query to retrieve from multiple tables

    hi, can someone plz help me with this:

    i have different tables for each month, ie 200701, 200702, ...
    i have the following query
    SELECT COUNT(*) FROM 200701 WHERE ISSUEDATE = '2007-01-01' and SECURITY = 'GIC'

    but i want this query to return the results from all tables, not just 200701. can someone plz tell me how to fix this query so that it looks up all the tables. thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use "UNION ALL":

    SELECT COUNT(*) FROM 200701 WHERE ISSUEDATE = '2007-01-01' and SECURITY = 'GIC'
    UNION ALL
    SELECT COUNT(*) FROM 200702 WHERE ISSUEDATE = '2007-01-01' and SECURITY = 'GIC'
    ...

    Andy

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    all of the tables for a year/quarter?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why do you have different tables for each month?

    and why would a table for 200702 have any rows for 200701 in it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2008
    Posts
    6
    thanks a lot arwinner!
    cougar8000, i would want to return all the tables for a year. so thats 12 tables, would this still work then if i am using 12 different tables in the query? Also, do I type UNION ALL each time I add in a new query then (ie for this example, i would type UNION ALL 11 times?) . Thanks!

  6. #6
    Join Date
    Jun 2008
    Posts
    6
    r937, my tables contain customer data for investment products (a 1 year investment called GIC). so if a GIC is issued on 1/1/2007, then for every month-end until maturity or until it is redeemed, there will be a row corresponding to that customer's investment. so each month's table will provide month-end data for all GIC's still outstanding. so i want my query to extract all the data for GIC's issued on 1/1/2007. thanks.

  7. #7
    Join Date
    Jun 2008
    Posts
    6
    guys the query doesn't seem to work, i get the following error message:
    the table's name is correct as typed in teh query, im guessing this message appears because there is a limit as to how many statements i can connect using UNION or UNION ALL? any ideas? thanks!

    SQL execution error.

    A database manager error occurred. : "200711" is an undefined name.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    AFAIK there is no limit. Check the name again.

    Andy

  9. #9
    Join Date
    Jun 2008
    Posts
    6
    thx andy! it turns out that table 200711 isnt available right now, because even a basic query on that table doesnt work.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is a bad idea to use all numbers for a table name.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would recommend that you create a union all view over the 12 tables of a year, then run all operations (instert/update/delete/select) on the view. DB2 (at least LUW) has mechanisms in place to filter-out tables that are not needed for a certain table.

    For a description, see here: http://www.ibm.com/developerworks/db...202zuzarte.pdf and http://www.ibm.com/developerworks/db...209rielau.html
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    I would recommend that you create a union all view over the 12 tables of a year, then run all operations (instert/update/delete/select) on the view. DB2 (at least LUW) has mechanisms in place to filter-out tables that are not needed for a certain table.

    For a description, see here: http://www.ibm.com/developerworks/db...202zuzarte.pdf and http://www.ibm.com/developerworks/db...209rielau.html
    The UNION all view is a great idea. In addition to being able to filter out tables not needed, UNION ALL views can use intra-partition parallelism if set up correctly (along with some db parms that need to be set up to allow it).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Yeap, that is exactly why I asked what is he trying to do.

    On the other hand, why can't you have all of this data in one table?! I do not know how wide or how big your tables are, but see no reason why these can't be combined into 1 table.

    Plus, depending on what you are doing you might want to look into creating a summary table(s).

    posibilities are endless.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Combining into 1 table has several potential drawbacks like:
    - you can't easily roll-out a single month only
    - you can't easily backup/restore a single month only
    - all data for all 12 months may not even fit into a single table
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Thus are all valid points to stay away from 1 table, but there are just as many good points to put it on 1 table.

    JJ is the only one at this point that can figure this out, sinse we do not have any more information available to us now.

    I did want to say Thank you to Marcus for providing those links. Very good reading material. Keep on posting.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •