| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Custom Function Help Needed?
In article <13e4506b.0695a1ed_at_usw-ex0107-055.remarq.com>,
gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:
> 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!
>
>
I will show 2 more ways to do it by calling PLSQL from sql. The first example uses static sql and hence you would need to code a routine per column/table you wished to "list" on. The second, more generic example, may be used on any table/column pair as it uses dynamic sql but only works in Oracle8i release 8.1 and up:
First we will set up a demo table. We have 2 columns A and B. We desire output in the format:
A-value1 b-value1 b-value2 b-value3 .... A-value2 b-value1 b-value2 ....
ask_tom_at_OSI1.WORLD> column t format a30
ask_tom_at_OSI1.WORLD> drop table t;
Table dropped.
ask_tom_at_OSI1.WORLD> create table t
2 ( a varchar2(25),
3 b varchar2(25)
4 );
Table created.
ask_tom_at_OSI1.WORLD> insert into t values ( '210','5000' ); ask_tom_at_OSI1.WORLD> insert into t values ( '210','5001' ); ask_tom_at_OSI1.WORLD> insert into t values ( '210','5002' ); ask_tom_at_OSI1.WORLD> insert into t values ( '220','6001' ); ask_tom_at_OSI1.WORLD> insert into t values ( '220','6002' ); ask_tom_at_OSI1.WORLD> commit;
Commit complete.
Our first implementation simply uses static sql to select all of the values for B from T for a given A and string them together:
ask_tom_at_OSI1.WORLD> create or replace
2 function get_transposed( p_a in varchar2 )
3 return varchar2
4 is
5 l_str varchar2(2000) default null; 6 l_sep varchar2(1) default null; 7 begin 8 for x in ( select b from t where a = p_a ) loop 9 l_str := l_str || l_sep || x.b; 10 l_sep := '-'; 11 end loop; 12 return l_str;
Function created.
ask_tom_at_OSI1.WORLD>
ask_tom_at_OSI1.WORLD> select a, get_transposed( a ) t
2 from t
3 group by a
4 /
A T ------------------------- ------------------------------ 210 5000-5001-5002 220 6001-6002
Our next example is more complex. We will pass in the name of the 'key' column (the column to pivot on), a value for that column, the name of the column to actually select out and string together and finally the table to select from:
ask_tom_at_OSI1.WORLD> create or replace
2 function transpose( p_key_name in varchar2, 3 p_key_val in varchar2, 4 p_other_col_name in varchar2, 5 p_tname in varchar2 )6 return varchar2
8 type rc is ref cursor; 9 l_str varchar2(4000); 10 l_sep varchar2(1); 11 l_val varchar2(4000); 12 13 l_cur rc;
16 open l_cur for 'select '||p_other_col_name||' 17 from '|| p_tname || ' 18 where ' || p_key_name || ' = :x ' 19 using p_key_val; 20 21 loop 22 fetch l_cur into l_val; 23 exit when l_cur%notfound; 24 l_str := l_str || l_sep || l_val; 25 l_sep := '-'; 26 end loop; 27 close l_cur; 28 29 return l_str;
Function created.
ask_tom_at_OSI1.WORLD> ask_tom_at_OSI1.WORLD> REM List the values of "B" for a given value ask_tom_at_OSI1.WORLD> REM of "A" in the table "T" ask_tom_at_OSI1.WORLD> ask_tom_at_OSI1.WORLD> select a, transpose( 'a', a, 'b', 't' ) t2 from t
A T ------------------------- ------------------------------ 210 5000-5001-5002 220 6001-6002
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Wed Jun 07 2000 - 00:00:00 CDT
 |
 |