Home » SQL & PL/SQL » SQL & PL/SQL » Calling a funcion in SQL Select stmt
Calling a funcion in SQL Select stmt [message #421296] Mon, 07 September 2009 06:16 Go to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi,

I have a function, which intruns select a SEQUENCE.NEXTVAL and returns the NEXTVAL number.

Note: I am using the function bcoz, the sequence needs to be selected based on the context set.

I am calling this function in my SQL SELECT stmt and the output iam getting is.

> SELECT name, (select myfn() from dual) from tab1;

Name Level
------ ---------
AAA 1
BBB 1
CCC 1

but the expected output is


Name Level
------ ---------
AAA 1
BBB 2
CCC 3

Kindly help.
Re: Calling a funcion in SQL Select stmt [message #421299 is a reply to message #421296] Mon, 07 September 2009 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select name, rownum from tab1;

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Mon, 07 September 2009 06:22]

Report message to a moderator

Re: Calling a funcion in SQL Select stmt [message #421302 is a reply to message #421296] Mon, 07 September 2009 06:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is the code in your function?
Re: Calling a funcion in SQL Select stmt [message #421303 is a reply to message #421302] Mon, 07 September 2009 06:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, why
SELECT name, (select myfn() from dual) from tab1

?

You could just use
SELECT name, myfn() from tab1

(Unless the procedure itself is broken)
Re: Calling a funcion in SQL Select stmt [message #421304 is a reply to message #421296] Mon, 07 September 2009 06:55 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Thanks Thomas Smile I got it..
Re: Calling a funcion in SQL Select stmt [message #421369 is a reply to message #421296] Mon, 07 September 2009 21:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK so how about telling us so we can get it. What goood is this forum if you do not share the solutions you find to your problems with others?

Kevin
Re: Calling a funcion in SQL Select stmt [message #421416 is a reply to message #421296] Tue, 08 September 2009 03:48 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
The select from dual would have caused the function to only be run once per query rather than once per row.
Re: Calling a funcion in SQL Select stmt [message #421483 is a reply to message #421296] Tue, 08 September 2009 12:17 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
thanks
Previous Topic: passing FILE ID in PL SQL
Next Topic: on check constraints...
Goto Forum:
  


Current Time: Fri Dec 09 02:07:21 CST 2016

Total time taken to generate the page: 0.21322 seconds