EXECUTE FUNCTION in select statement

hi how can i call a function  in select statement

for example

create or replace function GET_acc_DESC (par_accno    in   number)
  RETURN VARCHAR2
 
 
  select accno,--call function for the selected accno to desiplay discription from employyeee
chalie001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
is the description stored in a table? if yes, why not directly joining the tables in your Select SQL statement instead?
chalie001Author Commented:
no is not in table the the function call other table based on the accno pased then i get the discription
Vikas GargAssociate Principal EngineerCommented:
Hello,

You can call the function in select statement like this

Select *,dbo.GET_acc_DESC (par_accno) from table
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

chalie001Author Commented:
ok lets say you use deparno to get accno desc but you don't what to select departno

e.g

select accno,get_acc_desc(dp.depno) from emp n,dept d
where get_acc_desc(dp.depno) = d.depno;

i what to use get_acc_desc(dp.depno) in where clues
Vikas GargAssociate Principal EngineerCommented:
You can also use the function in where clause


select accno from emp n,dept d
where get_acc_desc(dp.depno) = d.depno;
chalie001Author Commented:
am geting ORA-01722: invalid number

this is the function
create or replace function hr.accno (par_accno in number)
  RETURN VARCHAR2
  IS
    accno  varchar2(16);
	begin
	ws_acno		number(11);
    ws_accvalu        	varchar2(13);
	 ws_err_ind		varchar2(1);
	 begin
	  ws_acno := par_accno;
	  
	  accvalidation(ws_sin, ws_accvalu,ws_err_ind);
	  
	  
	
	IF ws_err_ind = 'Y' then
        accno := null;
     ELSE
        accno := substr(ws_accvalu,1,4)||'-'||
               substr(ws_accvalu,5,2)||'-'||
               substr(ws_accvalu,7,3)||'-'||
               substr(ws_accvalu,10,4);
     END IF;
   END;
 RETURN(accno);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
Does that function compile? It look to me like all you're wanting is to take a number with 13 places and convert it into a string with a dash between the 4th and 5th digits, the 6th and 7th digits, and the 9th and 10th digits. If that's all you want, I don't know why you have those variables and that function call in your code. Since the variables never get initialized, how would the function know, for example, if the value of ws_err_ind is 'Y' or not? Perhaps you can provide some sample data from the table and the results you would like to retrieve from that sample.
chalie001Author Commented:
correct
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.