The right outer join isn't going to give you the pipe delimited list you want.
Here's the function in Oracle's code - maybe someone can translate to
sql server.
CREATE OR REPLACE function join
(
p_cursor sys_refcursor,
p_del varchar2 := ','
) return varchar2
is
l_value varchar2(32767);
l_result varchar2(32767);
begin
loop
fetch p_cursor into l_value;
exit when p_cursor%notfound;
if l_result is not null then
l_result := l_result || p_del;
end if;
l_result := l_result || l_value;
end loop;
return l_result;
end join;
/
And here's an example of how I call it:
SELECT p.problemid, p.latinname, p.commonname, p.damage, p.description,
p.culturalcontrol, t.problemtype,
t.problemtypeid,
join(cursor(select locationid from
problemlocation WHERE
problemid = p.problemid
order by locationid)) locations,
join(cursor(select timeperiodid from
problemtimeperiod WHERE
problemid = p.problemid
order by timeperiodid)) timeperiods
FROM problem p INNER JOIN problemtype t ON p.problemtypeid =
t.problemtypeid
(By default this uses a comma as delimiter, but you can set it to use a pipe.)
On 10/5/07, Erika L. Walker wrote:
> I prefer to ....
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Get involved in the latest ColdFusion discussions, product
development sharing, and articles on the Adobe Labs wiki.
http://labs/adobe.com/wiki/index.php/ColdFusion_8
Archive:
http://www.houseoffusion.com/groups/CF-Community/message.cfm/messageid:243663
Subscription: http://www.houseoffusion.com/groups/CF-Community/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.5
