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 -> Re: Help writing SQL query

Re: Help writing SQL query

From: <flatline_at_mailbox.hu>
Date: 27 Nov 2002 04:08:24 -0800
Message-ID: <bc3a43ba.0211270408.6797375a@posting.google.com>


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,

  SHAPEID NUMBER ) ; CREATE TABLE TMP_WORK (
  W_UID    NUMBER,
  W_QUERY  NUMBER,
  W_PATH   NUMBER,
  W_NODE   NUMBER,

  SUBRNK NUMBER ) ; create sequence sq_w_path nocache;

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

sq_w_uid.nextval,w_query,inspath,w_node from (select * from waypoints where w_path=wpath and w_uid<>fnewnode order by w_uid));
	   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;

   end loop;
   close nxt;
   return i;
end;
end tst_nodes;
/

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));

   end loop;
   return z;
end;
end tst_begin;
/

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;
   loop
      fetch pts into prec;
	  exit when pts%NOTFOUND;
      z2:=tst_begin(prec.node);

   end loop;
   EXECUTE IMMEDIATE 'insert into shapes    select w_query,w_path,
   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,
   0 SOLID,
   0 SHAPEID
   from
(select g.*,

   dense_rank() over (partition by g.w_path order by g.w_node) RN    ,dense_rank() over (partition by g.w_path,g.w_node order by subrnk) DR

   from tmp_work g)
   group by w_query,w_path,dr';

Received on Wed Nov 27 2002 - 06:08:24 CST

Original text of this message

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