| Creating view with grouping function [message #151895] |
Mon, 19 December 2005 13:00  |
sikorsky05
Messages: 40
Registered: October 2005
|
Member |
|
|
I have 17 tables in schema GEN_ARC. I want to be able to display the names of the tables along with Row Count based on certain criteria. Is it possible to do this with a view...
Below is a list of 5 tables..
ALLOCATION
ALLOCATION_IPTSUM
ALLOCATION_NEW
ALLOCATION_NEW_IPTSUM
ARCHIVE_CONTROL
I am trying to figure out how I can list the tables in schema GEN_ARC along with the # of rows in each table that have data pertaining to Model 'S92'.
This is what the output should look like
Table Name Row Count
ALLOCATION 500
ALLOCATION_IPTSUM 259
ALLOCATION_NEW 129
ALLOCATION_NEW_IPTSUM 59
ARCHIVE_CONTROL 33
Row count is based on user choosing Model 'S92'. Each table contains a Model column. How can I do this w/out having 17 select statements then writing all this to a temporary table.
I know I can create Select count(*) from table_name, but don't want to do this 17 times then store that information in a table. Is there an easier way to do this .
Thanks
|
|
|
|
| Re: Creating view with grouping function [message #151902 is a reply to message #151895] |
Mon, 19 December 2005 14:09  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm not sure how you would avoid the initial 17 select statements because you've got to actually count how many records fulfil your criteria. But, as for the temp table thing, you typically don't need or want to do that in oracle. For instance, you could just union those 17 select statements together in a larger select statement to get your final result.
Now, on the other hand, if you just wanted the total count from each table, you could get that info in one simple query from the all_tables view, assuming you had statistics correctly gathered.
Also, you don't have to manually type in 17 queries, you could write a select statement to generate them for you, or you could use dynamic sql to do it as part of plsql. But it may be simpler to just use copy and paste in this case.
|
|
|
|
| Re: Creating view with grouping function [message #151960 is a reply to message #151902] |
Tue, 20 December 2005 02:03 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Here's some code I implemented to solve a similar problem.
CREATE OR REPLACE FUNCTION rowcount (
pTab IN VARCHAR2
, pWhere IN VARCHAR2 := NULL
) RETURN NUMBER AS
lRet NUMBER;
--
TYPE CurType IS REF CURSOR;
C1 CurType;
BEGIN
-- Construct and execute the SQL:
-- SELECT count(*) FROM tab [WHERE clause]
--
OPEN C1 FOR 'select count(*) from ' || pTab || ' ' || pWhere;
FETCH C1 INTO lRet;
CLOSE C1;
--
return(lRet);
END rowcount;
select rowcount(table_name) from user_tables;
_____________
Ross Leishman
|
|
|
|
| Re: Creating view with grouping function [message #152024 is a reply to message #151895] |
Tue, 20 December 2005 09:43 |
sikorsky05
Messages: 40
Registered: October 2005
|
Member |
|
|
I got an error when trying to compile the code you provided me..
It give me an error @
OPEN C1 FOR 'select count(*) from '|| var || ' ' ||;
FETCH C1 INTO lRet;
CLOSE C1;
Error I get is Encountered the symbol "Select count(*) from "
when expecting one of the following
Select
The symbol "Select" was substituted for "Select count(*) from"
to continue
Thanks for the info
|
|
|
|
| Re: Creating view with grouping function [message #152031 is a reply to message #151895] |
Tue, 20 December 2005 10:02 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
For syntax errors it is best to post your whole session, showing the code you entered exactly as you entered it, and the exact results produced. Also, what version are you running?
|
|
|
|
| Re: Creating view with grouping function [message #152071 is a reply to message #151895] |
Tue, 20 December 2005 17:37 |
skgoel
Messages: 16
Registered: November 2005
|
Junior Member |
|
|
Try this procedure:
create or replace procedure get_rowcnt
IS
v_cnt number;
cursor c1 is
select table_name from user_tables;
v_str varchar2(1000);
begin
for rc in c1 loop
v_str := 'SELECT count(*) from ' || rc.table_name || ' where model_val = ''S92'' ';
execute immediate v_str into v_cnt;
dbms_output.put_line('Number of rows in table ' || rc.table_name || ' = ' || v_cnt);
end loop;
end get_rowcnt;
You can modify the procedure to accept input parameters for table names, model_col values etc.
Shakti
http://www.impact-sol.com
Developers of Guggi Oracle - Tool for DBAs and Developers
|
|
|
|
| Re: Creating view with grouping function [message #152146 is a reply to message #151895] |
Wed, 21 December 2005 07:03 |
sikorsky05
Messages: 40
Registered: October 2005
|
Member |
|
|
I got that to work.. Now I have another problem ..
This was the output I got
Number of rows in table ALLOCATION = 897
Number of rows in table ALLOCATION_IPTSUM = 5
Number of rows in table ALLOCATION_NEW = 922
Number of rows in table ALLOCATION_NEW_IPTSUM = 0
Number of rows in table ARCHIVE_CONTROL = 23
Now each Table has a Model, group_code and Archive # column.
How do I set it up so the procedure will only give me
model = 'S92'
group_code = 'PROD'
and archive_number = 0
The whole idea is to have a list of available Model,GP,& Arch # in a form LOV and then once the user higlights what they want the next screen will display the row counts of each table pertaining to those 3 criteria u user selects in the previous screen.
Thanks for all the help ...
|
|
|
|
| Re: Creating view with grouping function [message #152268 is a reply to message #152146] |
Thu, 22 December 2005 01:26 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Take another look at my function; it does what you want. The syntax is correct - just paste it straight into SQL*Plus.
select table_name, rowcount(table_name, 'WHERE model = ''S92'' AND group_code = ''PROD'' AND and archive_number = 0')
from user_tables
where table_name like ....
_____________
Ross Leishman
|
|
|
|
| Re: Creating view with grouping function [message #152345 is a reply to message #151895] |
Thu, 22 December 2005 11:49 |
sikorsky05
Messages: 40
Registered: October 2005
|
Member |
|
|
How can I implement this procedure in a Form Program Unit
I get an error on execute immediate
create or replace procedure get_rowcnt
IS
v_cnt number;
cursor c1 is
select table_name from user_tables;
v_str varchar2(1000);
begin
for rc in c1 loop
v_str := 'SELECT count(*) from ' || rc.table_name || ' where model_val = ''S92'' ';
execute immediate v_str into v_cnt;
dbms_output.put_line('Number of rows in table ' || rc.table_name || ' = ' || v_cnt);
end loop;
end get_rowcnt;
|
|
|
|
|
|
|
|
|
|
| Re: Creating view with grouping function [message #152499 is a reply to message #151895] |
Fri, 23 December 2005 08:47 |
sikorsky05
Messages: 40
Registered: October 2005
|
Member |
|
|
I got the same error
execute immediate 'v_str';
I read else where Execute immediate doesn't work in forms since it has to be done on server side so I found out u have to use
v_str := 'SELECT count(*) from ' || rc.table_name;
vcnt := dbms_sql.execute(v_str);
But then this give me an
FRM-40735 When button pressed trigger raised
ORA-06502.. using
Forms [32 Bit] Version 6.0.8.21.3 (Production
Any suggestions
|
|
|
|
|
|
| Re: Creating view with grouping function [message #153656 is a reply to message #152268] |
Tue, 03 January 2006 10:17 |
sikorsky05
Messages: 40
Registered: October 2005
|
Member |
|
|
I tried what you told me to I get this error
Ran
select table_name, rowcount(table_name, 'WHERE model = ''S92'' AND group_code = ''PROD'' AND and archive_number = 0')
from user_tables
where table_name like 'ALLOCATION'
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "SIKA28.ROWCOUNT", line 13
ORA-06512: at line 1
This was the function i created
SQL> CREATE OR REPLACE FUNCTION rowcount (
2 pTab IN VARCHAR2
3 , pWhere IN VARCHAR2 := NULL
4 ) RETURN NUMBER AS
5 lRet NUMBER;
6 --
7 TYPE CurType IS REF CURSOR;
8 C1 CurType;
9 BEGIN
10 -- Construct and execute the SQL:
11 -- SELECT count(*) FROM tab [WHERE clause]
12 --
13 OPEN C1 FOR 'select count(*) from ' || pTab || ' ' || pWhere;
14 FETCH C1 INTO lRet;
15 CLOSE C1;
16 --
17 return(lRet);
18 END rowcount;
19 /
Any suggestions
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
