• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Fetching Multiple Records Through Functions

 
Ranch Hand
Posts: 205
Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..


Regards,


John
reply
    Bookmark Topic Watch Topic
  • New Topic