Header Detail display on single line. 1

[landolakes]

I've posted and received a nice solution to an issue I had with SQL Server (see thread thread183-1190923), however now I'm faced with the same issue on Oracle 9i. Here is a summary, the solution for SQL Server, and my attempt in Oracle. Seems like it should work.....

/* I'm trying to get output from a header and detail to display 
all of the detail records on the same row as the header record.
The code below is a simplified version of what I'm going for here.

Any assistance would be greatly appreciated.  I've come up with some horribly complex
selects within selects etc., but there must be a better way, I'm just
drawing a blank it seems.  Thanks.
*/

-- build sample test tables
create table tmp_header (
nbr int primary key,
name varchar(20) null);

insert into tmp_header (nbr,name) values (1001,'joe');
insert into tmp_header (nbr,name) values (1002,'bob');
insert into tmp_header (nbr,name) values (1003,'jim');


create table tmp_detail (
hdrnbr int not null,
cert int not null);

insert into tmp_detail (hdrnbr,cert) values (1001,900);
insert into tmp_detail (hdrnbr,cert) values (1001,901);
insert into tmp_detail (hdrnbr,cert) values (1001,902);
insert into tmp_detail (hdrnbr,cert) values (1002,900);
insert into tmp_detail (hdrnbr,cert) values (1002,903);
insert into tmp_detail (hdrnbr,cert) values (1003,900);
insert into tmp_detail (hdrnbr,cert) values (1003,904);


create table tmp_cert (
cert int  primary key,
title varchar(20) null);

insert into tmp_cert (cert,title) values (900,'TI');
insert into tmp_cert (cert,title) values (901,'BA');
insert into tmp_cert (cert,title) values (902,'OC');
insert into tmp_cert (cert,title) values (903,'BK');
insert into tmp_cert (cert,title) values (904,'LV');

The following SQL Server function works like a charm...

/* SQLServer function  */
create function gettmp_headerCerts( @nbr int )
returns varchar( 100 )
as
begin
    declare @ret varchar( 100 )
    select @ret = coalesce(@ret + ', ', '') + C.title
        from tmp_detail D
        inner join tmp_cert C on D.cert = C.cert
        where D.hdrnbr = @nbr
        order by D.cert

    return @ret
end;

I converted it to Oracle, and I get nothing...

/* Oracle function  */
create or replace function gettmp_headerCerts( nbr IN number )
return varchar2
is retval varchar2(100);

begin
    select coalesce(retval || ', ', '') || C.title into retval
        from tmp_detail D
        inner join tmp_cert C on D.cert = C.cert
        where D.hdrnbr = nbr
        order by D.cert;

    return (retval);
end gettmp_headerCerts;
/

Here are the Selects and what they should return:

-- old way
select
	hdr.nbr,
	hdr.name,
	crt.title
from
	tmp_header hdr
	join tmp_detail dtl on hdr.nbr = dtl.hdrnbr
	join tmp_cert crt on dtl.cert = crt.cert;

/* Here is what I don't want
nbr	name	cert
1001	joe	TI
1001	joe	BA
1001	joe	OC
1002	bob	TI
1002	bob	BK
1003	jim	TI
1003	jim	LV
*/


------   new way
select hdr.nbr, hdr.name, gettmp_headerCerts(hdr.nbr) as "certs"
from tmp_header hdr
group by hdr.nbr, hdr.name;

/* This is the output I'm trying to get to, or at least something similar:
nbr	name	cert
1001	joe	TI,BA,OC
1002	bob	TI,BK
1003	jim	TI,LV

*/

There must be some difference in the way functions are handled in the two DBMS's? Don't know. Any assistance would be certainly appreciated. Thanks.

 

[carp]

What happens if you eliminate the GROUP BY clause?

 

[SantaMufasa]

Landolakes,

Here is a simplified version of both your function and your SELECT that produces what you want (sans the error, "RA-01422: exact fetch returns more than requested number of rows"):

create or replace function gettmp_headerCerts( nbr IN number )
return varchar2
is retval varchar2(100);

begin
    for x in (select title from tmp_detail d, tmp_cert c
               where d.hdrnbr = nbr and c.cert = d.cert) loop
        retval := retval||','||x.title;
    end loop;
    return ltrim(retval,',');
end gettmp_headerCerts;
/

Function created.

col certs format a10
select hdr.nbr, hdr.name, gettmp_headerCerts(hdr.nbr) as "certs"
from tmp_header hdr;

 NBR NAME                 certs
---- -------------------- ----------
1001 joe                  TI,BA,OC
1002 bob                  TI,BK
1003 jim                  TI,LV

3 rows selected.

Let us know if this is satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[landolakes]

Removing "group by" produces same results, that is "no rows returned".

I'll try Mufasa's code after lunch. Thanks both.

 

[landolakes]

Works nice. Should do the trick. Thanks again!