Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help writing SQL query
Hello! First of all, sorry for my bad english.... I think I've managed
to solve your problem, it was too interesting to just leave there.
This solution involves 3 stored procs and 3 temp tables,and can
analyze a shape and find n-agons up to 20 (can be modified, if you
need more)
Assumptions made:
- no connection is defined twice (better check it out!)
If you are interested in the theory behind it, mail me, or ask here...
Oracle 8.1.7 required
The code is far away from being elegant, or neat, but it works...
You can start the process with an anon pl/sql :block;
begin
declare
z number;
begin
z:=analyze_shape(300);
end;
end;
...and query the shapes table for the results...
w_query,w_path: doesn't matter
n1-n20 : nodes in increasing order
nodes: (3 means triangle, 4 rectangle)
Hope this helps you out, 'cos I was working with it for a few hours.... ;)
Test it a few times with different shapes...
Byez, Flatline
CREATE TABLE WAYPOINTS (
W_UID NUMBER, W_QUERY NUMBER, W_PATH NUMBER, W_NODE NUMBER ) ;
CREATE TABLE SHAPES (
W_QUERY NUMBER, W_PATH NUMBER, N1 NUMBER, N2 NUMBER, N3 NUMBER, N4 NUMBER, N5 NUMBER, N6 NUMBER, N7 NUMBER, N8 NUMBER, N9 NUMBER, N10 NUMBER, N11 NUMBER, N12 NUMBER, N13 NUMBER, N14 NUMBER, N15 NUMBER, N16 NUMBER, N17 NUMBER, N18 NUMBER, N19 NUMBER, N20 NUMBER, NODES NUMBER, SOLID NUMBER,
W_UID NUMBER, W_QUERY NUMBER, W_PATH NUMBER, W_NODE NUMBER,
create sequence sq_w_query nocache;
create sequence sq_w_uid nocache;
create sequence sq_multi;
CREATE OR REPLACE function tst_nodes(wpath number, wuid number)
return number is
begin
declare
cursor nxt is
select * from tblshapes where (node=(select w_node from waypoints where w_uid=wuid) and nextnode_linkedlist not in (select w_node from waypoints where w_uid<>wuid and w_path=wpath)) or (nextnode_linkedlist=(select w_node from waypoints where w_uid=wuid) and node not in (select w_node from waypoints where w_uid<>wuid and w_path=wpath));
nrec nxt%ROWTYPE;
i number;
nnode number;
oldnode number;
inspath number;
fnewnode number;
begin
i:=0;
select w_node into oldnode from waypoints where w_uid=wuid;
inspath:=wpath;
open nxt;
loop
fetch nxt into nrec; exit when nxt%notfound; if (i>0) then -- duplicate path select sq_w_path.nextval into inspath from dual; insert into waypoints (select
end if; i:=i+1; nnode:=nrec.node; if (nrec.node=oldnode) then nnode:=nrec.nextnode_linkedlist; end if; insert into waypoints values (sq_w_uid.nextval,sq_w_query.currval,inspath,nnode); if (i=1) then -- remember first new node select sq_w_uid.currval into fnewnode from dual; end if;
CREATE OR REPLACE function tst_begin(wstart number)
return number is
begin
declare
query number;
cursor paths is select w_path,max(w_uid) LAST from waypoints where
w_query=query group by w_path;
prec paths%ROWTYPE;
i number;
z number;
begin
insert into waypoints values
(sq_w_uid.nextval,sq_w_query.nextval,sq_w_path.nextval,wstart);
select sq_w_query.currval into query from dual;
i:=0;
loop
z:=0; open paths; loop fetch paths into prec; exit when paths%notfound; z:=z+tst_nodes(prec.w_path, prec.last); end loop; close paths; i:=i+1; exit when ((z=0) or (i=10));
CREATE OR REPLACE function analyze_shape(shape_id number)
return number is
begin
declare
cursor pts is select * from tblshapes where shapeid=shape_id;
prec pts%ROWTYPE;
z number;
z2 number;
begin
z:=0;
EXECUTE IMMEDIATE 'truncate table waypoints'; EXECUTE IMMEDIATE 'truncate table shapes'; EXECUTE IMMEDIATE 'truncate table tmp_work';open pts;
fetch pts into prec; exit when pts%NOTFOUND; z2:=tst_begin(prec.node);
max(case when rn=1 then w_node else 0 end) N1, max(case when rn=2 then w_node else 0 end) N2, max(case when rn=3 then w_node else 0 end) N3, max(case when rn=4 then w_node else 0 end) N4, max(case when rn=5 then w_node else 0 end) N5, max(case when rn=6 then w_node else 0 end) N6, max(case when rn=7 then w_node else 0 end) N7, max(case when rn=8 then w_node else 0 end) N8, max(case when rn=9 then w_node else 0 end) N9, max(case when rn=10 then w_node else 0 end) N10, max(case when rn=11 then w_node else 0 end) N11, max(case when rn=12 then w_node else 0 end) N12, max(case when rn=13 then w_node else 0 end) N13, max(case when rn=14 then w_node else 0 end) N14, max(case when rn=15 then w_node else 0 end) N15, max(case when rn=16 then w_node else 0 end) N16, max(case when rn=17 then w_node else 0 end) N17, max(case when rn=18 then w_node else 0 end) N18, max(case when rn=19 then w_node else 0 end) N19, max(case when rn=20 then w_node else 0 end) N20,0 NODES,
from tmp_work g)
group by w_query,w_path,dr';