File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and the fly likes Fetching Multiple Records Through Functions Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Fetching Multiple Records Through Functions" Watch "Fetching Multiple Records Through Functions" New topic

Fetching Multiple Records Through Functions

Eshwar Prasad
Ranch Hand

Joined: Mar 21, 2008
Posts: 202

I have a requirement to find the max of purchase for the user and then find the avg of those max records retrieved. So i created a procedure which joins multiple tables and hence calling the below function for one of those columns.

While executing this part of function in sql prompt, i am getting blank output, although the query when executed outside the function gives 45 records.

Also, please let me know how can i find the avg of all the values from max(purchase) and put that to avgsales variable and return that to the procedure.
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Eshwar,

I think you just forgot something: You never assign a value to variable avgsales.

How do you call your function? The only way this would return 45 rows is if you perform the select with a where clause which would return 45 rows (or without a where clause on a table containing 45 rows), and all of them would be the same, since you have no IN parameters in your function..


I agree. Here's the link:
subject: Fetching Multiple Records Through Functions
Similar Threads
Counting in Array List/ Standard deviation
writing my first stored procedure
question Regarding return DataInfo[] to Jtable
Execute Multiple SQL Queries in PL/SQL Procedure based on parameter