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?
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 ChongCommented:
is the description stored in a table? if yes, why not directly joining the tables in your Select SQL statement instead?
0
chalie001Author Commented:
no is not in table the the function call other table based on the accno pased then i get the discription
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

You can call the function in select statement like this

Select *,dbo.GET_acc_DESC (par_accno) from table
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
0
Vikas GargBusiness Intelligence DeveloperCommented:
You can also use the function in where clause


select accno from emp n,dept d
where get_acc_desc(dp.depno) = d.depno;
0
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

0

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
awking00Commented:
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.
0
chalie001Author Commented:
correct
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.