Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> PL/SQL Problem [One Row Retuns]

PL/SQL Problem [One Row Retuns]

From: Solidnull, Inc. <andrew_at_solidnull.com>
Date: Wed, 21 Nov 2001 11:43:13 -0000
Message-ID: <3bfb94aa$1@motown>


I do have a script that returns an intersection between two lines and it works fine, but when i add another line and run my sql statement against it, the script returns only returns one row. Any advice on how to solve this problem???
Thnx

Function looks like this:

static function func_intersect(fld_joint1 tp_joint,fld_joint2 tp_joint,fld_joint3 tp_joint,fld_joint4 tp_joint) return tp_point is

xc number; yc number;
r1 number; r2 number;
s1 number; s2 number;
t1 number; t2 number;
x1 number; y1 number;
x2 number; y2 number;
x3 number; y3 number;
x4 number; y4 number;

xn boolean; yn boolean;
var_point tp_point;
begin
x1:= fld_joint1.func_point_x(); y1:= fld_joint1.func_point_y();
x2:= fld_joint2.func_point_x(); y2:= fld_joint2.func_point_y();
x3:= fld_joint3.func_point_x(); y3:= fld_joint3.func_point_y();
x4:= fld_joint4.func_point_x(); y4:= fld_joint4.func_point_y();

r1:= (y2 - y1); r2:= (y4 - y3);

s1:= -(x2 - x1); s2:= -(x4 - x3);
t1:= (x2 * y1) - (x1 * y2); t2:= (x4 * y3) - (x3 * y4);

if ((s1 * t2) - (s2 * t1)) = 0 then
return null;
else
xc:= ((s1 * t2) - (s2 * t1)) / ((s2 * r1) - (s1 * r2)); yc:= ((t1 * r2) - (t2 * r1)) / ((s2 * r1) - (s1 * r2)); end if;

xn:= ((xc >= Least(x1,x2)) and (xc <= Greatest(x1,x2)) and (xc >=
Least(x3,x4)) and (xc <= Greatest(x3,x4)));
yn:= ((yc >= Least(y1,y2)) and (yc <= Greatest(y1,y2)) and (yc >=
Least(x3,x4)) and (yc <= Greatest(x3,x4)));

if (xn and yn) then
var_point:= tp_point(xc,yc);
return var_point;
else
return null;
end if;
end;

SQL Statement looks like this:

select fld_id, p1.fld_joint, p2.fld_joint, p3.fld_joint, p4.fld_joint from tbl_pipe p1, tbl_pipe p2, tbl_pipe p3, tbl_pipe p4 where p1. func_jointid()+1 = p2. func_jointid()

and p3. func_jointid()+1 = p4. func_jointid()
and p1.fld_id = p2.fld_id
and p3.fld_id = p4.fld_id
and p1.fld_id > p3.fld_id

and
tp_joint.func_intersect(p1.fld_joint,p2.fld_joint,p3.fld_joint,p4.fld_joint) IS NOT NULL; Received on Wed Nov 21 2001 - 05:43:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US