Re: Counting from several tables

From: J.P. <jp_boileau_at_yahoo.com>
Date: 4 Jun 2002 05:29:48 -0700
Message-ID: <7e388bc3.0206040429.6cb82e00@posting.google.com>

Here's a SQL script that does essentially what you are looking.

It gets all of the table names from the USER_TABLES table, and runs your query against each of those tables. It stores the result in the table myresulttable.

If you only want to analyze a few tables, use a custom table instead of the USER_TABLES table. You could then also specify which column that you want to look at (e.g. logdate, etc).

DECLARE CURSOR MYC IS
   SELECT
      table_name
   FROM
      user_tables
   cid INTEGER;
BEGIN
   FOR myrec IN myc LOOP

      cid := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cid, 
         'INSERT INTO myresulttable (' ||
         '   SELECT ' || myrec.table_name ||
         ', TO_CHAR(logdate, ''MMDDYY''), ' ||
         '      COUNT(*) FROM ' || myrec.table_name '||
         '   GROUP BY TO_CHAR(logdate, ''MMDDYY'')) ',
         dbms_sql.v7);
      DBMS_SQL.CLOSE_CURSOR(cid);
      END LOOP;

END; I hope this helps.

I haven't tried the code, but it should be pretty close to what you need.

All it is, is essentially on-the-fly dynamic SQL created from a table.

JP

"Keith C. Jakobs, MCP" <elohir_at_hotmail.com> wrote in message news:<%kTK8.16136$153.271725735_at_newssvr14.news.prodigy.com>...
> Greetings:
>
> I am trying to make a SQL script that will tell me the the number of records
> across several tables, listed by date.
>
> I would like my row data to show a range of dates, and the count of records
> for each table as my columns, though I will transpose row and columns if
> necessary (Can I use row data for column headings)?
>
> I know how to format my date comparisons, and I can do this script perfectly
> for one table using the script below, but I have no idea how to make one
> script, that will tally the count of all tables, in one output table.
>
> SELECT TO_CHAR(logdate, 'MMDDYY') AS "Date", count(*) AS "Table1"
> FROM table1
> GROUP BY "Date";
>
> I need the counts of Table2, Table3, etc. to also show up in this table.
>
> Can anyone help?
>
> Thanks in advance,
>
> Keith C. Jakobs, MCP
> elohir_at_hotmail.com
Received on Tue Jun 04 2002 - 07:29:48 CDT