Sql Query [message #306201] |
Thu, 13 March 2008 04:59  |
jhedshi
Messages: 13 Registered: February 2008
|
Junior Member |
|
|
the query that i wish to write is as follows :
i want to parameterise the column name , table name, value
and then retrieve the no of rows for that column, table and that value.
Example Query : select ename from emp where empno = 1;
here the count returned will be = 1. but there can be more employees by the empno = 1. this is the requirement.
so how can i write a parameterised function to parameterise 'ename' , 'emp'
and 'empno' and return the number of rows for the parameters entered.
please help..
regards.
|
|
|
|
Parameterised Function [message #306218 is a reply to message #306206] |
Thu, 13 March 2008 05:48   |
jhedshi
Messages: 13 Registered: February 2008
|
Junior Member |
|
|
the Function that i wish to write is as follows :
i want to parameterise the column name , table name, value
and then retrieve the no of rows for that column, table and that value.
Example Query : select ename from emp where empno = 1;
here the count returned will be = 1. but there can be more employees by the empno = 1. this is the requirement.
so how can i write a parameterised function to parameterise 'ename' , 'emp'
and 'empno' and return the number of rows for the parameters entered.
please help..
regards.
|
|
|
|
|
Re: Sql Query [message #306240 is a reply to message #306223] |
Thu, 13 March 2008 06:59   |
jhedshi
Messages: 13 Registered: February 2008
|
Junior Member |
|
|
this is what i tried but its futile.
i want to retrieve the number of rows for the table passed, column passed and the value passed :
create or replace function test
( tblnm IN varchar2 , colnm in varchar2 , val in number , cnt out number)
return number
is
declare
cursor c1 is
select count(*), colnm --colnm is the parameter
from tblnm -- tblnm is the parameter
where val = 1; -- val is the parameter
begin
open c1;
fetch c1 into cnt; --cnt is the out parameter
close c1;
return cnt;
end;
end test;
kindly guide
regards
|
|
|
|
Re: Sql Query [message #306250 is a reply to message #306244] |
Thu, 13 March 2008 07:49   |
jhedshi
Messages: 13 Registered: February 2008
|
Junior Member |
|
|
right michel i have tried this :
create or replace function count_in_table
(colnm in varchar2, val in varchar2, tbl in varchar2)
return number
is
cnt number;
begin
execute immediate 'select count(1) from ' || tbl || ' where ' || colnm || ' = :a' into cnt us
al;
return cnt;
end;
/
Function created
----------------------
select distinct count_in_table('JOB','ANALYST','emp') countjob from emp
/
---------------------
OUTPUT :
COUNTJOB
---------
2
this is what i exactly need.
PLEASE tell me is there any other way to execute this function other than using in SQL. for example : taking out in bind variable and then displaying
please help. Apologies for previous postings.
Regards
|
|
|
|
|
Re: Sql Query [message #306380 is a reply to message #306252] |
Fri, 14 March 2008 00:06   |
jhedshi
Messages: 13 Registered: February 2008
|
Junior Member |
|
|
just one last thing . michel
is it possible to call this function from another language .. for example we are supposed to use this in a cobol program.
if we can any ideas as to how ??
please help..
regards..
|
|
|
|