Home » SQL & PL/SQL » SQL & PL/SQL » Sql Query (9i)
Sql Query [message #306201] Thu, 13 March 2008 04:59 Go to next message
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.
Re: Sql Query [message #306206 is a reply to message #306201] Thu, 13 March 2008 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use PL/SQL and dynamic SQL.
Post what you already tried and where you are stuck.
Also search, this kind of question has been asked.
Of course, it will be hard to find it with only the title of topics as the other are like you they don't any effort in choosing a good title.
So your answer is somewhere in a topic named "SQL query" or "SQL help" or "need a query" or the like.

Regards
Michel
Parameterised Function [message #306218 is a reply to message #306206] Thu, 13 March 2008 05:48 Go to previous messageGo to next message
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: Parameterised Function [message #306221 is a reply to message #306218] Thu, 13 March 2008 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Stop multiposting in every forums and "PMing" everybody.

Regards
Michel
Re: Sql Query [message #306223 is a reply to message #306201] Thu, 13 March 2008 06:12 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
how many times you will post same thing?

reply in the way michel suggested.

regards,
Re: Sql Query [message #306240 is a reply to message #306223] Thu, 13 March 2008 06:59 Go to previous messageGo to next message
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 #306244 is a reply to message #306240] Thu, 13 March 2008 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next step use dynamic SQL and no more static cursor.
I think if you search for "dynamic sql" or "execute immediate" you will get many answers.

Regards
Michel
Re: Sql Query [message #306250 is a reply to message #306244] Thu, 13 March 2008 07:49 Go to previous messageGo to next message
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 #306252 is a reply to message #306250] Thu, 13 March 2008 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use something like (SQL*Plus syntax):
exec :cnt := count_in_table...

Also please read OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel
Re: Sql Query [message #306254 is a reply to message #306252] Thu, 13 March 2008 08:16 Go to previous messageGo to next message
jhedshi
Messages: 13
Registered: February 2008
Junior Member
real nice.. thanks michel
Re: Sql Query [message #306380 is a reply to message #306252] Fri, 14 March 2008 00:06 Go to previous messageGo to next message
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..
Re: Sql Query [message #306386 is a reply to message #306380] Fri, 14 March 2008 00:48 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, use Pro*Cobol for instance.

Regards
Michel
Previous Topic: is this a bug? (about analytic function) and "order by" in partitioning clause (merged)
Next Topic: creating query?
Goto Forum:
  


Current Time: Sun Dec 11 02:02:10 CST 2016

Total time taken to generate the page: 0.04568 seconds