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.
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..
subject: Fetching Multiple Records Through Functions