Custom Function Help Needed?

From: gdas <gdas1NOgdSPAM_at_yahoo.com.invalid>
Date: 2000/06/07
Message-ID: <13e4506b.0695a1ed@usw-ex0107-055.remarq.com>#1/1

I am trying to create a function that will essentially "SUM" a string. Unfortunately the built in SUM function works only on numeric datatypes. I can't simply concat || values because the values I'm trying to aggregate aren't within a single row, but rather span multiple rows in a column.

As an example, imagine a query that is:

select region, sum(sales_rep) from foo group by region

I'd like this to return each region and the next cell should be a comma delimited list of all sales_reps in that particular region.

Region sum(sales_rep)
------ --------------

WEST     Joe, Bill, Bob
East     Lisa, Steve, Mary

etc...
I created a custom function that basically fed in the "grouping" parameters (Region) and then that function loops through a cursor to return the appropriate value. no problems there.

Only problem is that everything needs to be hardcoded in the function, particularly the input parameters. I'd like to simulate a dynamic group by here based on whatever other columns are in the select list for the query? Does anyone have a creative idea on this?

One idea I had (if this is possible) was that if I could dynamically send as a parameter to my custom function the full text of the select statement...then inside of the function, I could parse that statement to determine the appropriate level of grouping. But in order to do this (again, it has to be dynamic because I can't hard code the statement as I would like to use this function in different scenarios), I need some kind of keyword or variable that will allow me to feed in the sql statement as a parameter to the function) Additionally, the application is end-user driven...so I have no idea what the users will be querying (it's a decision support app where people will be drilling up/down and all around) .

If anyone has any creative ideas to this dilemma, I'd very much appreciate it if you could share your thoughts.

Thanks in advance,
Gavin

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Wed Jun 07 2000 - 00:00:00 CDT