Re: Pl/SQL Problem [One Row Returns]

"Solidnull, Inc." <andrew_at_solidnull.com> wrote in message news:<3bfb93f2$1_at_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;
I confess that 1- my math is very rusty and 2- I did not study the process above that close, but the thought occurred to me that since you are comparing the numeric decimal results of calculations that perhaps you need to either round your results in a uniform manner or perform some type of value range comapre ( x > -.0005 and x < .0005 means x = 0 ).

Perhaps someone with a stronger math background or more time will give you a better response but I always had to perform rounding/compares with FORTRAN code to get proper results so I thought I would mention the idea.

• Mark D Powell --