Re: Help writing SQL query

From: Mike Liu <mike2322_at_hotmail.com>
Date: 18 Nov 2002 12:10:55 -0800
Message-ID: <2262aa8e.0211181210.2825e4a7@posting.google.com>

tonycantara123_at_hotmail.com (Tony Cantara) wrote in message news:<3d0c2563.0211161456.50f73e6c_at_posting.google.com>...
> Hi, I have designed a table that contains the following information
> about some simple shapes
>
> I need a bit of help writing some SQL (Oracle 8i/9i Enterprise). I
> would like to query the database and count, for example, the number of
> shapes with triangles in them. For all the data below this would
> return 2 (1 for the shape that is a triangle and 1 for the shape that
> is a rectangle made up of 2 triangles).
>
> If the design is bad, I would appreciate constructive critism on what
> would be a better design - at this stage nothing is set in concrete
> (initially I thought this would be an easy query to write using a
> CONNECT BY PRIOR construct clause but this doesn't work for
> 'cyclic/loop data' like that below).
> Many thanks
> Tony
>
>
> CREATE TABLE tblshapes(shapeID NUMBER,node NUMBER,nextnode_linkedlist
> NUMBER);
>
> -- RECTANGLE
> -- 1---------2
> -- | |
> -- | |
> -- | |
> -- 4---------3
> --
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (100,1,4);
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (100,2,1);
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (100,3,2);
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (100,4,3);
>
>
> -- TRIANGLE
> -- 10
> -- |\
> -- | \
> -- | \
> -- | \
> -- 11---12
> --
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (200,10,12);
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (200,11,10);
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (200,12,11);
>
>
> -- TWO TRIANGLES ADDED TOGETHER TO MAKE A RECTANGLE
> -- 20----21
> -- |\ |
> -- | \ |
> -- | \ |
> -- | \|
> -- 23----22
> --
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (300,20,23);
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (300,20,22);
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (300,21,20);
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (300,22,21);
> INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> (300,23,22);

Tony,

I'm not sure if this can be done is plain SQL. What I can think of is to write a stored function to check if there are any triangles in a shape. The function could be something like:

create or replace function hasTriangle (s_id number) return varchar2
as

   triangle_found exception;
   pragma exception_init(triangle_found, -1436); begin

   for rec in ( select *

                from (
                    select * from tblshapes
		    where shapeid = s_id )
		connect by prior nextnode_linkedlist = node 
		and level <= 4 ) loop
      null;

   end loop;
   return 'N';
exception

   when triangle_found then

      return 'Y';
end;

Then you could do

select count(*)
from (

   select distinct shapeid
   from tblshapes
   where hasTriangle(shapeid) = 'Y'
)

to count.

The idea is that, for any shape, if you can go 4 levels deep in the "connect by" query without getting a loop, then you don't have triangles in this shape. But for this to work, you can't have data like

insert into tblshapes values (100, 1, 2);  
insert into tblshapes values (100, 2, 3);  
insert into tblshapes values (100, 1, 3);  

HTH,
Mike Received on Mon Nov 18 2002 - 14:10:55 CST